Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Differentiating between day and night hours using a formula

Topic Labels: Formulas
443 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there, I’m trying to figure out a way to take 2 date inputs (shift start and shift end) to output a duration and I’ve done this using “DATETIME_DIFF({Shift End}, {Shift Start})”. However the system we use for our company relies on daytime hours and night hours being paid differently, so ideally we want the end duration to be split between day and night where the crossover is 7AM/7PM. For example if someone started their shift at 0500 and finished at 2300 they’d work 12 day hours and 6 night hours. Would this be possible to configure within Airtable’s capabilities? Thank you in advance.

4 Replies 4

Hey @Patrick_Musto!

I’m trying to solve this right now.
Quick question: What timezone are you in?

Additionally, are the times that are submitted in reference to multiple timezones? Or just one?

I’m building out the bones of a rather substantial formula and was curious if I needed to think about a possible solution for timezones.


Edit: Adding on another question here:
Do you need granular times down to the minute?

e.g. 1.4 hours versus 1:24.

Hi Ben,
Thanks for getting back to me. We only operate in one timezone and are based in the UK.
Regards
Patrick

Hi Patrick, I’m in the UK too but it doesn’t actually matter where you are as long as the time you have in your date/time field is correct. I have a similar formula for a base that logs calls that can come in 24/7. We define “night” at the hours between 8pm and 8am, and “day” as 8am-8pm.

Airtable has an HOUR() function that will give you a number from 0-24 from a Date/time field. 0 = midnight, 23 = 11pm. So my formula is then:

IF(HOUR({Date and time of call})=>20, "Night", IF(HOUR({Date and time of call})<=7, "Night", "Day"))

This just means that if the hour given in {Date and time of call} is more than or equal to 20 or less than or equal to 7, then display “Day”, otherwise “Night”. You should able to adapt this for your use.

Hi JonathanB,
That is very helpful, thank you. We will certainly use that.
KR
P.