Help with IF formula please

[Rachael_Dart]

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

[#2]

@Rachael_Dart 

In A2:

=MIN(A1,40)

In A3:

=MAX(A1-40,0)

[#3]

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?

[#4]

@Rachael_Dart 

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

[#5]

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.
Thank you for your help.

[#6]

@Rachael_Dart 

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

[#7]

@Rachael_Dart 

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

[#8]

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

[#9 · Accepted solution]

@Rachael_Dart 

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

[#10]

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

[#11]

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

[#12]

Hi! Pls check my question: https://techcommunity.microsoft.com/t5/excel/please-help-me-with-formula-in-excel/m-p/3518804