What excel formula can be applied to separate weekdays and weekends when given date and time?
This is in effort to solve weeknight and weekend pay differentials, which are an additional $1.50 an hour. It’s more extensive to compute due to the large number of employees. The issue is that I’ve been provided daytime hours, night time hours, and weekend hours worked without a distinction of whether the day and night hours worked are weekdays or weeknights or weekends. Very complicated, but if I can get the weekdays separated from weekends, that will give me enough info to work out everything else.
An example of the data:
John Doe worked Friday, 6/28/2024, from 11:00pm-6:00am (cuts into Saturday). The information given is
“daytime hours” = 1hr
“nightime hours” = 6hrs, but then “weekend hours” also equal 6hrs.
While relatively true, this isnt helpful when the hours are the same on Saturday 6/29, 11p-6a shift, in which hours in each time frame are the applied in the same manner. The day ltime hour on 6/28 does not recognize weekday or weekend day. Therefore, I would need a formula to help separate these hours in weekday and weekend slots. Sounds as complicated as explained here, but if you’re up for the challenge, help would be greatly appreciated.
Unable to solve
Fin Lin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.