Time calculation

[rleighton]

Hello,

I am trying to figure out a simple (hah) time calculation and having hard time.
I have a start time and stop time for when people come in and want to figure out in formula how many hours they worked.
I tried different things and cant seem to get it to work.

ie
Monday Tuesday wednesd etc..
9:30 5:30 5:00 9:00 12:00 4:30

then I wanted to total them for the week.


anyone able to help please =)

Randy

 

[NateO]

This worked for me (assuming your times are accurate time numbers (i.e., 5:30 is 5:30 pm (not AM)).

Take subtotals for each day. e.g., =hour(B1-A1) where B1 is your time out. Sum your subtotals and format that cell as a number.

HTH. Cheers, Nate

 

[Guest]

Make the start and end time cells in this format:
Time - 1:30PM
If A1 is start time, and B1 is end time, put this formula in C1 for your solution:
=B1-A1
Make C1 this custom format (you'll have to create it):
[h]:mm

Worked for me.

 

[rleighton]

cool those suggestions work well, I like the custom Time setting too. Now if you want to make it more of a challenge, On some days the data is entered as OFF, which seems to muck up the whole thing

I was experimenting with IF and trying to nest an if statement, and it is quite challenging.

What I used to total up the persons hours was this:

=(C8-B8)+(E8-D8)+(G8-F8)+(I8-H8)+(K8-J8)+(M8-L8)+(O8-N8) Where the first number is the off time..
that works good until they have an off in one of the fields..

Now how could I add up those hours using IF to make a OFF day a 0.

this is what i was trying so far:
IF(E7<>"OFF",E7-D7,0, IF(D7<>"OFF",E7-D7,0))
but that dont work

Randy

 

[charlie79]

A6 being start time. Assuming they type in "off" for both start and end time, check against only one of the cells.

=IF(A6="off", "0:00", B6-A6)