Creating a Timesheet
Posted by Jamie on March 08, 2000 1:29 PM
I'm trying to create a timesheet and all is going well except one area. If an employee works inbetween the hours of 12:00am through 7:00am, they are entitled to shift differential but, only if they are there for more than one hour inbetween those times. For example, working 12:00am to 9:00am would entitle 7 hours of shift diff., working 8:00pm to 4:00am would entitle 4 hours of shift diff., and etc. However, if one works from 5:00pm to 1:00am or 6:00am to 2:00pm, one would not get any shift diff because you must work at least more than one hour inbetween the hours of 12:00am through 7:00 to be eligible for the shift diff. Any and all help will be greatly appreciated.
Posted by Jaime on March 08, 2000 3:01 PM
There must be an easier way but i gues i am not familir with time problems in Excel. The followong formula is assumed the start and end time are in cells A1 and B2 respectively. Also they are just times with no dates. If u had dates in the same cell it would be easier.
=HOUR(MOD(B1-A1,1))-HOUR(MOD(0-A1,1))*(A1>0.29)-HOUR(MOD(B1-0.29,1))*(B1>0.29)
'This firmula goes in cell c1
this formula gives u the hours between ur target shift. In the cell next to this place this formula:
=IF(C1>1,C1,0)
'this formula goes in cell d1
I can place everthing in one cell but it would be even more confusing.
Good luck
Tell me if it works:)
Posted by Jamie on March 08, 2000 4:01 PM
Thank You...it works well with one exception. When I enter in anything other than top of the hour times, it does not calculate the extra time. For example, with your formula, if I enter 11:00pm to 3:00 am, it kicks back 3 hours which is perfect but, if I enter 11:00pm to 3:30am, it still only kicks back 3 hours instead of 3.5. How could I change the formula to kick back that extra 1/2 hour?
Thank you again.
Posted by Jaime on March 08, 2000 7:50 PM
Yeah, I see why. It was the hour function. Replace the old one in cell C1 with
this new function. It will give u the exact time to the minute.
=(MOD(B1-A1,1))-(MOD(0-A1,1))*(A1>TIME(7,0,0))-(MOD(B1-TIME(7,0,0),1))*(B1>TIME(7,0,0))
Posted by Jaime on March 08, 2000 7:54 PM
Yeah, I see why. It was the hour function. Replace the old one in cell C1 with
this new function. It will give u the exact time to the minute.
=(MOD(B1-A1,1))-(MOD(0-A1,1))*(A1>TIME(7,0,0))-(MOD(B1-TIME(7,0,0),1))*(B1>TIME(7,0,0))
Posted by Jamie on March 10, 2000 8:03 AM
It's Beautiful...
That last formula works perfectly and is exactly what I needed.
Thanks again for your help with this, it is greatly appreciated.
Posted by Jaime on March 10, 2000 8:32 AM
Re: It's Beautiful...
Anytime :^>
