How to Calculate Overtime in Excel for Early and Late Shifts in Mumbai Warehouse?

Dear Seniors,

Please let me know if overtime is applicable and how to calculate it in Excel based on employees' salaries. The warehouse is in Mumbai.

Work Time

Mon-Fri: 11:30 am to 8:00 pm (Ladies leave at 7:30)
Saturday: 11:30 am to 6:30 pm

This schedule includes a lunchtime of half an hour. Twice a month, employees have to come early, around 7/8 am, and sometimes leave around 10 pm due to shipments. Do we have to pay them overtime for this? If yes, what will be the calculation in Excel?

Overtime Calculation for Employees

On any day when employees work for more than 8 hours (excluding the lunch interval, say half an hour), you must pay overtime wages at double the rate. If employees start at 8 am, the scheduled working hours for that day are from 8 am to 4 pm. If an employee leaves at 6 pm, they should receive overtime wages for 2 hours. If, on a particular day, an employee arrives at 11:30 am and leaves at 10 pm, they should be entitled to 2 hours of overtime for that day.

In the salary sheet, there should be columns for the number of OT hours, OT rate per hour, and OT wages. The OT rate per hour should have a formula applied to reflect the hourly rate at double. The OT wages column should also have a formula to multiply the overtime hours by the OT rate. Once you input the number of hours, the OT wages will be calculated and should be included in the additions (Basic, DA, HRA, Conveyance, Special pay, etc.) to calculate the gross wages.

Single Overtime Calculation

If you have single OT (for those who have taken leave and not worked for 48 hours but have done overtime after taking leave for one or two days), you can split the column for overtime hours and rate with separate columns for "single rate" and "double rate."