excel tutorialDate & Time Functions
Dates and times are stored as numbers in Excel and count the number of days since January 1st, 1900. What you see depends on the number format.
For example, cell B1 below contains the number 34425.12. Cell C1, D1 and E1 contain the exact same number but have a different number format. Apparently, 34425.12 days after January 1st, 1900 (with the decimal part representing the time) is the same as Friday, April 1, 1994 at 2:52:48 AM.
Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.
1. To apply a different number format, right click a cell and then click Format Cells.
The 'Format Cells' dialog box appears.
Note: we used the General format for cell B1 and B2. General format cells have no specific number format. We used different Date formats for cell C1, C2 and D1, D2. We used the Time format for cell E1, E2. Remember, all cells contain the exact same number. We only changed the appearance!
Date and Time Functions
Below you can find the results of some date and time functions.
To get these results, we have used the following date and time functions.
Note: to show the formulas instead of their results, press CTRL + (`). You can find this key above the tab key.
Explanation:
| Cell C4 | to get the year of a date, use the Year function. |
| Cell C5 | to get the month of a date, use the Month function. |
| Cell C6 | to get the day of a date, use the Day function. |
| Cell C7 | to get the complete years between two dates, use the DateDif function ("y"). See note. |
| Cell C8 | to get the complete months between two dates, use the DateDif function ("m"). |
| Cell C9 | to get the complete days between two dates, use the DateDif function ("d"). |
| Cell C10 | to get the complete months between two dates (ignoring years), use the DateDif function ("ym"). |
| Cell C11 | to get the complete days between two dates (ignoring years), use the DateDif function ("yd"). |
| Cell C12 | to get the complete days between two dates (ignoring months and years), use the DateDif function ("md"). |
| Cell C13 | to get the weekday of a date, use the Weekday function. The result is a number, ranging from 1 (Sunday) to 7 (Saturday). Apparently, 4/1/1994 falls on a Friday. |
| Cell C14 | to get the hour of a date, use the Hour function. In a similar way, you can use Minute and Second to get the minute and second of a date. |
| Cell C15 | to get the date 4 days later than date 1, simply add the number 4 to cell C1. |
| Cell C16 | the Date function returns the number that represents the date. What you see depends on the number format. The Date function accepts three arguments, Year, Month and Day. |
| Cell C17 | the Today function returns the number of the current date. What you see depends on the number format. |
| Cell C18 | the Now function returns the number of the current date and time. What you see depends on the number format. |
Note: What's complete? If you look at date 1 and date 2, you might think that the difference in years is 11. However, the DateDif function returns the complete years between the two dates, which is 10 years (almost 11 years!).
More Date and Time Functions
There are many more date and time functions in Excel. Want to see more?
1. Click on Formulas, Date & Time.
2. For example, click on the NETWORKDAYS function. This function returns the number of whole workdays between two dates (Working days exclude weekends and any dates identified in holidays).
3. The 'Function Arguments' dialog box appears.
For more help, click on 'Help on this function'.
Did you like this free Excel tutorial? Show your appreciation, vote for us.