Calculate overtime outside core hours

[Val C]

Hello, I'm very new at this so I hope I make myself very clear. I'm creating a timesheet for each employee where it shows the time in each day and time out each day with a lunch break. What I'm having trouble is with calculating any overtime done by staff outside of the core hours of business. Our business operates Monday to Friday with the core hours being 06:00-20:00 each day with regular hours of 7.6 hours per day. I've attached a copy of my timesheet for anyone's comment on how I can do this. As you will see an average day of work would be 8:30 to 17:00 with 55 minutes for lunch. What I need is the extra time worked within the core hours and the overtime outside the core hours.

Thanks,

[TonyB51]

Hi,

I have modified your practice sheet to test whether start times or finish times are outside core hours, by adding additional IF's.

I hope this works for you.

Cheers

TonyB

[Val C]

That works, thanks so very much.

[Val C]

That works, thanks so very much.

It all works fine except for the very last working day Friday which shows a result of 0 when it should show 0.5. Am I doing something wrong?

Thanks
Val

[TonyB51]

Hi, Val

Start is After 6:00 am and Finish is before 20:00.

11/06/2010 Friday 7:30 13:00 13:54 18:30 10.1 0

Did you want excess of 7.6 hours per day treated differently?
Did you want excess of 76 hours to add as OT?

Cheers

TonyB

[Val C]

Hi, Val

Start is After 6:00 am and Finish is before 20:00.

11/06/2010 Friday 7:30 13:00 13:54 18:30 10.1 0

Did you want excess of 7.6 hours per day treated differently?
Did you want excess of 76 hours to add as OT?

Cheers

TonyB

Your right TonyB, I confused myself. Sorry for the trouble. I appreciate everything you have done. Just checked everything and all is Fantastic. Thanks so much.

[Val C]

Your right TonyB, I confused myself. Sorry for the trouble. I appreciate everything you have done. Just checked everything and all is Fantastic. Thanks so much.

I need help again. I've extended my timesheet to include Public Holidays, Annual Leave and Sick Leave. If I don't record a time in but instead put 7.6 in say public holiday for the day I get overtime showing 6. How can I fix this? I've attached timesheet practice 2 so you can see what I am talking about.

Thanks,
Val C

[Val C]

Extending time sheet hours

[Lili Robinson]

Hi The thread above has been useful, however trying to revise it to meet my needs has been time consuming.
What I am looking for is a basic timesheet for an employee where it shows the time in each day and time out each day with a lunch break (as per spreadsheet perfect).
For me, business operates Monday to Friday each day with regular hours of 7.6 hours per day.
What I need is the extra time worked column to calculate on an accrual basis so I can see clearly whether I am owing time in lieu or need to make up time in lieu. Also at the end of the week a balance to be carried forward to the next week. thank you Lili

[arlu1201]

Lili,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[Lili Robinson]

Thanks for feedback so quickly.

New to this website, I'm struggling with how to use properly.

Adding a link, posting new thread example. But will keep trying

Lili,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.