# Timesheet without break times

1. ## Timesheet without break times

I need help to make a spreadsheet to figure work times. There is a twist, because I have to subtract break and lunch time. As you can see from the attached file, I have the break and lunch times in cells F7:F9, this is because different people have different times. If the start time is before break 1 and stop time before break 1 then the time at F4 will be right, if start time is before break 1 and stop time is after break 1 then the correct time will be F4 –I7(15 minutes). At this point I keep coming up with a #value error. I also have to cover the lunch break and break 2, with starting times all though the day. This is to keep track of time on different work orders and break and lunch times are not included in the total time per job. As I have said before, I’m self taught and my teacher is an idiot so any help will be very much appreciated.

2. Hi,
do you mean that different people have different break and lunch times ?
I changed your formula as follows
=IF(AND(start<F7;stop<F7);F4;IF(AND(start<F7;stop>F7);F4-I7;""))

but if the break and lunch times are diff it will have to be a little more elaborated

3. Hi,

Try something like this:

=F2-F3-(\$F\$3<F7)*(\$F\$2>F7)/96-(\$F\$3<F8)*(\$F\$2>F8)/48-(\$F\$3<F9)*(\$F\$2>F9)/32

Some explanation: 1/96 of a day is 15 minutes, 1/48 is half an hour and 1/32 is 45 minutes.

- Asser

4. Thanks for the replys, and yes different people have different break and lunch times, some break at 9:00 - 10:15 for 15 minutes and lunch is at 10:30 - 1:00 for 30 minutes. Second break is at 2:00 - 3:15 again for 15 minutes. This is why I put the break and lunch time in cells F7:F9 so people could copy spread sheet and put their own time in. I want to be able to put any start time from 6:00 AM to 3:30 PM and have the formula figure the time minus the break or lunch times if in the time slot.

Jazzer I tried your formula and changed the last number from 32 to 96 so that that time fram subtracted only 15 minutes and so far the thing worked great. Thanks very much for the help and now I can disect the formula to see what I did wrong before. Tell your boss I said to give you a raise. Now that I found this web site I'll be like the Terminator and I'll be back.

5. Hi village
if no one has come up with a solution I'
I' ll work on it tomorrow. But I think you'll have to add 2 data tables, one with the pause and lunch hours and one for your employees relating to this first table
Then, combining Index and Match functions retrieve the data for each employee, the formula can be worked out
Cheers

6. Thanks for your help, but as my last post says, I got it done. If you want to you can go ahead and do your thing. That way I will have even more stuff to dissect and learn more good stuff. Thanks for your help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1