SOLVED

# Help with IF formula please

Occasional Contributor

# 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

11 Replies

In A2:

=MIN(A1,40)

In A3:

=MAX(A1-40,0)

# Re: Help with IF formula please

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?

# Re: Help with IF formula please

The formula in A1 should return a number, not a text value.

# Re: Help with IF formula please

Yes it does return a number. However, when I paste in the formula that you have very kindly posted, I am getting an answer of 41 which I should be getting an answer of 40. I am a bit stumped.

# Re: Help with IF formula please

Could you attach a small sample workbook without sensitive information that demonstrates the problem?

# Re: Help with IF formula please

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))``````

# Re: Help with IF formula please

@Hans Vogelaar here is the spreadsheet so far. Thank you SO much for your help.

best response confirmed by Rachael_Dart (Occasional Contributor)
Solution

# Re: Help with IF formula please

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

# Re: Help with IF formula please

Fantastic!!! That has sorted it. Thank you SO much!!

# Re: Help with IF formula please

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