A conditional formula for weekdays

There are several possible fixes, depending on which works best for you.

1. I assume you’re using CREATED_TIME() to generate your timestamp. Don’t. Instead define date field that you have to manually set (click in field; calendar pops up; select ‘Today’ — unless, of course, the post is supposed to be logged against the previous day, in which case you would select yesterday’s date).

2. Continue to use CREATED_TIME(), but reference it through two new fields: The first is a date field named something like {Date Override}; on those occasions when you are posting after midnight, manually set a more representative date and time in this field. The second is a formula field with the formula

   IF({Date Override},{Date Override},CREATED_TIME())

   That way, if an override date and time exists, it’s assumed to be the legitimate timestamp for the entry; otherwise, the record creation date and time are used.

3. If there is a known maximum lateness value for your entries — that is, you’ll never post an entry for a day later than, say, 3:00 a.m. the following day — you can do something like the following formula. Should the entry have been created between midnight and 3:00 a.m., it simply shifts the logged time 3 hours earlier (and, thus, into the previous day); otherwise, it uses the record CREATED_TIME().

IF(
    HOUR(
        CREATED_TIME()
        )<3,
    DATEADD(
        CREATED_TIME(),
        -3,
        'h'
        ),
    CREATED_TIME()
    )

4. If shifting the timestamp back 3 hours risks sequencing the entry before ones that actually preceded it, you can simply force any after-midnight entries to be timestamped 11:59 p.m. with this formula (again using an arbitrary 3:00 a.m. cutoff point):

IF(
    HOUR(
        CREATED_TIME()
        )<3,
    DATETIME_PARSE(
        DATETIME_FORMAT(
            DATEADD(
                CREATED_TIME(),
                -1,
                'd'
                ),
            'MM/DD/YYYY'
            )&' 23:59',
        'MM/DD/YYYY HH:mm'
        ),
    CREATED_TIME()
    )

Choose whichever works best for you…

Thanks a lot @W_Vann_Hall for the very detailed response.
The last formula solved it :slightly_smiling_face: