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