Calculating variance between two dates and converting result to qty of hours

[rfigueroa1976]

I'm subtracting actual pickup date from requested pickup date - " Pickup Var: ([dbo_stops].[stp_arrivaldate])-([dbo_orderheader].[ord_origin_latestdate]) " to get the variance between the two. The format of both date fields is MM/DD/YY HH:MM:SS. The result of the subtraction is a decimal number... "

1.415200197 ".

What i'm hoping access will return is the duration of time between the two dates "

33:57:53 "

Long story short, can someone please help me convert a result of subtracting one date field from another as a duration between the two in hours.



Thanks in advance.

Tags:
Access Change decimal number to hours
Access convert decimal number to hours
Access convert number to duration
Access convert number to time

[aytee111]

Use the function DateDiff, you can get hours from that.

[ranman256]

=DAteDiff("h",date1,date2)

(in dates, the integer is the day, the decimal is the time)

[rfigueroa1976]

This worked! However, is there a way to include minutes with the hours? Meaning, if date1 was "10/9/2017 17:15:00" and date2 was "10/10/2017 19:30:00", the calculation would show the difference of "26:15:00"

=DAteDiff("h",date1,date2)

(in dates, the integer is the day, the decimal is the time)

[aytee111]

You need to get the number of minutes with DateDiff.

Dim Period as Double, strElapse as String

Period = DateDiff("n",date1,date2) 
strElapse = Int(Period/60) & ":" & (Period / 60 - Int(Period / 60)) * 60

[rfigueroa1976]

I don't understand (I'm a novice). Can you please right it out in formula format so that i can copy and paste?

You need to get the number of minutes with DateDiff.

Dim Period as Double, strElapse as String

Period = DateDiff("n",date1,date2) 
strElapse = Int(Period/60) & ":" & (Period / 60 - Int(Period / 60)) * 60

[aytee111]

DateDiff("n", [date1], [date2]) \ 60 & Format(DateDiff("n", [date1], [date2]) Mod 60, "\:00")

Source: http://allenbrowne.com/casu-13.html