AUTO CALCULATE PAY FOR OVERTIME WITH MULTIPLE CONDITIONS

Occasional Visitor

Hi All,

 

Please i need help with excel formula to calculate overtime pay with multiple conditions. I will be entering the start and end time. Is there a way for the formula to connect to the calendar where it can detect if its a weekday, weekend or public holiday?

 

Below are the rates and different conditions, there are different rates for Weekday, Saturday, Sunday and Public Holiday. There are also different rates based on the time they worked.

 

Kath0427_0-1663136844718.png

 

2 Replies
You need XLOOKUP() function. Share a sample workbook and show your desired output.

@Kath0427 

Here is an example in the attached file.

formula examble: =IF(C2<B2,(((C2+1)-B2)*24)*D2,((C2-B2)*24)*D2)

 

Hope I could help you with this information / file.

 

NikolinoDE

I know I don't know anything (Socrates)