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)