military time calculations

[kiwi]

I have a problem that I can't seem to find a solution for anywhere so this is my last resort. I'm an amateur user doing a volunteer project for a local fire department. I need to calculate response times from the time a called is toned out to the time it takes an engine or whatever to arrive. The calculation works great if both of the times (in 24 hour time format) are on the same date but if one time is say 23:54 and the other is 0:14, the response time shows up as 23:40 rather than 0:20. I have a simple calculation of =Format([Arr Tm 1]-[Tone Time],"Short Time") at present. Any and all suggestions would be most welcomed.

 

[Travis]

In order for anytime format to work either (12 hour or 24 hour) when the time spans two days is to include the Date also. I would recommend using the Now() function to help you in this endevour. Otherwise the calculations will return strange values.

also when you calculate use the following

=DateDiff("m",[Tone Time],[Arr Tm 1])
This will return the Minutes between the tone and Arrive (hopefully with a fire department it never takes them longer then 60 minutes to arrive)


Hope this helps

 

[Neal]

I've had the same problem with the length of pro sports games. If they start before midnight and end after, I get a minus number. Rather than have my users enter the dates for the start and end times, I trap for the minus numbers. I.e.
If [Length] <0, then
[Length] = [Length] + 1440 'the number of minutes in a day

 

[kiwi]

Thanks for the help! I still can't quite get it to work however using either option. Travis - in using your formula, the field always returns a 0:00 value. I haven't used Now() anywhere as I believe it inserts the current date and time only?? Sometimes the calls are being entered several weeks later. How can I link the dates into the formula so that this will work and can I only enter the date if there is an actual difference??

Neal - I also tried your option and it reads as follows:
Private Sub Tn_to_Arrival_2_GotFocus()
If DateDiff("m", [Arr Tm 2], [Tone Time] < 0) Then DateDiff("m", [Arr Tm 2], [Tone Time]) = DateDiff("m", [Arr Tm 2], [Tone Time]) + 1440

End Sub

Is this what you were meaning?? It is debugged so to speak but also returns no values. The field is now unbound which may be my problem but what do I "bind" it to if this is the case?? Also is using the "Got Focus" correct?

Thanks again for the help... Jane

 

[Neal]

I'm not sure what fields you have, but this is the way I do it with game lengths.

LenGame = DateDiff("n",[Begin],[End])
if LenGame <0 then
LenGame = LenGame + 1440

I don't store LenGame in my table since it is the result of a calcuation. I use it in a form (so the user can immediately see how long the game lasted) and in a report that calculates the average length of games played by particlar teams.

 

[Travis]

When times used in a calculation could possibly span more then a single day it is best to store the entire Date/Time. I realize that this may seem like an overkill but it is very important to the calculation. Also if you currently have a date field that stores a date you can consolidate the table by removing it and using the StartTime (which will now be the Date/Time). I used Now() since that is the format that you will need to store the data in the field (date and Time).

Hope this helps.

 

[BarkerD]

I have also had to deal with the 24 Hour clock. I have to calculate hours worked on a midnight shift that starts at 11 P.M. Here is the code that I used to solve it.

If Me![Finish] < Me![Start] Then
Me![PT] = Round((Me![Finish] + 1 - Me![Start]) * 24, 2)
Else
If Me![Finish] > Me![Start] Then
Me![PT] = Round((Me![Finish] - Me![Start]) * 24, 2)
End If
End If

This code looks to see if the Finish time is a smaller value than the Start time. If it is, it adds a full day of hours to the finish time, and then subtracts this from the start time.

Eg. 11 pm - 7 am shift
7 Am = 31 [Finish]
11 pm = 23 [Start]

Finish - start = 8 hours

I had to multiply the whole value by 24 to get it into hours format.

Hope this helps.

Duane Barker

 

[FionaC]

Another option is a calculated field in your query. I used to use this to calculate how long an operation took, especially if it went over the midnight period. It does put it into decimal format eg 1h 30 min (1:30) = 1.5 (that is the *24 part of the equation)

TIME_LENGTH

IIF(([END_TIME]+1)<([START_TIME]+1),(([END_TIME]+1)+1),([END_TIME]+1))-(([START_TIME])+1))*24

 

[90007017]

I used the DateDiff with m option for minutes then I divide that by 60 to get hours.

I use decimal hours.

It works on dates spanning 2 days
Datediff(n,[Time leave],[Time arrive])/60

 

[analyst1]

Working Time: IIf([end time]-[start time]<0,[end time]+1-[start time]*24,[end time]-[start time])

This is my formula but it doesn't seem to work. Any help here?

 

[John Big Booty]

Have a look at the DateDiff() function.

Try somthing like;

=IIf(DateDiff("n",[StTime],[EnTime])>0,DateDiff("n",[StTime],[EnTime]),((DateDiff("n",[StTime],"23:59"))+(DateDiff("n","0:00",[EnTime]))+1))/60

 

[pbaldy]

In my notes, this is supposed to handle times across midnight:

Format([StartTime] - 1 - [EndTime], "Short Time")

 

[John Big Booty]

That Formula ^^^^ assumes you are dealing with times either side of midnight, and will not work for time periods that span more than a 24 hour period.

 

[pbaldy]

That is certainly true. Didn't realize that was a possibility here. It is geared towards work shift type situations.

 

[John Big Booty]

In my notes, this is supposed to handle times across midnight:

Format([StartTime] - 1 - [EndTime], "Short Time")

Yes use this one, far simpler, and will return HH:mm rather than HH.hh as my solution does.

 

[John Big Booty]

That is certainly true. Didn't realize that was a possibility here. It is geared towards work shift type situations.

Sorry that was supposed to be in reference to my formula, but you posted your solution whilst I was writing that post.

 

[pbaldy]

No problem John; I figured I'd missed something in my admittedly brief review of the thread.

 

[John Big Booty]

No problem John; I figured I'd missed something in my admittedly brief review of the thread.

Either way yours is the superior solution.