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. |