1. 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. Re: time sheet challenge (xp)

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. 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. 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. Re: time sheet challenge (xp)

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

6. 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. 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. Re: time sheet challenge (xp)

=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. 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
•