Re: Help with DATEADD() Formula for Due Date with time modification

334 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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
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.


1 Reply 1

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:

        )&' 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.