Text Formula Not working

hi guys,

Can someone tell me why this formula isn't working on my pc, yet it is working on a colleagues pc...

Bascially just want to use the TEXT function and keep getting a #VALUE! error

Cell A1 = 20/01/2018
Cell B1 = =TEXT(A1,"dd/mm/yyyy")

That is literally all i am trying to do. The formula worked fine two weeks ago, works fine on a colleague pc, but now i keep getting this error

Thanks!

@AlKey. A comma delimeted formula is the correct format for UK regional settings, in the event that the OP is using different settings then excel would give the 'there is a problem with this formula' error warning before getting as far as a #VALUE! error.

My initial thought was that the system regional settings might have been changed to US format, while the date is still being entered in UK format, but trying a few things would suggest that is not the case.

Trying various things, I can only reproduce the #VALUE! error by entering a numeric value that is either negative, or greater than 2958465 in A1 (date serial for 31/12/9999).
If I enter any valid or invalid date (31/12/10000, 31/20/2018, date entered in a format inconsistent with regional settings) or even a random text string, the text function replicates the entry, not the error.

Possibly updates to newer versions of excel don't produce the same errors, maybe somebody with 2007 could try the above to see if the results are the same?

@Hawkinsr86, does =A1+0 give a number or an error? Has the date been typed in or is it copy and paste from elsewhere?
If it is typed, did you try deleting and re-entering it?
Copy and paste data from other sources can sometimes contain zero width characters which excel will not recognise. The date in A1 should be 10 characters in the format you show, test with =LEN(A1) if the result is greater than 10 then this could be the problem.