Compute only certain number of hours between two date/time

Ian_Muyrong · ‎May 22, 2021

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!

kuovonne · ‎May 22, 2021

Welcome to the Airtable community!

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.

Ian_Muyrong · ‎May 22, 2021

Hi @kuovonne thanks for replying!

To answer your question, it can be both. same date or consecutive date! Here is the scenario for reference:

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

kuovonne · ‎May 22, 2021

Can it be non-consecutive days? For example, time in is a Monday, and time out is a Wednesday?

Ian_Muyrong · ‎May 22, 2021

Hi @kuovonne! There won’t be such scenarios but would like to have airtable flag it if it does :slightly_smiling_face:

kuovonne · ‎May 22, 2021

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.

Ian_Muyrong · ‎May 22, 2021

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 :slightly_smiling_face: