Solved
Time To / From Timestamp
+5Best answer by TimBeeston
I got it. Use the SET_TIMEZONE() function to set the formula to match your date field and you’ll not have to use the adjustment or deal with daylight savings.
The below will also return hours:minutes, as per your original post.
IF(
HOUR(SET_TIMEZONE(Start, 'America/New_York')) < 8,
"0:00",
CONCATENATE(
HOUR(SET_TIMEZONE(Start, 'America/New_York')) - 8,
":",
IF(
MINUTE(Start) < 10,
"0" & MINUTE(Start),
MINUTE(Start)
)
)
)
+3Hi
Try these:
8:00 am to Start:
IF(
HOUR(Start) + 8 < 8,
0,
(HOUR(Start) + 8 - 8) + (MINUTE(Start) / 60)
)End to 5:00 pm
IF(
HOUR(End) + 8 > 17,
0,
(17 - (HOUR(End) + 8)) - (MINUTE(End) / 60)
)
The +8 in the above formulas compensate for the timezone difference between UTC (which Airtable uses internally) and my local timezone. You will need to amend this based on the time difference from UTC where you are.
The issue with the above is that you will have to change the formula if you have daylight savings to contend with. I’m not sure how you would do that, but I bet someone here could show you how.
+3I got it. Use the SET_TIMEZONE() function to set the formula to match your date field and you’ll not have to use the adjustment or deal with daylight savings.
The below will also return hours:minutes, as per your original post.
IF(
HOUR(SET_TIMEZONE(Start, 'America/New_York')) < 8,
"0:00",
CONCATENATE(
HOUR(SET_TIMEZONE(Start, 'America/New_York')) - 8,
":",
IF(
MINUTE(Start) < 10,
"0" & MINUTE(Start),
MINUTE(Start)
)
)
)
+3Thanks mate and no worries. Learned something new myself in the process.
+5I just posted another topic for the ultimate goal this formula is leading to, if you’re interested in a real challenge… lol
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.