Mar 22 2021 07:44 AM - edited Mar 22 2021 08:27 AM
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 :)
Mar 22 2021 08:00 AM
Mar 22 2021 08:26 AM
Mar 22 2021 08:45 AM
The formula in A1 should return a number, not a text value.
Mar 22 2021 08:49 AM
Mar 22 2021 09:07 AM
Could you attach a small sample workbook without sensitive information that demonstrates the problem?
Mar 22 2021 10:30 AM
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))
Mar 23 2021 02:19 AM
@Hans Vogelaar here is the spreadsheet so far. Thank you SO much for your help.
Mar 23 2021 05:01 AM - edited Mar 23 2021 05:01 AM
SolutionThanks! 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
Mar 23 2021 07:59 AM
Mar 23 2021 08:00 AM
Jun 16 2022 05:42 PM
Mar 23 2021 05:01 AM - edited Mar 23 2021 05:01 AM
SolutionThanks! 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