All Dates are Not Created Equal

As anyone who has used our favorite spreadsheet application for a significant amount of time knows, Dates can be occasionally tricky in Excel. As we discussed in 2011, what may Look Like a Date, may not “Play Nice” with other dates that you have in your worksheet.

To paraphrase Gertrude Stein, A Date is a Date is a Date is simply Not true. A good example is a date with an apostrophe at the beginning will look like an ordinary date when entered on a spreadsheet. But it is not…

Let’s say that you are importing records from another database application and you want to perform some Brilliant Analysis (since you are doing this, it must be “brilliant”…) that save the organization countless hours and expense. The trouble is that the dates are saved and exported as Simple Text in the database, and Excel is not giving you the results you deserve. Unless you can rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you obviously Cannot Rely on the value of your results!

So what is a Brilliant Analyst to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your jangled nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, bring fast relief for your hangover, and make any Dates in your worksheet work in unison with all of the dates therein. (Your results may vary on some of the attributes listed, but it will make your dates get along with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Totally Rad is that! It may not sound like a big deal, but it can save you a world of grief in many circumstances.

The DATEVALUE function: It’s not going to bring World Peace, but it is Good Stuff to know…