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

 

[Guest]

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?????????????????????????

 

[Dave Bradshaw]

A work around would be to set up a new field of date/time type, and
run an update query to copy the data from the numeric field to the
date field, converted to the date data type.

I don't mean to be flippant, but computers can't read minds yet, so
you are going to have to tell it the date as well as the time. The
only thing is that if you know that the time must be within the past
24 hours you could write a bit of code for the BeforeUpdate event of
the time textbox to take a day off the the time if is later than
now().

This seems to be making a big assumption about the range of
dates/times that can be entered, so I would create a text box for the
date too. The date can be stored separately from the time, or you can
do a calculation on the contents of the two text boxes to come up with
a single date/time value that can be stored in a single field, which
would make subsequent calculations a bit simpler.

 

[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.