Hi Joe,
What is it you want to work out? My formula calculates money. So, where in your imaginary sample sheet is the Basic Pay?
What do you have in B1? Hours, right?
What is in C1 and D1? Is that also hours? Or do you want the result there?
Note that my formula needs one number of hours and calculates one amount of money. So, it doesn't need the first hour to be separated out, nor can it supply two results. If you need to keep these items separate the formulas should be changed. They will be simpler.
Where would you like the result to go? I already asked if you want results in C1 and D1. If not, it doesn't really matter. But I need to know how many amounts you want to work out.
It isn't good practice to integrate static variables invisibly into formulas. The worksheet should show what was calculated. You have 3 static variables in your calculation: 173, 1.5 and 2. You may like to put these three items in some cells at the top of your sheet, with a description in the neighbouring cell, such as "Factor for 1st hour of daily OT", "Factor for 2nd and more hours of daily overtime" etc. The formulas would then refer to the cells where you keep the values and, in future, if there is achange you just change the cell value and continue using the same formulas.
Let us deal with the holidays after we have done the normal days. OK.
Have a great day!
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)