Converting information from days to hours and minutes

[mulchgirl]

I have a table where I have fields name START DATE and SHUT OFF DATE with the data type at Date/Time and it's format set to General Date so that I can record both date and time of starting and shutting off. I created a query that I added an expression [SHUT OFF DATE] - [START DATE] to figure out total time the irrigation system was running - it gave its answer in days - how can I convert this answer to hours and minutes instead?

[missinglinq]

You need to get elapsed time in minutes (I'm assuming that's as fine as you want to cut it) using DateDiff(), then parse it out, something like this:

TotalMinutesElapsed = DateDiff("n",[Start Date], [Shut Off  Date])
HoursElapsed = TotalMinutesElapsed\60
MinutesElapsed = TotalMinutesElapsed mod 60
 

Be sure to notice the back slash in the HoursElapsed formula. This causes Access to return only the interger (hours) portion of the division.

Welcome to Bytes!
Linq ;0)>

[mulchgirl]

Thanks a bunch. I have limited knowledge of coding - where would I enter the DateDiff() and the code following it - in the query, right? Sorry for my limited knowledge and thanks for your help

[hjozinovic]

If you want to use query you can try with Format function.
in the field having formula [HTML][Shut Off Date]-[Start Date][/HTML]
try replacing with [HTML]Format([Shut Off Date]-[Start Date];"hh:mm:ss")[/HTML]
Note that depending on regional settings you might need to replace semicolon (;) with comma (,)
Also hh:mm:ss is used for hours:minutes:seconds format so you might want to play with that too.