Differentiating between day and night hours using a formula

[Patrick_Musto]

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.

[Ben_Young1]

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.

[Patrick_Musto]

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

[JonathanB]

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.

[Patrick_Musto]

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