Apr 02 2022 10:45 PM
Hi everyone,
I need a function that calculates the wage by hours. Example
Andrea worked from 19:00 - 4:00 am
From 19:00 -to 1:00 the wage per hour. is 2,000
ƒrom 1:00 - 4:00 the wage per hour is 2.500
Is there only one function for this problem?
Thank you
Apr 03 2022 04:03 AM - edited Apr 03 2022 04:03 AM
SolutionIt would be better if you provided the general rule, but with your example:
Let's say start time is in A2 and end time in B2.
=24*MOD(TIME(1,0,0)-A2,1)*2000+24*(B2-TIME(1,0,0))*2500
Format the cell with the formula as General or as Currency.
Apr 03 2022 05:46 AM
Apr 03 2022 06:13 AM
Apr 03 2022 07:10 AM - edited Apr 03 2022 07:11 AM
Sample workbook attached
Apr 10 2022 11:16 PM
Hans,
let me ask you about the formula, I realized the formula presents a problem when the schedule does not exceed 24:00 hours, it calculates the wrong salary amount, Please, check the attached.
I will appreciate your help.
Thank you.
Apr 11 2022 04:26 AM
@MASB70 Here is a modified formula and demo workbook.
=24*((MIN(TIME(12,0,0),MOD(C2-TIME(13,0,0),1))-MIN(TIME(12,0,0),MOD(B2-TIME(13,0,0),1)))*2000+(MAX(TIME(12,0,0),MOD(C2-TIME(13,0,0),1))-MAX(TIME(12,0,0),MOD(B2-TIME(13,0,0),1)))*2500)
Apr 17 2022 03:12 AM
Apr 03 2022 04:03 AM - edited Apr 03 2022 04:03 AM
SolutionIt would be better if you provided the general rule, but with your example:
Let's say start time is in A2 and end time in B2.
=24*MOD(TIME(1,0,0)-A2,1)*2000+24*(B2-TIME(1,0,0))*2500
Format the cell with the formula as General or as Currency.