I have the date format at dd/mm/yyyy in numbers and have the format as English Australian. If I reverse the data entry eg 2020/02/01 the formula works
Screenshots are almost useless for troubleshooting problems of this nature, since the problem might be an invisible extraneous character (or errant blank space) that causes Excel not to recognize the data entry as a numeric date.
Moreover, the cell format does not matter, since it does not apply to text. In any case, the cell format only affects the appearance of a numeric value. It does not affect how data entry is interpreted.
The regional and language settings for your computer determine, in part, how Excel interprets data entry. Even though the language might be English Australian, you can override the default short date data-entry form (d/m/y).
It is also possible to set a language option just for Excel; in Excel 2010, I click File > Options > Language. I don't know if that affects the interpretation of data entry, overriding regional and language settings. But I also think that is not a likely cause of your problem.
The fact that 2020/2/1 works suggests that one language setting or another specifies that the short date data-entry form is y/m/d or y/d/m.
Given that, when you enter 1/2/2020, Excel tries to interpret 2020 as a day or month number. Since the year is obviously not valid as such, the data entry is interpreted as text.
Obviously, the long-term remedy is to track down the source of the unintended short date data-entry form and correct it.
But a short-term solution might be to convert text that Excel does not recognize as dates.
-----
A simple method that might work is to use Text To Columns to convert the data-entry form.
In Excel 2010, I select the cells (column range), then I click Data > Text To Columns. Work through the first two dialog boxes. In the third and final dialog box (with the Finish button), select the column to be converted, click on the radio button next to Date, select the appropriate form of the __data_entry__ text to be converted (DMY), then click Finish.
I believe TTC will choose a default cell format. So, you might have to select the cell format that you want again.
----
If TTC does not work for you, you will have use formulas to do the conversion.
In order to eliminate any confusion between what you see and what Excel expects (I speculate), I suggest that you use the DATE function. For D6:
Code:
=DATE(RIGHT(D6,4),
REPLACE(REPLACE(D6,1,FIND("/",D6),""), FIND("/", REPLACE(D6,1,FIND("/",D6), "")), LEN(D6), ""),
LEFT(D6,FIND("/",D6)-1))
Copy the cell with that formula, and paste-special-value into D6. Then clear the cell with the formula.
And again, you might have to select the D6 cell format that you want again
-----
If neither of those methods works, please upload an example Excel file that demonstrates the problem to a file-sharing website, and post the download URL in a response here. I like box.net/files. Please test the download URL to ensure that it works when you are not logged into the file-sharing website.
Some participants might object because they cannot or will not download external Excel files. But if neither of the methods above works, the devil might be in details that we can only see in the Excel file.