Skip to main content

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!

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.


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.


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


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



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



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


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


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.


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 🙂



Reply