Forum Discussion
Solved
Help with IF formula please
I am creating a spreadsheet to help me calculate our staff's payroll. I need a formula to help me work out the standard pay and overtime. So what I want excel to do is-
If the hours in cell A1 are 40 or less than 40, then cell A2 should equal the value of cell A1.
If the hours in cell A1 are more than 40 then cell A2 should equal 40.
If the next cell I would then work out how many hours of overtime have been worked. This would be any hours worked over 40 hours that week.
If anyone could please let me know what the formula would be I would be incredibly grateful - I am pulling my hair out with this!
Thank you 🙂
Thanks! Your values are not a number of hours such as 41, but a time value such as 41:00
With a total time in E9, the formula for regular hours is
=MIN(E9,40/24)
and the formula for overtime is
=MAX(E9-40/24,0)
Format the cells with these formulas as [h]:mm
A bit premature to post this since you are still debugging the original formula, but for 365 users, 'hours' would be an array so you would need to avoid MAX. Another formula for 'overtime' would be
= IF(hours>40, hours-40, 0)[subtracting that from 'hours' gives standard hours worked]. Occasionally it might be useful to have the standard and overtime hours in a single array, in which case
= LET( overtime, IF(hours>40, hours-40, 0), CHOOSE({1,2}, hours-overtime, overtime))- Thank you very much for your reply. Hans Vogelaar replied with a suggestion for his previous response which has worked perfectly. However, I will keep your kind response for future reference. Thanks again
- Thank you for your kind reply. For some reason that is not working. Would it be because the number in Cell A1 is created from a formula itself?
The formula in A1 should return a number, not a text value.
