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.
+9Hey @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.
Ben_Young1 wrote:
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
Patrick_Musto wrote:
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.
JonathanB wrote:
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.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.