SOLVED

Calculate wage by hours

Occasional Contributor

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

8 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@MASB70 

It 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.

Hans, thanks for your response.
I will try to be more exactly, please check the next example:
A. B. C. D
1 Name Clock In. Clock out. Wage
2. Julia. 19:00. 3:00 ?
3. Andrea. 21:00 4:00. ?

Wage hours
From 19:00 to 1:00 ¥2,000. From 1:00 to 5:00 ¥2.500

I will appreciate your help. Thank you
MASB70
Hans
Thank you so much, the formula is working. Only, cells without hours, the formula give a negative value.
Thank you so much.

@MASB70 

Sample workbook attached

Thank you very much for your time.

@Hans Vogelaar 

 

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.

Screen Shot 2022-04-11 at 15.12.27.png

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

Hans,

I just checked your formula and the excel file attached. Thank you so much for your help, I think it will help me a lot at work.
Again, thank you so much for your time.
Regards.