+ Reply to Thread
Results 1 to 6 of 6

Timesheet without break times

  1. #1
    Registered User
    Join Date
    04-13-2007
    Posts
    19

    Smile 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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    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. #3
    Forum Contributor
    Join Date
    01-05-2004
    Location
    Helsinki, Finland
    Posts
    100
    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. #4
    Registered User
    Join Date
    04-13-2007
    Posts
    19
    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.
    Last edited by village_idiot; 05-03-2007 at 10:22 AM. Reason: add to it

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    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. #6
    Registered User
    Join Date
    04-13-2007
    Posts
    19
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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