Calculating Military Time

Question posted by: Serenityquinn15 (Newbie) on July 4th, 2008 01:42 AM

Hi!
I used Query in Access to calculate the time in and out of the employee of course to get the total minutes, however i've been encountered a problem. when i put for timeIn=22:00 and for time Out =24:00 the total mins display like this:" -1320 ", which actually wrong. it should be "120" mins instead of "-1320". how i can fix this problem??? i already set those data into short time format.
Please help me.... thank you....

Would you like to answer this question?
Sign up for a free account, or Login (if you're already a member).

salimudheen

Newbie

14 Posts

July 4th, 2008
01:15 PM
#2

Re: Calculating Military Time

Hey,
Try this simple method,

Option Compare Database
Dim minsIn As Long
Dim minsOut As Long
Private Sub TimeIn_Exit(Cancel As Integer)
If TimeIn = 0# Then
minsIn = 1440
Else
minsIn = VBA.DatePart("h", TimeIn) * 60
minsIn = minsIn + VBA.DatePart("n", TimeIn)
End If
End Sub
Private Sub TimeOut_Exit(Cancel As Integer)
If TimeOut = 0# Then
minsOut = 1440
Else
minsOut = VBA.DatePart("h", TimeOut) * 60
minsOut = minsOut + VBA.DatePart("n", TimeOut)

End If
End Sub

Private Sub TotalTime_Enter()
TotalTime = minsOut - minsIn
End Sub

ADezii

Expert

3,869 Posts

July 4th, 2008
03:08 PM
#3

Re: Calculating Military Time

Midnight should be expressed as 00:00 and not 24:00.
You must allow for the post-Midnight scenario by adding a Day in Minutes (1,440).
Based on a [StartTime] and [EndTime] Fields, and a Table named tblTest, the following Query using a simple Public Function should solve the problem:

Code: ( text )

SELECT tblTest.StartTime, tblTest.EndTime, fCalcDiffInMinutes([StartTime],[EndTime]) AS [Interval]
FROM tblTest;

Code: ( text )

Public Function fCalcDiffInMinutes(dteStart As Date, dteFinish As Date) As Long
Dim intDifference As Integer
intDifference = DateDiff("n", dteStart, dteFinish)
If intDifference < 0 Then
fCalcDiffInMinutes = intDifference + 1440
Else
fCalcDiffInMinutes = intDifference
End If
End Function

Test Data (tblTest):

Code: ( text )

StartTime EndTime
12:00 13:00
22:00 23:50
6:00 8:00
22:00 0:00
23:00 4:00
12:00 13:30

OUTPUT:

Code: ( text )

StartTime EndTime Interval
12:00 13:00 60
22:00 23:50 110
6:00 8:00 120
22:00 0:00 120
23:00 4:00 300
12:00 13:30 90

Serenityquinn15

Newbie

25 Posts

July 7th, 2008
12:23 AM
#4

Re: Calculating Military Time

Hi!
It works good!!! Thanks for the help!

ADezii

Expert

3,869 Posts

July 7th, 2008
11:52 AM
#5

Re: Calculating Military Time

Quote:

Originally Posted by Serenityquinn15

Hi!
It works good!!! Thanks for the help!

You are quite welcome.

Not the answer you were looking for? Post your question . . .
183,908 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).