Calculating hours and minutes between two times in HH:MM format

[andielangley]

I have a table of flexitime data which contains four fields AM Time In, AM Time Out, PM Time In and PM Time Out. All the fields are of type Date/Time and are formatted as Short Date so they appear as 08:45, 13:56, etc

What I need to do with a query is find out the number of hours and minutes worked between those times and display the final result in the format HH:MM. So, for example, the check in and out values are 08:30, 12:30, 13:30, 17:00 and the result I need is 07:30 representing 7 and a half hours worked.

I can do the arithmetic in a calculated field but the result is displayed as a numeric fraction and I have no idea how to get this into the format I need.

Any advice would be appreciated, thanks

 

[ashm01]

I have a query similar to that..I also have 2 fields in short date format(STARTTIME and ENDTIME), I need to know whether the current time is within the range.

please help.

Sincerely,

Ashm01

 

[andielangley]

A correction to my original post - the fields are in Short Time format, not Short Date as I originally stated. I don't know if that makes any difference...

 

[ZmrAbdulla]

Hi,
Check this
thread702-569949
multiply the result by 60 wil the time in minutes

May be useful
In
ZmrAbdulla

 

[PaulBricker]

Say your expression are like this
Expr1:[AM Time Out] - [AM Time In]
Expr2:[PM Time Out] - [PM Time In]

And the results are
0.166666666666667
0.145833333333333

This expression in another column will give you
7:30

Expr3: Int(([Expr1]+[Expr2])*24) & ":" & Int((([Expr1]+[Expr2])*24-Int(([Expr1]+[Expr2])*24))*60)

There may be an easier way to do this but the Format function didn't seem to do it so I worked it out long hand.

Paul

 

[andielangley]

Thanks, this was helpful, but I find that Expr3 is sometimes a minute short - possibly to do with rounding errors of the INT function? I'm getting closer to a working solution though.

 

[PaulBricker]

Can you give me a couple of examples that have rounding problems? I will look at it a little more.

Paul

 

[PaulBricker]

You can try this instead. It seems to pick up that extra minute.

Expr4: Int(((([Expr1]+[Expr2])*24)*60)/60) & ":" & (([Expr1]+[Expr2])*24)*60 Mod 60


Paul

 

[andielangley]

Brilliant, that's done it, thanks a lot Paul