Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Jun 20, 2019 10:06 AM
Hi folks,
I’m trying to create a formula that will return a date and time that is a pre-designated time the next day.
For example:
Jan 12, 2019 @ 2:19 PM
AND
JAN 12, 2019 @ 4:00 PM
Would both return a date of JAN 13th, 2019 @ 5:00 PM
I can add 24 hours with the DATE ADD formula, but is there a nested formula I can use to modify all times to a predetermined time?
The purpose of this formula is to create a due date field, that is a set time one day after all form submissions that populate my table.
Thanks!
Jun 20, 2019 11:34 AM
That’s easy enough to put together using the extremely useful DATETIME_FORMAT()
and DATETIME_PARSE()
twins. The secret is using the rich set of format specifiers both functions support.
For instance, to create a datetime value equal to 5 pm on the day following the day on which a record was populated by a form submission, you could use this formula:
DATETIME_PARSE(
DATETIME_FORMAT(
DATEADD(
CREATED_TIME(),
1,
'day'
),
'MM-DD-YYYY'
)&' 17',
'MM-DD-YYYY H'
)
That might not be what you want, though — for instance, it means a form submitted at 11:59 PM on January 1 would have a due (?) datetime of 5:00 PM on January 2. If you want to set a cut-off time for submissions, you’ll need to add an IF()
clause to tweak DATEADD()
into adding either one or two days, depending.