#VALUE! Error when using DateValue function

[MarkVMcCullagh]

Hi there

The function in use is:
=DATEVALUE(D5)
and it returns #VALUE!

Cell D5 Contains:
3/19/2005

My suspicion is that this occurs because the file originated in the US where the date format is M/D/YYYY whereas here in the UK our date format is D/M/YYYY. The data I receive in the

I can create a workaround but frankly I'd rather it was fixed permanently. My workaround is rather cumbersome and I have lots of spreadsheets to update:
=DATEVALUE(MID(D5,FIND("/",D5)+1,FIND("/",D5,FIND("/",D5)+1)-FIND("/",D5)-1)&"/"&LEFT(D5,FIND("/",D5)-1)&"/"&RIGHT(D5,4))


The reason the above is so cumbersome is that the number of digits in the days and months vary.

Maybe someone can tell of an easier solution

I'm using Microsoft Excel 2000 (9.0.7616 SP-3) set up for British date formats.

Thanks

 

[MarkVMcCullagh]

It appears to be an international issue as I found a date of "7/7/2005" and tested the DATEVALUE function on it and it worked but for other dates which were obviously American format eg 9/13/2005 in the same file the #VALUE! continues to appear.