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

[Aaron_Hoegenaue]

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!

[W_Vann_Hall]

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.