# Timesheet Calculation for Shift workers

1. ## Timesheet Calculation for Shift workers

Hoping to get some more help here today with my timesheet as I did yesterday.

As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.

Any help would be greatly appreciated.

Cheers

2. ## Re: Timesheet Calculation for Shift workers

since normal hours end at 22:00. test if the worker finished after 10, then in normal hours subtract 22-(start time)*24, after midninght hours should be end time *24 if the exit time < than start time. use the total hours subtracted from normal hours and after midnight hours to calculate the late hours.

3. ## Re: Timesheet Calculation for Shift workers

It's not very refined or elegant, and there's probably a better way to do it, but this works:Penalty Hours Worked Test.xlsx

4. ## Re: Timesheet Calculation for Shift workers

Thanks so much for your replies rcm & gak67. Very helpful and I am definitely getting somewhere now. Both calculations work with that one particular scenario but if I have different start/ finish times, then I cant get it to work quite right.

I have attached another spread sheet with your suggested calculation RCM, it did work perfectly until I put in an end time that is less than 22:00 then it throws it out (Note: I have highlighted the incorrect calcs in red). I tried adjusting it, then that scenario worked but another didn't. Any suggestions? I think I have now been looking at it too long now that it is probably something simple that I just cant put my finger on!

Any further suggestions would be greatly appreciated.

Looking forward to hearing back

5. ## Re: Timesheet Calculation for Shift workers

A simple change to cells in D fixed the normal hours rule, and conditional formatting of the penalty columns to hide 0 or <0 values cleans the sheet up

6. ## Re: Timesheet Calculation for Shift workers

Find the attached file

7. ## Re: Timesheet Calculation for Shift workers

Thanks for your reply dubcap01, however I dont think I was clear with what normal hours represent. Normal Hours is time worked between 7am to 10pm. Normal meaning no penalty rates. Total Hours is the employees total hours for that shift.

Therefore, for example, cell D2 should equal 5. 5 hours between 5pm to 10pm. Cell E2 should equal 2 (which is correct), and cell F2 should equal 1.5 (also correct). So in this instance, its the normal hours which I am having trouble with.

The three time frame examples all need to work as they are all examples of hours that an employee may work in any given week.

Hoping you may have another suggestion?
Thanks

8. ## Re: Timesheet Calculation for Shift workers

Thanks nflsales! That works perfectly. I didnt realise when I first looked at it as the hours calcs were in time format so I just multiplied all by 24 which worked.

Thanks again, I really appreciated your help

TRy it now

10. ## Re: Timesheet Calculation for Shift workers

Fantastic! Thanks again rcm

There are currently 1 users browsing this thread. (0 members and 1 guests)