Date format issue Excel 2010

Hello,

I have an issue with an inherited spread-sheet. I need to calculate months remaining between 2 dates but some of the dates aren't correctly formatted.

The values are, for example, 1/1/11. I have changed the format to date and tried to use the =DATEVALUE() formula but it just returns a VALUE error.

Any ideas?

Thanks in advance for any advice.

DATEVALUE converts a text string to the excel serial number for the corresponding date.

Off-hand DATEVALUE("Date_text") throws the #VALUE! error if it's not getting an input between Jan 1, 1900 and Dec 31, 9999.

It also throws the #VALUE! if you neglect to add the double quotes to a typed-in string (doesn't apply if it's pointing to a cell like DATEVALUE(A1) though).

Of course...

this assumes that you've got a text string representing the date and you want a number. If you're feeding it a number (like something that's already a date) that will also throw the #VALUE! error because you're giving a number to a text function.

If it's already the number and you want a different format, right click => format cells => date
Then take your pick of the available formats (make sure it's the right language, eg "English (US)" vs "English (UK)" though)

Thanks for the advice I have sorted it now. Converted the text to dates(which were all in US format) by removing the / and then using a =DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2)) formula.