Posts Tagged ‘Excel’

Adding Dates and Times in Excel

I answered a question on Quora today regarding how to add a column with dates to a column with times using Excel.

Simple Answer: just add the two cells.

Detailed Answer: dates and times in Excel are stored “behind the scenes” as numbers

  • Dates are stored as the number of days since 1/1/1900
    • 1/1/1900 is stored as 0
    • 1/2/1900 is stored as 1
    • 7/24/2012 is stored as 41,114
  • Times are stored as a fractional number (out of 24 hours)
    • 12:00 midnight (aka 0:00) is stored as 0.00
    • 12:00 noon is stored as 0.50
    • 11:59:59pm is stored as 0.999988…
    • 9:25am is stored as 0.39
      • fractional number of minutes into the day is (9 * 60 + 25) / (24 * 60) = 0.39236
  • Thus, adding together a date cell and a time cell gives you a number with a decimal, which Excel converts to a Date/Time.
    • 7/24/2012 9:25am = 41,114.39

That’s a pretty technical answer, so here’s a screenshot:

Cell D3 is calculated by simply entering the equation =B3+C3.  From there, you can right-click on that cell and go to ‘Format Cells…’ to make the resulting cell look exactly how you want.

Categories: Excel Tags: ,

Left Padding Zeros in Excel

14 February 2012 26 comments

Here’s a quick equation to use in Excel if you’re trying to pad a number out to the left.  For instance, you have “427” and you really want it to be “0000427”:






The TEXT function has a few more tricks up it’s sleeve.  Here’s a link to more details on it:

Categories: Excel Tags: , ,