Calculating Time

[Guest]

Had one issue calculating time in a Report. The report shows people looking
for service, it has the time the person arrive and the time an officer call
them. Another field subtracting the second time from the first time. All
those fields has the short time format on it.
The problem is with the short format I didn't see seconds but the report
consider those. So if I had a person arrives at 11:15 with 19 seconds and
this person was called at 11:24 with 3 seconds, the reports will show 11:15
in the first field, 11:24 in the second but in the calculated field it will
show 00:08 minutes because of the diference in the seconds.
How can I fix this? Because it didn't make sence to user, the fact of
having one minute difference because of some seconds.
Hope you can help me with this...

 

[Rick B]

TIME is a set point in time. DURATION is the amount of time that has
elapsed between to points in time.

You can't calculate a duration and display it using a time field (actually
not a field here).

Durations should be calculated in seconds (or minutes, or hours).

The following example will display a stored field containing a number of
minutes in a format that shows hours and minutes...

=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")



The following example will calculate the number of minutes elapsed between
two times and display them in the same format...

=(DateDiff("n",[StartLunch],[EndLunch]))\60 &
Format((DateDiff("n",[StartLunch],[EndLunch])) Mod 60,"\:00")

 

[Rick B]

Note that my examples used minutes, you will want to change them to seconds
and use the same format.

They key here is that it is not a TIME field.

 

[Guest]

The example works perfectly, I just change the "n" for an "s" to get the
seconds but the number in this field show as minutes:seconds and I would like
it to show an hours:minutes:seconds.

Thanks again for your help.

TIME is a set point in time. DURATION is the amount of time that has
elapsed between to points in time.

You can't calculate a duration and display it using a time field (actually
not a field here).

Durations should be calculated in seconds (or minutes, or hours).

The following example will display a stored field containing a number of
minutes in a format that shows hours and minutes...

=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")



The following example will calculate the number of minutes elapsed between
two times and display them in the same format...

=(DateDiff("n",[StartLunch],[EndLunch]))\60 &
Format((DateDiff("n",[StartLunch],[EndLunch])) Mod 60,"\:00")



--
Rick B

Had one issue calculating time in a Report. The report shows people looking
for service, it has the time the person arrive and the time an officer call
them. Another field subtracting the second time from the first time. All
those fields has the short time format on it.
The problem is with the short format I didn't see seconds but the report
consider those. So if I had a person arrives at 11:15 with 19 seconds and
this person was called at 11:24 with 3 seconds, the reports will show 11:15
in the first field, 11:24 in the second but in the calculated field it will
show 00:08 minutes because of the diference in the seconds.
How can I fix this? Because it didn't make sence to user, the fact of
having one minute difference because of some seconds.
Hope you can help me with this...

 

[Rick B]

Then you'll have to modify the formula to include a more complex division
formula.

I have not tried it before, you'll just need to play with the math.

--
Rick B

The example works perfectly, I just change the "n" for an "s" to get the
seconds but the number in this field show as minutes:seconds and I would like
it to show an hours:minutes:seconds.

Thanks again for your help.

TIME is a set point in time. DURATION is the amount of time that has
elapsed between to points in time.

You can't calculate a duration and display it using a time field (actually
not a field here).

Durations should be calculated in seconds (or minutes, or hours).

The following example will display a stored field containing a number of
minutes in a format that shows hours and minutes...

=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")



The following example will calculate the number of minutes elapsed between
two times and display them in the same format...

=(DateDiff("n",[StartLunch],[EndLunch]))\60 &
Format((DateDiff("n",[StartLunch],[EndLunch])) Mod 60,"\:00")



--
Rick B

Had one issue calculating time in a Report. The report shows people looking
for service, it has the time the person arrive and the time an officer call
them. Another field subtracting the second time from the first time. All
those fields has the short time format on it.
The problem is with the short format I didn't see seconds but the report
consider those. So if I had a person arrives at 11:15 with 19 seconds and
this person was called at 11:24 with 3 seconds, the reports will show 11:15
in the first field, 11:24 in the second but in the calculated field it will
show 00:08 minutes because of the diference in the seconds.
How can I fix this? Because it didn't make sence to user, the fact of
having one minute difference because of some seconds.
Hope you can help me with this...

 

[John Vinson]

The example works perfectly, I just change the "n" for an "s" to get the
seconds but the number in this field show as minutes:seconds and I would like
it to show an hours:minutes:seconds.

Just to amplify on Rick's suggestion:

[duration] \ 3600 & Format([duration] \ 60 MOD 60, ":00") &
Format([duration] MOD 60, ":00")

John W. Vinson[MVP]

 

[Guest]

I had your formula in my report and it works perfectly in a detail report.
If I put the same formula in another report (summary by services) the report
only shows the value for the first record with the same service.
How can I change it to get the info for all the records as a summary

TIME is a set point in time. DURATION is the amount of time that has
elapsed between to points in time.

You can't calculate a duration and display it using a time field (actually
not a field here).

Durations should be calculated in seconds (or minutes, or hours).

The following example will display a stored field containing a number of
minutes in a format that shows hours and minutes...

=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")



The following example will calculate the number of minutes elapsed between
two times and display them in the same format...

=(DateDiff("n",[StartLunch],[EndLunch]))\60 &
Format((DateDiff("n",[StartLunch],[EndLunch])) Mod 60,"\:00")



--
Rick B

Had one issue calculating time in a Report. The report shows people looking
for service, it has the time the person arrive and the time an officer call
them. Another field subtracting the second time from the first time. All
those fields has the short time format on it.
The problem is with the short format I didn't see seconds but the report
consider those. So if I had a person arrives at 11:15 with 19 seconds and
this person was called at 11:24 with 3 seconds, the reports will show 11:15
in the first field, 11:24 in the second but in the calculated field it will
show 00:08 minutes because of the diference in the seconds.
How can I fix this? Because it didn't make sence to user, the fact of
having one minute difference because of some seconds.
Hope you can help me with this...

 

[DAT]

My question would be directed to Rick. I just found this thread and I have a similar problem. I needed to calculate the time difference. I found the formula here and used it which did work for the time difference. I was wondering how I could convert that time in to the decimal time (100 minutes in an hour). Also I need to use that time number to devide Revenue by so that I would get Revenue/Hour figure. I would appreciate your help.

Dmitriy