Best Of

You can use

=DATE(YEAR(TODAY()), MONTH(ColumnA@row ), DAY(ColumnA@row ))

in column B (your target column) and then make it a column formula.

Hi @Lyndie

When you are dealing with a system column like Created Date, you need to be aware that the display value and internal or back-end value are not the same.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Time zones and system columns

System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

In the demo sheet below, since my time zone is UTC+9 in Tokyo, any Created date before 9:00 AM shows yesterday when I apply the ISDATE function, whereas it displays today.

https://app.smartsheet.com/b/publish?EQBCT=3320fb06b55146babf8b36cb24b9ec5c

To calculate Created Date as you see it in the system column, you can use the combination of the DATE function and text functions, like the MID or LEFT, and the VALUE function.

=DATE(
  VALUE("20" + MID([Created date]@row, 7, 2)), 
  VALUE(LEFT([Created date]@row, 2)), 
  VALUE(MID([Created date]@row, 4, 2))
)

Once you get the desired date, you can reference it in your cross-sheet reference formula.