Weird excel issue

[MiniFav]

I have come to this weird issue where excel isn't using the target cells for my formula until i double click into each cell that that kind of refreshes it into working.

in the example below you can see rows 9,10 and 11 have nothing in column K, however if i were to double click into one of these values in columnn J then column K would start working.

test.xlsx
	J	K
1	Last Done	Date
2	03/09/2020	03/09/2020
3	53847:39FH
4	11/10/2019	11/10/2019
5	30/03/2022	30/03/2022
6	16/10/2021	16/10/2021
7	16/10/2021	16/10/2021
8	16/10/2021	16/10/2021
9	29/03/2022
10	16/10/2021
11	11/10/2019
Sheet1

Cell Formulas
Range	Formula
K2:K11	K2	=IF(ISBLANK(J2),"",IF(ISNUMBER(J2),J2,""))

I am looking for either a solution to stop this (I have automatic formulas on) or maybe a VBA macro to cycle through roughly 3000 cells within column J to refresh them all for me.

 

[Joe4]

That tells me that the values you have in column J are actually entered as Text, and not as valid Numbers/Dates.
When you click in one and press enter, you are re-entering it as a number, and then your formula in column K (which uses the ISNUMBER function) will work.

So you need to convert all the values in column J to valid Date/Number entries (Dates really are just Numbers with a special Date format).
You can convert column J all at one by selecting it, going to "Text to Columns" from the Data menu, hit next until you get to Step 3, then select the Date option, choosing the DMY format, and clicking Finish.

It shouldn't affect the non-date value you show in cell J3.

Also note, if after running the "Text to Columns" step, your dates start showing up as big integers, simply change the format of column J to your desired date format.
Those actually are valid dates! As I said, Dates are really just Numbers in Excel, specifically the number of days since 1/0/1900, with some date format applied.

 

[MiniFav]

You are incredible! thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!