Date format doesn't work with formula

[RodneyC]

I've inherited a number of spreadsheets and am trying to add new data and am running into a formatting issue

This formula is In column I =DATEVALUE(C736) dragged up/down the entire column with the row reference changing as needed. in C736 there is this value 2021-09-01. Currently C736 is the last row of data.

New data is being added to rows 737 - 800

1. In C737 I have entered 2021-10-01, which publishes 10/1/2021. which results in column I producing #VALUE!
2. In C737 I have custom formatted the cell to yyyy-mm-dd which does not resolve the issue in column I
3. I've looked at the formatting in cell C736 and it shows General as selected. If I move to C737 and format to General the sample value that displays is 44470 which is what displays in C737 if I select General

I'm baffled. What am I missing?

Thanks in advance

 

[jasonb75]

Just try =C736 without the DATEVALUE function.

DATEVALUE is only needed to convert a text string that looks like a date to a proper date. If the cell already contains a proper date then it will not work (as you have already discovered).

 

[RodneyC]

That will very likely work, thank you.