How Excel Calculates Date and Time

How Excel Calculates Date and Time

Excel handles dates and times numerically. The numbers for dates range from 1 to 2958465. The number1indicatesthe date January 1, 1900, and the number 2958465 indicates the date December 31, 9999.

Type any number in a cell. Press Ctrl + Enter to enter the number. Then press Ctrl+Shift+3 to see the date for the number you entered.

Because of this numeric method, you can treat dates as values and perform calculations such as subtraction or totalling dates. The results of subtracting an earlier date from a later date are displayed as a number, which (as it happens) is also the number of days between the two dates.

Time, on the other hand, ranges from 0 to 1 and indicates precise time intervals of seconds:

Midnight = 0, noon = 0.5, and 14:09:03 = 0.589618.

Entering the Date in a Cell

Excel recognizes a number as a date by the date format, which uses a slash (/) as a separator.

An example of the date format in Excel:  7/25/2001.

Some users prefer to use a period (.) as a separator, instead of a slash    (/).

If you are one of those people and would like to change the default setting for the date format, perform the following steps: From Windows, choose Start, Control Panel, Regional Options.

Select the Date tab. In the Date separator box, change the slash (/) to a period (.). Click Apply and OK.

Easily entering the date in a cell

Use the slash separator (/) on the right side of the keyboard above the number 8 to enter the date. Using this key ensures the correct date format.

Shortcuts for entering current date and time

  • To enter the current date in a cell, press Ctrl+;.
  • To enter the current time in a cell, press Ctrl + Shift+;.

Date format table

m Month.Thenumberofthemonth,without0ifthenumber is lower than10.
mm Month.  The number of the month, including 0 if the number is lower than10
mmm Month. The first three letters of the name of the  month are displayed
mmmm Month. The full name of the month is displayed
d Day.Thenumberofthedayofthemonth,withouta0if the number is lower than10.
dd Day. The number of  the  day of  the  month, including  0if the number is lower than10
ddd Day, as three-character text. For example, Thursday is displayed as Thu.
dddd Day, as complete text.            For example, Thursday is displayed as Thursday
yyory Year. Two digits. For example, 1997 is displayed as 97.
yyyor yyyy Year.  The full number of the year is displayed. For example, 1997 is displayed as 1997.