Skip to main content

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!

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.

Reply


Terms & Conditions