A conditional formula for weekdays


#1

Hi.
I started a personal logging/journaling.
Every night, I log a few details regarding the day.
However, sometimes I log it after 12 AM (00:00).
I want to analyze my data, and need a formula that will allow me to do the following:
If the time is earlier than 12 AM, it should show the weekday (a simple weekday formula).
But if it’s after 12 AM, It should show the previous weekday (because this is the one it is actually related to).
Thanks a lot,
Daniel


#2

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()
    )
  1. 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…


#3

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