Is anyone great with Airtable formulas? I need hel...
# 2-ask-for-advicea
Allie Mullen
02/14/2023, 12:40 PMIs anyone great with Airtable formulas? I need help with an events table displaying dates & times.
• I have a DATE column ("Date & Time") also including TIME in a 12-hr format
• I have a DURATION column ("Duration")
• I set up an automation from Airtable to Slack when a new event is created, but the Date & Time displays weird (supposed to be "2/21/2023, 1:00pm" but comes out as "2023-02-21T180000.000Z")
• I want to create a new FORMULA column that pulls info from DATE and DURATION that displays info as "Feb 21, 1-2pm ET".
◦ I have been troubleshooting with ChatGPT and it's not working
• Maybe I could just update it to be a text-based column, but I have it connected to a Google Calendar and would love to keep it that way
d
Drew Claybrook
02/14/2023, 2:54 PMYour long date is an ISO 8601 date format and probably just needs to be reformatted when its ingested. That DATETIME field is probably stored as an Epoch timestamp, which may or may not matter.
If you want to use a formula field in Airtable, try this one:
CONCATENATE(DATETIME_FORMAT(Date,'MMM D'),", ",DATETIME_FORMAT(Date,'h'),"-",DATETIME_FORMAT(DATEADD(Date,Duration, 'seconds'),'h:mm A'))Drew Claybrook
02/14/2023, 2:54 PMimage.png
Drew Claybrook
02/14/2023, 2:56 PMI had to set the date field to use the same timezone for all collaborators. You can set timezone in the formula field if you want.
a
Allie Mullen
02/14/2023, 3:38 PM@Drew Claybrook this is the closest I've gotten!! Duration is correct but it's not registering the time correctly.
d
Drew Claybrook
02/14/2023, 3:40 PMDid you adjust the Date column setting to “Use the same time zone for all collaborators?”
👍 1
Drew Claybrook
02/14/2023, 3:55 PMIf you want everything in EST/EDT, you can force it:
CONCATENATE(DATETIME_FORMAT(SET_TIMEZONE(Date,'America/New_York'),'MMM D'),", ",DATETIME_FORMAT(SET_TIMEZONE(Date,'America/New_York'),'h'),"-",DATETIME_FORMAT(DATEADD(SET_TIMEZONE(Date,'America/New_York'),Duration, 'seconds'),'h:mm A'))Drew Claybrook
02/14/2023, 4:12 PMThere may be a way to inherit the timezone, but I didn’t see it. Never used airtable before now 😬😆. I just assumed it was a lot like Excel.
a
Allie Mullen
02/14/2023, 4:13 PMYOU DID IT!!!!
Allie Mullen
02/14/2023, 4:13 PMthank you thank you thank you
d
Drew Claybrook
02/14/2023, 4:15 PMAwesome! You’re welcome!
Now lets see if your automation brings it over properly. That formula column should just be text at this point, which I imagine will work as expected.
a
Allie Mullen
02/14/2023, 4:16 PMIt didn't at first, but I just re-tested and it works
d
Drew Claybrook
02/14/2023, 4:16 PM🤘
Drew Claybrook
02/14/2023, 4:16 PMDrewGPT over here haha
🤪 1
😂 2
‼️ 1
a
Alex Gordon-Furse (Community Hacked)
02/14/2023, 5:10 PMhahah love this exchange so much. Thanks @Drew Claybrook for coming fresh in here and helping out cc @Sharon Huey 😄
😊 1
🙌🏼 1
s
Sharon Huey
02/14/2023, 5:21 PMI'm a Drew-fan!! 😁
😎 1
a
Allie Mullen
02/14/2023, 5:54 PMSOOOO helpful!! I just cannot get around the airtable formula situation. Just so confusing for me.
d
Drew Claybrook
02/14/2023, 5:59 PMIs there maybe something I can help explain? Or another question to ask?
a
Allie Mullen
02/14/2023, 6:01 PMNot right now, but I will certainly be asking you in the future if anything else comes up!
☝️ 2
✅ 2