# Thread: Calculating time worked over 24 hour period

1. Registered User
Join Date
Apr 2004
Location
Posts
6

## Unanswered: Calculating time worked over 24 hour period

My problem is I can't get my expression to return the time worked in hours and minutes, only hours. I am using the following expression:

IIf([StartTime]<[EndTime],DateDiff("h",[StartTime],[EndTime]),24-DateDiff("h",[Endtime],[StartTime]))

I substituted "n" for "h" and that returned a number, that when divided by 60, would not have given me the correct answer for total time worked.

I have formatted my date and time fields as General Date.

What do I need to change to have this work for me?

Thanks

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Using "n" should have worked for you...

did you remember to change the 24 to 1440 (24*60)?

3. Registered User
Join Date
Apr 2004
Location
Posts
6
No I didn't do that. I'll give it a try.

Thanks

4. Registered User
Join Date
Apr 2004
Location
Posts
6
Ran the expression with "n" and changed 24 to 1440 and that's giving me the answer I want.

Thank you very much!

Another question:
When I click on the query to open it, it's acting like a parameter query. It opens up asking me to enter a parameter value for "n". I input "n" and up comes my query.
I don't want to have it work this way. I prefer it to just open.
I'm thinking I need to add another field to the query representing the "n" value, so that it doesn't keep asking for it when I open it up???

Not sure just what I need to do.

I've been looking for some good books that I can reference to help me learn some of this "creating expression" stuff. Can you recommend one? or a detailed help site on the web?

Thanks again.

5. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Could you post the exact expression as it is now?

"n" is an argument to the datediff function, it shouldn't be evaluated as a parameter, that's a bit odd.

6. Registered User
Join Date
Apr 2004
Location
Posts
6
Hours Worked: IIf([StartTime1]<[StartTime2],DateDiff([“n”],[StartTime1],[Endtime2]),1440-DateDiff([“n”],[Endtime2],[StartTime1]))

I noticed when I pasted this expression in that I had an error in the first portion of it.
IIf([StartTime1]<[StartTime2],

According to the original expression I pulled off the web help site, I should have EndTime2 in the second half of this. I went back and changed it, but it returns the incorrect time worked?

I attached the database so you can see what I've done.

When these guys go on shift, they count the time worked as the entire time they're away. They still need to keep track of their actual "working" hours spent on the train separate from their time spent at the bunkhouse, so hence the 2 different date and time fields.

Thanks again for your help.
Cheryl

7. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
remove the brackets from ["n"]

the []'s explicitly tell access that it's supposed to be a field or parameter.

just "n" should be fine.

8. Registered User
Join Date
Apr 2004
Location
Posts
6
I initially created the expression without the brackets.

As soon as I switch to datasheet view, it puts them back and before it will give me datasheet view, it asks for the "n" parameter again.

Do I need to adjust a setting in Access perhaps?

Thanks

9. Registered User
Join Date
Apr 2004
Posts
66
I put just single quotes around it
'n'
and it seemed to work.

Susan

10. Registered User
Join Date
Apr 2004
Location