Created Date extracted form Created column is not the same

Amr

Created date is extracted using formula DATEONLY() from Created column.The date in Created column is 3/8/2024 and time is 12:01 AM but how come the extracted date is 2/8/2024 and not 4/8/2024

date.png

KPH

This is due to the way the dates are stored in UTC but displayed in local time. Unless you are in UTC time zone, the DATEONLY extraction from a system generated field will not work for the periods around midnight. For PST, for example, DATEONLY shows the next day for any timestamps between 4pm and midnight local time (as PST is 8 hours behind UTC). It looks like you are probably behind UTC so the date is still showing the previous day.

A workaround is to extract the date manually rather than using DATEONLY.

This formula will give you just the date part (the left 8 characters), but it will be a text string:

=LEFT([Created Date]@row, 8)

If you need the date to be a date format, it is a little more tricky and will depend on your original date format

The function DATE lets you specify the year, month, and day

=DATE(year, month day)

You need to extract the values for year, month, and day from your created column, use a VALUE function to convert that into numbers, and then put that into the correct place in the DATE function.

Have a try with this:

=DATE(VALUE(MID(Created@row, FIND("/", Created@row, FIND("/", Created@row) + 1) + 1, 2)), VALUE(MID(Created@row, FIND("/", Created@row) + 1, 2)), VALUE(LEFT(Created@row, 2)))

I use a different date format so cannot test it. If it needs to be adapted, or you have any questions, just ask.