Cross-sheet referenced Date keeps defaulting to non-Date format

cantpickname

I have a column that uses 3 INDEX MATCH(es) to pull in a single date (there is no way this will ever pull in more than one date) using IF statements and "" if it is not found on a sheet.

Formula works as planned, and I now have a System Column of "Created On" in my data (first pic). I can then change it to display as a date format (second pic). But once I make an update in the data somewhere it reverts back to the original format as in the first pic.

My formula works, the display as date works but only momentarily. I've tried to change the field as "Restrict to dates only" but that doesn't work. Any insights are greatly appreciated!

ABsu6dwuTH.png

0ErD2cNSPz.png

Genevieve P.

Hi @cantpickname

It looks like you may be pulling a date from a System Date column, is that correct? (Ex. "Created" or "Modified").

In this instance, we can add a DATEONLY function around your INDEX, like so:

=DATEONLY(INDEX({Value to return}, MATCH([Value to match]@row, {Column with value to match}, 0)))

Let me know if this made a difference!

Cheers,

Genevieve