Formatting time conversion (1 Viewer)

[cdoner]

Looking for help on a formatting issue regarding elapsed time calculated using DateDiff().

I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.

[PunchIn] = 11/23/2013 8:11:28 AM
[PunchOut] = 11/23/2013 5:43:30 PM

[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.

Great! Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:

=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct

=Format(([ShiftLength]\60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example

=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?

Thank you in advance.

 

[cdoner]

Found something in another post with a reply by Galaxiom but since I am new member I can't link the url. Thanks Galaxiom.

=Round(([PunchOut]-[PunchIn])*24,(2))

This will give me a two decimal point version of decimal time but if the result is 5.80, you will only see 5.8

I was hoping to have my form and report be tight with H:MM. Suggestions?

Thanks

 

[Mihail]

= dm \ 60 & "." & dm Mod 60
for dm = 572 minutes => 9.32

 

[ypma]

Try this in the control of the Shift length field of the form.

=Round((DateDiff("n",[punchin],[punchout])/60),(2))

Regards Ypma

 

[cdoner]

Mihal and ypma: thank you both for offering up your help.

Mihal: I tried your suggestion and it does not produce the result in decimal format. By decimal format I do not mean put a decimal point in between the hour and minutes but the act of converting time such that 30 minutes divided by 60 minutes = 0.5. Your example would yield 0.30.

ypma: your example does produce a result in decimal format but it doesn't solve for the problem of how to get a result of 0.5 or 3.8 or 4.3 to display as 0.50 or 3.80 or 4.30. So close.

 

[pr2-eugin]

Use Format?

Format(Round((DateDiff("n",[punchin],[punchout])/60),2), "####.00")

 

[cdoner]

pr2-eugin: That did the trick. Thank you very much.