# MS Access Query - calculate number of hours (military time)

I have a work begin time and work end time field that each contain the date and time (military time). I need to calculate the number of hours between the two. For example:
Work_Begin_Time  4/10/2007 5:04:13 PM
Work_End_Time    4/10/2007 6:34:13 PM

Thoughts on how to do this in a query?

Thanks!
###### Who is Participating?

Commented:
NoOFHours:dateDiff("h", [Work_Begin_Time],[Work_End_Time])
0

Commented:
NameOfField: DateDiff("h", [Work_Begin_Time],[Work_End_Time])
0

Commented:
Should have hit refresh...
0

Commented:
Capricorn1, when I first click on the Question it doesn't always show the first comment, have you experience this? Imoutwest
0

Commented:
yes, it happens..don't worry about that
0

Author Commented:
Capricorn1:

I forgot one thing, what do I need to do to account for something that is less than 1 hour. For example:

Begin time 4/10/2007 5:00:27 PM
End time4/10/2007 5:45:27 PM

This is going to be 45 min so I will need to calculate hours and minutes.

Thanks!
0

Commented:
you will need a function to do that
from m\$ http://support.microsoft.com/default.aspx?kbid=210276

'------------------------------------------------------------------
'  This function calculates the elapsed time between two values and then
'  formats the result in four different ways.
'
'  The function accepts interval arguments such as the following:
'
'     #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
'
'
'
'     [End Time]-[Start Time]
'------------------------------------------------------------------

Function ElapsedTime (Interval)
Dim x
x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
Debug.Print x
x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
& " Minutes:Seconds"
Debug.Print x
x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
& " Hours:Minutes:Seconds"
Debug.Print x
x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
& " Hours " & Format(Interval, "nn") & " Minutes " & _
Format(Interval, "ss") & " Seconds"
Debug.Print x

End Function

0

Commented:
Me.TotalHours = Format([Work_Begin_Time] - 1 - [Work_End_Time], "Short Time")
0

Author Commented:
capricorn1:

How would I apply that function in my query?

Thanks!
0

Commented:
in a column in the query grid

NoOFHours:ElapsedTime([Work_End_Time]- [Work_Begin_Time])
0

Author Commented:
capricorn1:

I'm receiving an error when I run the query - undefined function 'ElapsedTime' in Expression. I copied exactly what you listed above and my module is named ElapsedTime.

Thoughts?
0

Commented:
< my module is named ElapsedTime> this is a big mistake never name a module with the same name as the function or sub

rename you module to  modElapsedTime
0

Author Commented:
Thank you for that advice. I do appreciate it.

I renamed the module and the following is in my query...

NoOFHours: modElapsedTime([Work_End_Time]-[Work_Begin_Time])

However, I'm still receiving the same error. Is there anything I need to do to the module itself? I copied exactly what you had listed above.

Thoughts?
0

Commented:
khawkins96,
you should change the name of the module not the function.

copy this and replaced the codes from the module modElapsedTime

Function ElapsedTime (Interval)
Dim x
x = Int(CSng(Interval * 24 * 3600)) & " Seconds"

x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
& " Minutes:Seconds"

x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
& " Hours:Minutes:Seconds"

x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
& " Hours " & Format(Interval, "nn") & " Minutes " & _
Format(Interval, "ss") & " Seconds"
ElapsedTime= x

End Function

then use this in your query
NoOFHours: ElapsedTime([Work_End_Time]-[Work_Begin_Time])

0

Author Commented:
Oops! Sorry about that! I got a little confused.
I did exactly what you suggested and the query ran, however I'm getting a #Error in the field. The data in the two fields are:

WORK_BEGIN_TIME 4/10/2007 5:04:13 PM
WORK_END_TIME 4/10/2007 6:34:13 PM

There's also another one:
WORK_BEGIN_TIME 4/10/2007 5:00:27 PM
WORK_END_TIME 4/10/2007 5:45:27 PM

Both are displaying #Error. Any ideas?

0

Author Commented:
Thanks for all the help! I took your very first suggestion and did some more searching on the internet. I ended up with the following:

TimeInMinutes: DateDiff("n",[Work_Begin_Time],[Work_End_time])
HoursTime: [TimeInMinutes]\60
MinutesTime: [TimeInMinutes] Mod 60
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.