Results 1 to 5 of 5
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Calculate time difference in days hours minutes

    Dear All ,
    I am working on a task system and i have to view to the admin how much time taken to end this task
    so if i have startDate as 8/9/2019 7:23:13 PM and endDate as 8/8/2019 7:23:13 PM so it should give me 1 day , 0 hour , 0 minutes

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Start date of Aug 9 and End date Aug 8? Shouldn't those dates be swapped?

    Code:
    Function DHM(dStart As Date, dEnd As Date) As String
    Dim Dy As Integer, Hr As Integer, Mn As Integer
    DHM = DateDiff("n", dStart, dEnd)
    Dy = Int(DHM / 1440)
    Hr = Int((DHM Mod 1440) / 60)
    Mn = (DHM Mod 1440) Mod 60
    DHM = Dy & " day, " & Hr & " hour, " & Mn & " minutes"
    End Function 
    

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    On fly (and check brackets). I added some additional brackets and multiplying with 1 too, so the calculation structure in every formula remains same and is easier to follow.
    Code:
    Days = Int(1*(CDbl(EndDate) - CDbl(StartDate)))
    Hours = Int(24*(1*(CDbl(EndDate) - CDbl(StartDate)) - Int(1*(CDbl(EndDate) - CDbl(StartDate)))))
    Minutes = Int(60*(24*1*(CDbl(EndDate) - CDbl(StartDate)) - Int(24*1*(CDbl(EndDate) - CDbl(StartDate)))))
    Seconds = Int(60*(60*24*1*(CDbl(EndDate) - CDbl(StartDate)) - Int(60*24*1*(CDbl(EndDate) - CDbl(StartDate)))))

  4. #4
    shod90 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by June7 View Post
    Start date of Aug 9 and End date Aug 8? Shouldn't those dates be swapped?

    Code:
    Function DHM(dStart As Date, dEnd As Date) As String
    Dim Dy As Integer, Hr As Integer, Mn As Integer
    DHM = DateDiff("n", dStart, dEnd)
    Dy = Int(DHM / 1440)
    Hr = Int((DHM Mod 1440) / 60)
    Mn = (DHM Mod 1440) Mod 60
    DHM = Dy & " day, " & Hr & " hour, " & Mn & " minutes"
    End Function 
    

    Worked like a charm , Many thanks !
    And yes you are right time should be swapped

  5. #5
    shod90 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by ArviLaanemets View Post
    On fly (and check brackets). I added some additional brackets and multiplying with 1 too, so the calculation structure in every formula remains same and is easier to follow.
    Code:
    Days = Int(1*(CDbl(EndDate) - CDbl(StartDate)))
    Hours = Int(24*(1*(CDbl(EndDate) - CDbl(StartDate)) - Int(1*(CDbl(EndDate) - CDbl(StartDate)))))
    Minutes = Int(60*(24*1*(CDbl(EndDate) - CDbl(StartDate)) - Int(24*1*(CDbl(EndDate) - CDbl(StartDate)))))
    Seconds = Int(60*(60*24*1*(CDbl(EndDate) - CDbl(StartDate)) - Int(60*24*1*(CDbl(EndDate) - CDbl(StartDate)))))
    Thanks buddy , I'll keep that in my records too

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-04-2020, 09:21 AM
  2. Replies: 13
    Last Post: 02-12-2020, 04:13 PM
  3. Replies: 11
    Last Post: 12-12-2016, 12:00 PM
  4. Replies: 8
    Last Post: 07-07-2014, 11:21 AM
  5. How to calculate duration in hours & minutes
    By joypanattil in forum Access
    Replies: 0
    Last Post: 11-25-2009, 04:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums