The fact is that what may Look Like a Date, may not “Play Nice in the Sandbox” with other dates that you have in your workbook.
Example: Let’s say, for instance, that you have inherited an Excel workbook created by some Genius who no longer works in your company (I’m sure that his high IQ has led him to bigger and better things – cough, cough…). Now you (a true Genius) wants to perform some Analysis that save the company countless hours and expense. The problem that this now-absent bright fellow has not documented his work, and unless you can Rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you Cannot Rely on the efficacy of your results.
So what is a well-meaning Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your upset stomach, relieve that itch on your back that you can’t quite reach, cure that nagging doubt that you are being watched (you are, you know…), and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, maybe I’m being a Snake Oil Salesman with some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert anything that looks-like-a-date into the standard Excel serial number, and you can then format it as you wish. Formatting is, of course, rudimentary…
How Totally Cool is that! Mismatched dates can cause you a world of grief, but this simple DATEVALUE trick can save the day
One Note of Caution to Apple Users: For you MacBook Users out there, (I use Excel on one occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (don’t ask me why…).