Most Excel users have at one time entered the dates and times in their spreadsheets, but relatively few users understand how Excel stores times and dates. This can often lead to confusion and prevents users benefit from a wide range of operations and formulas useful for dates and times of Excel.

Dates and times recorded as numbers

Excel really save the date and time are mere numbers, which are numbered in each day, and every time coded fraction per day. If you think about it, this makes perfect sense, because it means you can logically add and subtract dates and times.

For example, runs on the Windows system:

January 1, 2010 represents a total of 40,179

January 2, 2010 is represented by integer 40,180

and so on.

The time is represented by a fraction of a day. Thus:

00:00 hours is represented by the decimal 0.0

06:00 am is represented by the decimal 0.25

12:00 am is the decimal 0.5

18:00 hour represents the decimal 0.75

and similarly, all times between those given above are represented by the fraction of day they occur.

Logically, therefore, a date and time in Excel is represented by a number that consists of an integer (the date) and a decimal number (representing time). For example:

18.00 on 1 January 2010 is represented by the number 40179.75

24:00 January 2, 2010 is represented by the number 40180.5

Now consider the addition and subtraction of dates and times. If I want to calculate the date is 56 days after January 1, 2010, I have to add the integer 56 to January 1, 2010. Internally, Excel adds the integers 56-41079 (integer representation of 1 January 2010) and get the value 40235, which corresponds to the date of February 26, 2010th

Similarly, if I have the date and time 12:00 hours, January 2, 2010 and I want to take 18 hours from now, I just drag the Excel from 18:00 to 12:00 the date and time, January 2, 2010. Internally, Excel subtracted 0.75 (representing the time 18:00) from 40180.5 (representing the noon hour on January 2, 2010) and results 40,179.75 – ie the date and time of 18: midnight January 1, 2010

Formatting Excel dates and times

It is also important to understand the formatting of dates and times in Excel. As dates and times are only numbers in Excel, the number formatting is the only one that allows us to interpret it as a date or time.

Regardless of solitary confinement, if you type “1-Jan-2010 12:00 in an Excel cell, the cell actually contains the numerical value 40179.5. However, the format of the cell defines how the value is shown. Therefore:

If the cell is formatted Excel-like “General” will display 40179.5

If the cell is formatted as a date, it displays 01/01/2010 (or a corresponding date format selected)

If the cell is formatted as a date and time, it displays 01/01/2010 00:00 (or similar selected date and time format)

If the cell is formatted as currency, it will show $ 40,179.50 (or equivalent format in such currency)

This often leads people to believe that the Excel format for the return date or time has not worked properly. Often, however, the problem can be solved only by changing the cell formatting.

To change a cell format, simply right-click the cell and choose Format Cells … option. The formatting requirements may be selected from the list in the Number tab in the menu.