# Help with overtime formula

1. ## Help with overtime formula

Hi Lovely people,
You have been wonderful in helping me previously and I require some more assistance please?

I have a workbook with calculations for working out overtime. Unfortunately not everybody works 9-5 8 hours daily Mon-Fri, so the formula I have doesn't cover everyone.

I have the formula for the Mon-fri 8 hours daily 49 Hours per week lot.
Also Mon-Thurs 9.15 hours daily 49 hours per week.

What I need is:
Tues-Fri 9.15 hours daily 49 hours per week
and
Mon. Tue, Thurs, Fri 8 hours daily 28 hours per week.

We sometimes come in on weekends and days that are normally off so need them to add hours to the overtime total too.

I have attached a workbook with my existing formula and pages where I would like the other formulas.

Thanks
Kirsten

2. ## Re: Help with overtime formula

Use this general formula where "0010011" represents day of week, from Monday to Sunday:
=IF(C4="","",MAX(0,F4-TIME(8,0,0)*NETWORKDAYS.INTL(A4,A4,"0010011")))
"0" means working day and "1" means rest day.
WorkRestDay.png

3. ## Re: Help with overtime formula

Change the time from TIME(8,0,0) to TIME(9,15,0) where necessary.

4. ## Re: Help with overtime formula

Hi Josephteh,

Thanks for getting back to me. I will try that.

Kirsten

5. ## Re: Help with overtime formula

Hi Josephteh,

When I put that in the overtime column without changing any of the other formula I'm just getting 'value' in the columns. Do i need to change anything else?

Thanks
Kirsten

7. ## Re: Help with overtime formula

HI,

When I put the formula in as is, it seems to be OK but when i change the day I'm getting a value entry.

My changed formula =IF(C4="","",MAX(0,F4-TIME(8,0,0)*NETWORKDAYS.INTL(A4,A4,"00000111")))

8. ## Re: Help with overtime formula

Why so many digits between the apostrophe?

9. ## Re: Help with overtime formula

In other words, try this:

=IF(C4="","",MAX(0,F4-TIME(8,0,0)*NETWORKDAYS.INTL(A4,A4,"0000111")))

There are seven days in a week, so you need seven digits.

10. ## Re: Help with overtime formula

This is an overtime formula, why did you put under total column?

11. ## Re: Help with overtime formula

Hi,

Thanks I see what I did with the digits and sorry I thought it was for the totals column, will change that now.

12. ## Re: Help with overtime formula

You could also simplify the formula for Total column: =IF(C4="","",SUM(G\$4:G4)-SUM(H\$4:H4))

What is the significance of 28 or 49 hours per week?

13. ## Re: Help with overtime formula

That's fixed those problems, thanks.

I think that's everything sorted now. Thank you so much for your help, I can do basic formula but these are way over my head.

Kirsten

14. ## Re: Help with overtime formula

Cool, thank you.

15. ## Re: Help with overtime formula

Hi,

Sorry, I seem to have hit a snag when i changed the off day to the Wednesday and the hours to 8.

If I put hours in on the off day it is still calculating to take time back when it should just add the total hours and when I put a shorter time in a normal day (to take time back) it's not calculating that at all.

It worked fine with the Friday as the day off and 9.15 hours.

Thanks
Kirsten

16. ## Re: Help with overtime formula

What is Time taken back?

17. ## Re: Help with overtime formula

Hi Joasephteh,

When we do overtime we don't get paid for it but get it back as Time in Lew, so that column needs to be taken away from the total as and when we work less than out 8 (or 9.15) hours in a day.
So as you can see in example 4 I put in 9-12 which is a 3 hour day, that should have been noted in the time taken back column and taken off the total. Also with hours worked on an off day there should be no time taken back, with the total hours of that day added to the Total.

18. ## Re: Help with overtime formula

Still don't understand. Can you show with examples please?

19. ## Re: Help with overtime formula

All the formula including yours are working find on the sheet where the day off is the friday and the hours per day are 9.15 but when I've changed the day off and number of hours it's caused the problems.

20. ## Re: Help with overtime formula

Try in H4, copy down:
=IF(C4="","",MAX(0,(TIME(8,0,0)-F4-(TIME(1,0,0)-D4))*NETWORKDAYS.INTL(A4,A4,"0010011")))

21. ## Re: Help with overtime formula

Perfect that worked for both issues.

Thanks

22. ## Re: Help with overtime formula

Glad to have helped and thanks for the Rep!

23. ## Re: Help with overtime formula

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1