How to show hours and minutes in a query in MS Access

LillyC
LillyC used Ask the Experts™
on
I just can't get this right grrrrrrr, I'm trying to show the number of hours and minutes, I can get the hours and the minutes separately but want to show as hours/minutes, eg 100 hours = 1 hour 40 minutes

Lateness Total Hours: (DateDiff('n',[Time due in],[Time arrived])/60)
Lateness Total Minutes: (DateDiff('n',[Time due in],[Time arrived]))
Lateness Total Hours/Minutes: (DateDiff('n',[Time due in],[Time arrived]))-(DateDiff('n',[Time due in],[Time arrived]))/60
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
CIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use:

Lateness Total Hours/Minutes: Format([Time arrived]-[Time due in], "h:nn")

Open in new window

Mark EdwardsChief Technology Officer

Commented:
This should give you the number of hours between in/out times:
Hours = DateDiff("h",[Datetimein],[datetimeout])
This should give you the leftover minutes after you subtract the full hours:
Minutes = DateDiff("n",[DatetimeIn],[datetimeOut])-((DateDiff("h",[DatetimeIn],[datetimeOut]))*60)

Works for me....
Mark EdwardsChief Technology Officer

Commented:
The above works if you want separate hours and minutes, or you could parse Gustav's solution results.
LillyCDatabase Developer

Author

Commented:
Superb - thank you both :)
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial