Hello! Trying to figure out a formula to compute for hours worked between two dates/time given a condition. The formula only needs to compute for hours worked between 10:00PM - 6:00AM. Here are the scenarios:

Scenario 1
Time in: May 4, 2021 4:00AM
Time out: May 4, 2021 1:00PM
Hours worked: 2 hours

Scenario 2
Time in: May 3, 2021 11:00PM
Time out: May 4, 2021 8:00AM
Hours worked: 7 hours

Trying to compute for “night differential” as part of our payroll. Hoping someone can help or lead me towards the right direction. Thank you!

This is an interesting question. There are a few different ways to solve it.

Will the two dates always be either the same date or consecutive dates? If so, the formula will be much simpler than if the two dates could be several days apart.

I’m sorry, but the more that I look at this, the more complicated it gets and I don’t have the time to create this formula for fun right now.

There are many different scenarios that the formula would have to take into account. Even if the time in and out are on the same days, the formula would need to take into account all of these scenarios:

both start and end times before 6am

both start and end times between 6am and 10pm

both start and end times after 10pm

start time before 6am, and end time before 10pm

start time before 6am, and end time after 10pm

start time between 6 and 10, and end time after 10pm

both begin and end times

Once you add in the possibility of start and end times being on different days, things get even more complex.

This doesn’t even take time zones into account. Airtable stores all date/times in GMT time, and formulas will need to account for this.

The end result will likely be a very long, very complex formula that is very hard to maintain.

I think that this value is better computed in a script.

Hi @kuovonne thanks for providing inputs on how I can approach this formula. I created a sample set of data based from your inputs. I think I got it but would be more than happy to get your thoughts