double time formula for timesheet

**techmob** · 09-13-2009, 07:37 AM

Hi All

i am new here and i have come to this site to look for answers to my problem and possibly help from more experienced people

i am struggling with excel, i am new to it and part of my new job is to add double time to our companies time sheets, i am finding this very difficult so if anyone with excel skills can help, i would really appreciate it

i will give some info on what i need to have, and also provide the original time sheet we are using now, which does not calculate double time..

and i will also provide an example of why we need the double time to work, and what happened with our time sheet

these are the times for double pay

For Any Retail/Operations/Security Employee (Company Wide):

From 1800 hrs Christmas Eve to end of shift – Double Time

Christmas Day (to midnight) – Double Time

From 1800 hrs New Years Eve to end of shift – Double Time

New Years Day (to midnight) – Double Time

here are some time sheet screen shots,for security and club (which are part of our departments) which give examples of our problem with the current time sheet and what adjustments they needed to do

for example look at the club time sheet
he worked on the 31st of December, came into work at 11.30 and finished at 18.00 that would be normal pay because double pay does not start until 18.00 hours on New years eve, he does not have any breaks so nothing is deducted and then he starts work again (within the double pay time period) of 18.30 until 03.00am which is double time.

i need something in the already made time sheet to produce the correct amount of hours of 23.5 hours worked rather than the 15hrs worked with the current time sheet. any breaks would just deduct the time from the hours that is already in the time sheet

the person who checks these time sheets and does the wages has to manual check each time sheet and make adjustments, we are trying to make the double time automated

=(R10>S10)*MEDIAN(0,S10-R41,R42-R41)+MAX(0,MIN(R42,S10+(R10>S10))-MAX(R41,R10))

this has been used in the past but on christmas day for example double time is paid from midnight to midnight. The 24 hour duration of christmas day. If i enter 00:00 as the start time and 24:00 as the end time i dont get the expected result.

An employee might work from 20:00 until 03:00 and the double time should stop at midnight and dosn't when i enter the above. If i enter 23:59 then it works its just that extra minute that throws it out.

can anyone help with this?

thanks