Time Conversions

[Hillary]

I am having a really rough time trying to compare two
time values - a clock in and a clock out. I have used
DateDiff function with decent results, but cannot seem to
format the result to be a hh:nn (hour:minute) value
instead of all hours or all minutes.
For example:
TimeIn: 3:44 AM TimeOut: 12:28 PM
DateDiff(DateDiff("n",[TimeOut],[TimeIn]) = -524

I need to convert that to a time value, in this case,
8:44. Dividing 524 of course won't work becuase it
returns a decimal value instead of a time value.
Any help would be GREATLY appreciated. I've been trying
to figure this out for far too many hours!
Thanks
Hillary

 

[Ken Snell]

The "trick" to displaying the calculated hours and minutes in the "hh:nn"
format is to calculate the time differential in minutes, and then use the
minutes to calculate the full hours and the residual minutes, and then
display them using the Format command in the "hh:nn" format.

Assuming that a field named STime is the start time and ETime is the end
time, use this as the control source to display the time differential in
your desired format:

=Format(DateDiff("n", [STime], [ETime]) \ 60, "0:") & Format(DateDiff("n",
[STime], [ETime]) Mod 60, "00")

If you're using a query, just use the above expression (without the = sign)
as a calculated field.

 

[HIllary]

Ken - Thank you! I'll try it today.

-----Original Message-----
The "trick" to displaying the calculated hours and minutes in the "hh:nn"
format is to calculate the time differential in minutes, and then use the
minutes to calculate the full hours and the residual minutes, and then
display them using the Format command in the "hh:nn" format.

Assuming that a field named STime is the start time and ETime is the end
time, use this as the control source to display the time differential in
your desired format:

=Format(DateDiff("n", [STime], [ETime]) \ 60, "0:") & Format(DateDiff("n",
[STime], [ETime]) Mod 60, "00")

If you're using a query, just use the above expression (without the = sign)
as a calculated field.
--

Ken Snell
<MS ACCESS MVP>

I am having a really rough time trying to compare two
time values - a clock in and a clock out. I have used
DateDiff function with decent results, but cannot seem to
format the result to be a hh:nn (hour:minute) value
instead of all hours or all minutes.
For example:
TimeIn: 3:44 AM TimeOut: 12:28 PM
DateDiff(DateDiff("n",[TimeOut],[TimeIn]) = -524

I need to convert that to a time value, in this case,
8:44. Dividing 524 of course won't work becuase it
returns a decimal value instead of a time value.
Any help would be GREATLY appreciated. I've been trying
to figure this out for far too many hours!
Thanks
Hillary

.