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.

Left Padding Zeros in Excel

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”:

=TEXT(A1,"0000000")

 

 

 

The TEXT function has a few more tricks up it’s sleeve.  Here’s a link to more details on it:  http://www.techonthenet.com/excel/formulas/text.php