Results 1 to 9 of 9
Thread: time sheet challenge (xp)

20040925, 23:12 #1
 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

20040926, 00:04 #2
 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:
=D8C8+(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:
=D8C8+(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:
=D8C8+(D8<C8)  E8
Steve

20040926, 00:40 #3
 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?

20040926, 01:17 #4
 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))

20040926, 02:04 #5
 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

20040926, 07:33 #6
 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:
=D8C8+(D8<C8)E8
C8=START TIME
D8=END TIME
E8=DAY HOURS
F8=NIGHT HOURS
Thanks, Dave

20040926, 10:51 #7
 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.

20040926, 11:58 #8
 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(D8IF(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.

20040926, 14:33 #9
 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>