24hrs time difference

J

jor

Is there a way to get time in a "Military Time" format from a DateDiff formula?

example:
Start End DIFFERENCE
19:15 1:05AM 5:50

I am using below formula but it gives me = -18:-10 result.

Difference: DateDiff("n", [Start], [End]) & ":" &
Format((DateDiff("n",[Start], [Out]) Mod 60), "00")

Thanks
 
C

Clifford Bass

Hi Jor,

If you are crossing through midnight you need to include the dates also:

DateDiff("h", #11/11/2008 19:15#, #11/12/2008 1:05#) & ":" &
Format((DateDiff("n", #11/11/08 19:15#, #11/12/08 1:05#) Mod 60), "00")

Also the first DateDiff should use "h", not "n".

Clifford Bass
 
J

jor

Thanks clifford, am really new on this and i really appreciate ur help. I am
using the code on my query and it works. Another question is, how can i
apply this for the rest of my data which has different dates and time.

Thanks again for your help.


Clifford Bass said:
Hi Jor,

If you are crossing through midnight you need to include the dates also:

DateDiff("h", #11/11/2008 19:15#, #11/12/2008 1:05#) & ":" &
Format((DateDiff("n", #11/11/08 19:15#, #11/12/08 1:05#) Mod 60), "00")

Also the first DateDiff should use "h", not "n".

Clifford Bass

jor said:
Is there a way to get time in a "Military Time" format from a DateDiff formula?

example:
Start End DIFFERENCE
19:15 1:05AM 5:50

I am using below formula but it gives me = -18:-10 result.

Difference: DateDiff("n", [Start], [End]) & ":" &
Format((DateDiff("n",[Start], [Out]) Mod 60), "00")

Thanks
 
K

Ken Sheridan

The following function will return the time difference between two date/time
values:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function


Paste the function into a standard module, and if it’s a new module save it
under a different name, e.g. mdlDateTimeStuff.

You can then call it by passing the start and end date time values into it
like so:

Difference:TimeDuration([Start], [End])

Its important to understand that there is no such thing in Access as a time
value per se, only date time values. If you enter a time value its actually
stored as the time on 30 December 1899, which is 'day zero' in Access's
implementation of the date/time data type. Consequently
TimeDuration(#19:15#,#1:05AM#) will return the negative result of -6:10:00 as
Access treats the times as being both on the same day (30 December 1899). So
if your start and end times include the actual dates as well as the times the
function will work fine, but if your values are entered as times only you'll
get incorrect results. Provided that your time differences will always be
less than 24 hours you can amend the function as follows to cater for 'time
only' values being entered:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

' add this new line
If dtmFrom > dtmTo Then dtmTo = DateAdd("d", 1, dtmTo)

dblDuration = dtmTo – dtmFrom

' and so on as before

This does limit the use of the function to these specific circumstances,
however.

If you want to show the number of days in the return value rather than the
total hours, then pass in True as the third optional argument:

Difference:TimeDuration([Start], [End], True)

This will return the difference in a format along these lines:

2 days 3:45:00

Ken Sheridan
Stafford, England

jor said:
Thanks clifford, am really new on this and i really appreciate ur help. I am
using the code on my query and it works. Another question is, how can i
apply this for the rest of my data which has different dates and time.

Thanks again for your help.


Clifford Bass said:
Hi Jor,

If you are crossing through midnight you need to include the dates also:

DateDiff("h", #11/11/2008 19:15#, #11/12/2008 1:05#) & ":" &
Format((DateDiff("n", #11/11/08 19:15#, #11/12/08 1:05#) Mod 60), "00")

Also the first DateDiff should use "h", not "n".

Clifford Bass

jor said:
Is there a way to get time in a "Military Time" format from a DateDiff formula?

example:
Start End DIFFERENCE
19:15 1:05AM 5:50

I am using below formula but it gives me = -18:-10 result.

Difference: DateDiff("n", [Start], [End]) & ":" &
Format((DateDiff("n",[Start], [Out]) Mod 60), "00")

Thanks
 
J

jor

Thanks ken/clifford, i really appreciate your help and am started learning
using codes.

Cheersssss. . .



Ken Sheridan said:
The following function will return the time difference between two date/time
values:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function


Paste the function into a standard module, and if it’s a new module save it
under a different name, e.g. mdlDateTimeStuff.

You can then call it by passing the start and end date time values into it
like so:

Difference:TimeDuration([Start], [End])

Its important to understand that there is no such thing in Access as a time
value per se, only date time values. If you enter a time value its actually
stored as the time on 30 December 1899, which is 'day zero' in Access's
implementation of the date/time data type. Consequently
TimeDuration(#19:15#,#1:05AM#) will return the negative result of -6:10:00 as
Access treats the times as being both on the same day (30 December 1899). So
if your start and end times include the actual dates as well as the times the
function will work fine, but if your values are entered as times only you'll
get incorrect results. Provided that your time differences will always be
less than 24 hours you can amend the function as follows to cater for 'time
only' values being entered:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

' add this new line
If dtmFrom > dtmTo Then dtmTo = DateAdd("d", 1, dtmTo)

dblDuration = dtmTo – dtmFrom

' and so on as before

This does limit the use of the function to these specific circumstances,
however.

If you want to show the number of days in the return value rather than the
total hours, then pass in True as the third optional argument:

Difference:TimeDuration([Start], [End], True)

This will return the difference in a format along these lines:

2 days 3:45:00

Ken Sheridan
Stafford, England

jor said:
Thanks clifford, am really new on this and i really appreciate ur help. I am
using the code on my query and it works. Another question is, how can i
apply this for the rest of my data which has different dates and time.

Thanks again for your help.


Clifford Bass said:
Hi Jor,

If you are crossing through midnight you need to include the dates also:

DateDiff("h", #11/11/2008 19:15#, #11/12/2008 1:05#) & ":" &
Format((DateDiff("n", #11/11/08 19:15#, #11/12/08 1:05#) Mod 60), "00")

Also the first DateDiff should use "h", not "n".

Clifford Bass

:

Is there a way to get time in a "Military Time" format from a DateDiff formula?

example:
Start End DIFFERENCE
19:15 1:05AM 5:50

I am using below formula but it gives me = -18:-10 result.

Difference: DateDiff("n", [Start], [End]) & ":" &
Format((DateDiff("n",[Start], [Out]) Mod 60), "00")

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totaling Times 2
Where to put an IIF in a query 2
DateDiff 4
Calculating Time Error????? 3
Please help with a date/time query! 2
Wierd Characters 2
Datediff Help 3
Calculating Minutes 5

Top