Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    time sheet challenge (xp)

    Here's a challenge for anyone. I have been working on this for weeks and now I finally broke down. I have a start time and an end time column (formatted as h:mm) I need to calculate and set it up so that the time is split into Day hours and Night hours. I was able to figure out and work up a formula with some help. The formula appears to be working correctly for the day hours (6am to 6pm). Can someone assist me in this challenge to figure out a formula for the night hours.

    =IF(OR(C8<=18/24,D8>6/24),MIN(IF(D8<6/24,18/24,D8),18/24)-MAX(IF(C8>18/24,6/24,C8),6/24),0)

    Thanks, Dave

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: time sheet challenge (xp)

    How about this?

    The total time difference is:
    =D8-C8+(D8<C8)

    It takes the difference (adds 1 if D8<c8 assuming it is the next day)
    And since your calc gives the day time, the "night hours"
    = total - day hours

    So you can use:
    =D8-C8+(D8<C8) -IF(OR(C8<=18/24,D8>6/24),MIN(IF(D8<6/24,18/24,D8),18/24)-MAX(IF(C8>18/24,6/24,C8),6/24),0)

    Or if the dayhours is stored in cell (eg E8) you could just use:
    =D8-C8+(D8<C8) - E8

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time sheet challenge (xp)

    Thanks, for the help, I plugged in the formulas and it calculated the day and night hours correctly. One thing I did not realize was that when only night hours are plugged in (7pm to 3am) my original formula gives me 12:00 in the day column instead of 0, which in return gives me ######### in the night column. Any ideas on a quick fix?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time sheet challenge (xp)

    If you never have somebody working from, say 3 PM on one day to 10 AM the next day, you could use this for the day hours:

    =IF(OR(AND(C8<6/24,D8<6/24),AND(C8>18/24,D8>18/24)),0,IF(OR(C8<=18/24,D8>6/24),MIN(IF(D8<6/24,18/24,D8),18/24)-MAX(IF(C8>18/24,6/24,C8),6/24),0))

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time sheet challenge (xp)

    Thanks, it fixed the #######, but I do need it to cover up to 20 hours.
    Dave

  6. #6
    New Lounger
    Join Date
    Sep 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time sheet challenge (xp)

    Can someone look at and disect this formula. My only problem with this formula is that when the start time = 5am and the end time = 4am, the day hours show 0 and the night hours show 23. I have been over this formula but can not find the flaw that prevents the day/night hour to show correctly. I think something is missing but don't know what. I included both formulas just in case.

    Day hour formula:
    =IF(OR(AND(C8<6/24,D8<6/24),AND(C8>18/24,D8>18/24)),0,IF(OR(C8<=18/24,D8>6/24),MIN(IF(D8<6/24,18/24,D8),18/24)-MAX(IF(C8>18/24,6/24,C8),6/24),0))

    Night hour formula:
    =D8-C8+(D8<C8)-E8

    C8=START TIME
    D8=END TIME
    E8=DAY HOURS
    F8=NIGHT HOURS

    Thanks, Dave

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: time sheet challenge (xp)

    There is probably an easier way of doing this but one way of fixing it is to add another IF statement that checks for start time before 6am and end time less than the start time. It then sets the daytime hours to 12.
    To avoid problems with excessively wide posts I have split the formula over 2 lines.

    <code>=IF(AND(C8<6/24,D8<C8),12/24,IF(OR(AND(C8<6/24,D8<6/24),AND(C8>18/24,D8>18/24)),0,
    IF(OR(C8<=18/24,D8>6/24),MIN(IF(D8<6/24,18/24,D8),18/24)-MAX(IF(C8>18/24,6/24,C8),6/24),0)))</code>

    The new part of the formula is the <code>"IF(AND(C8<6/24,D8<C8),12/24,"</code> at the beginning plus an extra closing parentheses at the end.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time sheet challenge (xp)

    How about this for the day hours?

    =MAX(MIN(D8+(D8<C8),18/24)-MAX(C8,6/24),0)+MAX(D8-IF(D8<C8,6/24,1),0)

    This one even works if the start and end time include parts of two day periods, e.g. start time = 3 PM end end time = 9 AM: this includes a day part from 3 PM to 6 PM, and a day part from 6 AM to 9 AM the next day.

  9. #9
    New Lounger
    Join Date
    Sep 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time sheet challenge (xp)

    <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31> It works!!!!!!!! I plugged in both formulas and both of them work the way I wanted them to. I can finally put closure to this work sheet and all my frustrations. I could not have done this without the advise and support of everyone that responded to my post. Thank you all. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I ultimately used the formula Hans provided for my project, so, Thanks again. Back to business for me. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

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