Time Calculations

[Tim Leach]

I have a problem with a time calculation for a report. I am subtracting a
start time fom an end time. I tried using the Elapsed Time function from
the MVP board, but my result came out blank. So now I am using the
following:

DateDiff("hh",[Time]-[EndTime])

My report lists the beginning and ending time and a column for the elapsed
time in hours and minutes.

The results are wierd;

Time Start Time End Time Used
01:15 02:03 00 H 88 M (Not Correct)
02:03 03:45 01 H 42 M (Correct)
16:30 17:10 00 H 80 M (Not Correct)
20:40 00:30 -20 H 10 M (Not Correct)

This continues for the whole report, some are correct and some are not.

Please help............

Tim

 

[SteveS]

Comments inline

I have a problem with a time calculation for a report. I am subtracting a
start time fom an end time. I tried using the Elapsed Time function from

There is an excelent custom datediff function at:

http://members.rogers.com/douglas.j.steele/Diff2Dates.html

that takes into account crossing midnight. If you have problems getting it
to work, just ask for help.

the MVP board, but my result came out blank. So now I am using the
following:

DateDiff("hh",[Time]-[EndTime])

1) Shouldn't use "Time" as a field name because it is a reserved word.
2) There should be a comma between the start time and the end time, not a
minus sign and there should only be one "h":
DateDiff("h",[Time],[EndTime])

My report lists the beginning and ending time and a column for the elapsed
time in hours and minutes.

The results are wierd;

Time Start Time End Time Used
01:15 02:03 00 H 88 M (Not Correct)
02:03 03:45 01 H 42 M (Correct)
16:30 17:10 00 H 80 M (Not Correct)
20:40 00:30 -20 H 10 M (Not Correct)

The last set of times won't be correct unless you take into account crossing
midnight.

This continues for the whole report, some are correct and some are not.

Please help............

Tim

Steve

 

[Tim Leach]

Comments inline

I have a problem with a time calculation for a report. I am subtracting a
start time fom an end time. I tried using the Elapsed Time function from

There is an excelent custom datediff function at:

http://members.rogers.com/douglas.j.steele/Diff2Dates.html

that takes into account crossing midnight. If you have problems getting it
to work, just ask for help.

the MVP board, but my result came out blank. So now I am using the
following:

DateDiff("hh",[Time]-[EndTime])

1) Shouldn't use "Time" as a field name because it is a reserved word.
2) There should be a comma between the start time and the end time, not a
minus sign and there should only be one "h":
DateDiff("h",[Time],[EndTime])

My report lists the beginning and ending time and a column for the elapsed
time in hours and minutes.

The results are wierd;

Time Start Time End Time Used
01:15 02:03 00 H 88 M (Not Correct)
02:03 03:45 01 H 42 M (Correct)
16:30 17:10 00 H 80 M (Not Correct)
20:40 00:30 -20 H 10 M (Not Correct)

The last set of times won't be correct unless you take into account crossing
midnight.

This continues for the whole report, some are correct and some are not.

Please help............

Tim

Steve

Thanks, I tried the code and it still did not work. So I went looking for a
reason. The original database made the time fields as Numeric not date. I
tried to change it but it won't let me. I keep getting a message of "Too
many fields defined". I was also wondering, if you are just going to enter
a time in 24 hour format, How do you make the form know which date you want
the time attached to. For instance: if you enter 17:50 at 03:00 On the
following date, how will it know you meant 17:50 on the previous day
without also having to enter the date?

Know what I mean?????????????????????????

 

[Gregory Paret]

I have a problem with a time calculation for a report. I am subtracting a
start time fom an end time. I tried using the Elapsed Time function from
the MVP board, but my result came out blank. So now I am using the
following:

DateDiff("hh",[Time]-[EndTime])

My report lists the beginning and ending time and a column for the elapsed
time in hours and minutes.

The results are wierd;

Time Start Time End Time Used
01:15 02:03 00 H 88 M (Not Correct)
02:03 03:45 01 H 42 M (Correct)
16:30 17:10 00 H 80 M (Not Correct)
20:40 00:30 -20 H 10 M (Not Correct)

DateDiff("n",[StartTime],[EndTime])-24*60*(DateDiff("n",[StartTime],[EndTime])<0)
will return the number of minutes accounting for "unwrapping" midnight rollover.

Format([Minutes]/(24*60),"hh \H mm \M") will display the time used in the form
shown in your example.

-Greg.