There are several possible fixes, depending on which works best for you.
-
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).
-
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.
-
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()
)
- 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…
