I have 2 time, time work in at 12/01/2023 9:00:00 AM and time work out at 13/01/2023 6:00:00 AM. But before 22:00:00, my salary is 13.000 and after 23:00:00 is 15.000. How can i calculator time before 23:00:00 and after 23:00:00
i used if(C3>B3,(C3-B3),if(B3>C3,C3+1,C3)-B3), but it only return total worked time
You can use the IF and AND functions in Excel to calculate the time worked before 23:00:00 and after 23:00:00.
For example :
=IF(AND(C3>=B3, C3<TIME(23,0,0)), (C3-B3)*13000, IF(AND(C3>=TIME(23,0,0), C3<=TIME(24,0,0)), (C3-TIME(23,0,0))*15000, 0))
In this formula, the first IF statement checks if the time in cell C3 is greater than or equal to the time in cell B3 and less than 23:00:00. If that is true, it calculates the time worked before 23:00:00 by subtracting the time in cell B3 from the time in cell C3, then multiplies that time by the hourly rate of 13000.
The second IF statement checks if the time in cell C3 is greater than or equal to 23:00:00 and less than or equal to 24:00:00. If that is true, it calculates the time worked after 23:00:00 by subtracting 23:00:00 from the time in cell C3 and multiplying that time by the hourly rate of 15000.
You can then sum the results of the two IF statements to get the total pay for the time worked before and after 23:00:00