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:

Format cells.png

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:

Several versions of the 3rd day of Christmas.  Formulas (Col. A) with output (Col. B)

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…

Control + Arrow

An easy way to appear to have ninja skills in Excel is to skip around a spreadsheet without a mouse.  With this easy trick, you can channel your inner Kal-El and leap 16,833 columns or 1,048,575 rows in a single bound.

  • Open a new spreadsheet
  • Hold down the “Ctrl” button (and don’t let go!)
  • Hit the right arrow key “→”
  • Hit the down arrow key “↓”
  • Hit the right arrow key “←”
  • Hit the right arrow key “↑”

Here’s an animation of what it should look like:

Run circles around your spreadsheet in under a second

Once through the procedure, you should be back to cell A1 (as a bonus, you also have 30 lives). Note that you can release the “Ctrl” button between moves, but it must be held down before pressing the next arrow key to make the next jump.

I’ll explore this trick further in spreadsheets with data as this comes in handy when working with blocks of information.

Hello World

“A journey of a thousand miles begins with a single step.” – Lao Tzu

Here’s to first steps and stumbling our way into what may be.