12/27/2015 = 42365?

Happy third day of Christmas!  ‘Tis the season for celebrating with friends and family, a bit of introspection, and a good a cup of coffee.  Best wishes to you and yours!

And now, for something completely different.

What is 12/27/2015?  My calculator tells me 0.000220567962503446.  My calendar tells me there are four days left in the year.  Half of the world’s population wonders what the 27th month of the year is.  As for Excel, it tells me it’s equal to 42,365. Of course it is…

Day 1, according to Excel, is January 1, 1900.  Day 42,365 is December 27, 2015, by Excel’s reckoning, though that’s not quite right.  Apple fans have a different quibble.

Converting the date into a number is useful for sorting data and for making calculations based on dates, but it’s a bit frustrating when you expect to see 12/27/15 and get 42,365.  This can be easily remedied by tweaking the cell’s format.

“Ctrl” + “1” opens the “Format Cells” dialog box:

Excel recognizes 12/27/2015 as a date, and represents it as such.  Don’t like it?  Pick another format!  “OK” makes the update.

A different way to format dates on the fly is with the TEXT() function.  Here are a few examples:

By using TEXT(), we can change how the date is displayed without changing the format of the cell.  This also forces Excel to maintain your desired format even when it wants to change it for you.

TEXT() comes in handy for creating chart titles, but I’ll save that for another day.  As for now, I need to figure out what to do with these three French hens from my true love…