DateDiff Function

[mhinkle]

Hello,
I am trying to create a query that subtracts "TimeOUT" from "TimeIN" fields to calculate the total time worked.

The "TotalTime" is a new field that is not in the table, only in the query.

I am attempting to use the DateDiff function to calculate this, but I need to return both hours and minutes.

Currently, it looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT])

It works this way to return the total time in hours only.

The DateDiff function works to return either hours (h) or minutes but I cannot figure out how to get both!

Perhaps someone has an answer or possibly a better way to get the results in hour:minute format for me??!!

 

[mlai08]

I don't know if there is a simple solution to this due to the limitation on the DateDiff function but you may do it in a long & winding way as follows:

1) Get the time difference in minutes and convert the result to the unit of hour, then convert the result to string.

TimeDiff = CStr(DateDiff("n",[Timein],[timeout])/60)

2) Break it up to Hour and Minutes by using the Left and Mid Functions.

Hours = Left([TimeDiff],InStr([TimeDiff],".")-1)
Minutes = Mid([TimeDiff],InStr([TimeDiff],"."),Len([TimeDiff]))*60

3) Concatenate the Hours & Minutes to the resulting field

Final time difference = [Hours] & ":" & Left([Minutes],InStr([Minutes],".")-1)

You may put all these into one formula but it would be a really long one.

Mike

Hello,
I am trying to create a query that subtracts "TimeOUT" from "TimeIN" fields to calculate the total time worked.

The "TotalTime" is a new field that is not in the table, only in the query.

I am attempting to use the DateDiff function to calculate this, but I need to return both hours and minutes.

Currently, it looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT])

It works this way to return the total time in hours only.

The DateDiff function works to return either hours (h) or minutes but I cannot figure out how to get both!

Perhaps someone has an answer or possibly a better way to get the results in hour:minute format for me??!!

 

[mhinkle]

slightly confused

Thanks Mike,
but how would i use all these in one formula in my access query?
I've never tried to string together several formulae on one field using the zoom box...
It seems like it should be easier to work with time in access and excel !

I was trying to use excel first but it had problems with subtracting time and negative numbers.

What I need to do is keep track of time worked for the week.
Then I need to subtract the time worked from 50:00 hours (minimum mandatory hours for my salary) to determine if I worked more or less than the 50 hours for the week.
Then, I need to keep a running total for the year...


ANY HELP HERE would be GREATLY appreciated!!
Thanks,
Matt.

 

[Jon K]

What I need to do is keep track of time worked for the week.
Then I need to subtract the time worked from 50:00 hours (minimum mandatory hours for my salary) to determine if I worked more or less than the 50 hours for the week.
Then, I need to keep a running total for the year...

Date/Time fields are internally double precision numbers. [TimeOut]-[TimeIn] returns the elapsed time in Days. As there are 24 Hours in a day, you can get the elapsed time in hours with the expression:
([TimeOut]-[TimeIn])*24

You can round it to e.g. 4 places of decimal like this:
Round(([TimeOut]-[TimeIn])*24, 4)

You can then use a Totals Query to group the records by week numbers and calculate the variance from the minimum mandatory hours.


As for the running total, it will be more efficient to show it in a report based on the Totals Query than in a query.

See the queries and report in the attached example.
.

 

[mhinkle]

Thanks Jon

Thanks Jon K.
The example you provided is a great help.
However, the "boss" is totally OCD, so he NEEDS the reports and query output for hours to display in hh:mm format.
The roadblock here is that negative time is a problem, so if I work less than the 50 hours, my display is horked up.
Any suggestions?
Mike had a seemingly good suggestion earlier in this thread, but I don't know how to get all that into one query, or if I should?!

 

[mlai08]

Hi mhinkle,

I know it would be messy to put everything in one formula. I have inserted formula into different fields in query qselTestTime and it should make things easier for you to follow. You may run another query qselTimeDiff_ForBoss and show it to your boss. Please see db attached.

Take the TimeDiff field on the first query to get a running total for each person and set a threshold of 50 hours on the total, say if the total is < 50 hours, do something...flag it or show a remark etc.

If you like, you may convert format of the running total to hour:min, taking similar approach used in qselTestTime.

Mike

 

[mhinkle]

Thanks Mike!
I'll give it a try...