Many, if not most, worksheets use dates and times in one manner or another and most users have worked with dates and times in Excel. However, not all users are able to utilize their spreadsheets formulas and functions to the fullest because they do not understand how dates and times are stored in their worksheets.

Dates and times are actually stored as simple numbers within Excel. It is the formatting of the spreadsheet’s cells that causes a number to be displayed as a date, time, or date & time.

Within Excel, dates and times are coded as follows:

Dates Stored as positive integers. For example:
  1 = Sunday, 01-Jan-1900 
  2 = Monday, 02-Jan-1900
  3 = Friady, 20-Feb-2015

Times Stored as decimals, between 0 and 1, which represent a portion of the day. For example:   
  0 = 00:00 (midnight)
  0.25 = 06:00 (6 a.m.)
  0.5 = 12:00 (noon)
  0.75 = 18:00 (6 p.m.)

Dates & Times Stored as decimals,comprised of

  • an integer representing the date, and
  • a decimal between 0 and 1 representing the time

For example:

  1.25 = 06:00 (6 a.m.) on 01-Jan-1900
  42036.5 = 12:00 (noon) on 01-Feb-2015
  42036.75 = 18:00 (6 p.m.) on 01-Feb-2015
  42037.75 = 18:00 (6 p.m.) on 02-Feb-2015