Trying to sum differential hours

[BobB44]

Hi.

We pay our employees a differential for hours worked during an overnight shift as well as on the weekend. So anytime worked overnight between 11 PM and 7 AM and one differential in any hours worked between midnight on Friday night (Saturday morning) through midnight on Sunday night earn an additional differential.

So, if somebody clocks in at 8 o’clock at night and works until midnight they would earn one hour of differential pay. I.e. 11 PM to midnight. I need two formulas that will allow me to calculate these two different differential based on the clock in day snd time and the clock out day and time.

I am completely stumped how to even begin this. Any thoughts?

 

[ranman256]

its best to use DATE/TIME to do date math, and you dont have to worry about midnight.
ex:
vTime1 = "1/1/2022 11:45 pm"
vTime2 = "1/2/2022 6:45 am"

= dateDiff("h",vTime1, vTime2)

or using cells
=(A2-A1)*24

 

[BobB44]

its best to use DATE/TIME to do date math, and you dont have to worry about midnight.
ex:
vTime1 = "1/1/2022 11:45 pm"
vTime2 = "1/2/2022 6:45 am"

= dateDiff("h",vTime1, vTime2)

or using cells
=(A2-A1)*24

Thanks. I am still having trouble.

How do I identify the hours that someone worked on the weekend separately?

E.g. If someone starts at 11:00 PM Friday night and gets off at 1:00 AM Saturday morning, I need my formula to calculate that they only worked 1 hour of the weekend time (i.e. on a Saturday or Sunday.

 

[BobB44]

Thanks. I am still having trouble.

How do I identify the hours that someone worked on the weekend separately?

E.g. If someone starts at 11:00 PM Friday night and gets off at 1:00 AM Saturday morning, I need my formula to calculate that they only worked 1 hour of the weekend time (i.e. on a Saturday or Sunday.

Thanks. I am still having trouble.

How do I identify the hours that someone worked on the weekend separately?

E.g. If someone starts at 11:00 PM Friday night and gets off at 1:00 AM Saturday morning, I need my formula to calculate that they only worked 1 hour of the weekend time (i.e. on a Saturday or Sunday

Part of my problem is that our timesheet software only gives me a clock-in date and time and then the clock-out time. It doesn't give me the clock-out date. I have been able to work out getting the overnight hours between 11:00 PM and 7:00 AM every day but stumped how to identify hours worked only one a Saturday or Sunday.

 

[BobB44]