Time calculation

rleighton

New Member
Joined
Feb 27, 2002
Messages
2
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top