Excel timesheet formula to account for days worked > 5 and lunch

**shanecline** · 11-09-2012, 03:58 PM

I have a spreadsheet that keeps track of work hours. Their are two cells for everyday of the week ("in" and "out"). At the end I have a cell for "total hours", I have a formula in that cell to add all the hours for the whole week, but I need something else. I need the formula to account for days that an employee works 5 or more hours and subtract 30 minutes or 0.5 hours for a lunch, but only on those qualifying days.

here is the formula I currently have (this is only for a single day of work)

=24*(IF(B6>C6,C6+1-B6,C6-B6))

"B6" is the "in" time and "C6" is the "out" time.

How can I add to this formula to say that if that day is >=5 then subtract 0.5?

Please... Somebody help me!!!

**TheExcelphile** · 11-09-2012, 04:08 PM

This should work:

=IF(24*(IF(B6>C6,C6+1-B6,C6-B6))>5,24*(IF(B6>C6,C6+1-B6,C6-B6))-0.5,24*(IF(B6>C6,C6+1-B6,C6-B6)))

http://theexcelphile.com/learning-ex...if-and-no-but/

Don't forget to click on the little star to the left of this post if you feel I helped!

**shanecline** · 11-10-2012, 05:27 AM

It worked!!! but how do I string together the rest of the days?

If you would be so kind... here is the whole string if you could edit it. Its returning False. Also cell "K6" wont let me enter a time, it changes to a random decimal.

=IF(24*(IF(B6>C6,C6+1-B6,C6-B6))>5,24*(IF(B6>C6,C6+1-B6,C6-B6))-0.5,24*(IF(B6>C6,C6+1-B6,C6-B6)))=IF(24*(IF(D6>E6,E6+1-D6,E6-D6))>5,24*(IF(D6>E6,E6+1-D6,E6-D6))-0.5,24*(IF(D6>E6,E6+1-D6,E6-D6)))=IF(24*(IF(F6>G6,G6+1-F6,G6-F6))>5,24*(IF(F6>G6,G6+1-F6,G6-F6))-0.5,24*(IF(F6>G6,G6+1-F6,G6-F6)))=IF(24*(IF(H6>I6,I6+1-H6,I6-H6))>5,24*(IF(H6>I6,I6+1-H6,I6-H6))-0.5,24*(IF(H6>I6,I6+1-H6,I6-H6)))=IF(24*(IF(J6>K6,K6+1-J6,K6-J6))>5,24*(IF(J6>K6,K6+1-J6,K6-J6))-0.5,24*(IF(J6>K6,K6+1-J6,K6-J6)))=IF(24*(IF(L6>M6,M6+1-L6,M6-L6))>5,24*(IF(L6>M6,M6+1-L6,M6-L6))-0.5,24*(IF(L6>M6,M6+1-L6,M6-L6)))

You are a life saver!

**TheExcelphile** · 11-10-2012, 07:25 AM

I think the best thing for you would be to have 5 cells, one for each day of the week, calculating the time for that day only. Once you have those 5 cells, you can simply add them up in a sixth cell to get your total time.

Regarding cell K6, I think you need to change the format of the cell. Select that cell, and press Ctrl + 1. Choose any time format you want.

If you upload a sample workbook, I could come up with a better solution.

Don't forget to click on the little star to the left of this post if you feel I helped!

**shanecline** · 11-10-2012, 01:48 PM

Are you telling me that it is impossible for me to have it how I want?
the way I have it setup is nice.
the work week is 6 days Monday - Saturday, I have two cells for each day for in and out times and those cells have "drop down list"
boxes to make it easy to choose the times. If I were to only put 6 cells for the days of the week there would be too many possibilities to use the drop down lists. I don't want to have to type them all in manually.

Your code worked perfectly, but only for a single day... isn't there a way to add them all together? I really want to use the code you have given me, if there is any way to add them all together, my project would be complete.

this is the code I used previously
=24*(IF(B7>C7,C7+1-B7,C7-B7))+24*(IF(D7>E7,E7+1-D7,E7-D7))+24*(IF(F7>G7,G7+1-F7,G7-F7))+24*(IF(H7>I7,I7+1-H7,I7-H7))+24*(IF(J7>K7,K7+1-J7,K7-J7))+24*(IF(L7>M7,M7+1-L7,M7-L7))

can't you just string your code together the way this one did?
I can try to upload a sample, but if you can give me a solution to what I want it would be preferred, thank you for your wisdom and
patience.

**TheExcelphile** · 11-12-2012, 03:16 AM

I can't really give you the solution you want without taking a look at your sheet! Upload a sample, then we can work on it.

**shanecline** · 11-13-2012, 04:09 PM

Okay, I got the formula to work for me... but now I need something else.
Each cell has a drop down list box with the following
off
8:00 am
8:30 am
9:00 am
etc...
I then have other cells calculate the in and out times and deduct 30min for lunch on days that are over 5 hours.
=IF(24*(IF(D6>E6,E6+1-D6,E6-D6))>5.5,24*(IF(D6>E6,E6+1-D6,E6-D6))-0.5,24*(IF(D6>E6,E6+1-D6,E6-D6)))

but now for that pesky "off" option.
if someone selects "off", it messes up the calculation because its a word. I need to add to the formula above to
first check if the 2 cells = "off" then I want it to treat "off" as 0 instead of a word and complete the formula.
Is it possible to do that? something like (IF D6,E6 = "off" then D6,E6 = 0 and then complete the rest of the formula.)
this is a tricky one.

Side problem... I also want it so if you choose "off" on D6 then E6 automatically populates with "off" as well.