Excel Date Functions
Excel stores dates as serial numbers behind the scenes, and its date functions let you retrieve today's date, pull out a year or month, and calculate the gap between two dates.
Getting the Current Date and Time
TODAY() returns the current date, and NOW() returns the current date and time. Neither function takes any arguments, and both are volatile, meaning they automatically refresh whenever the worksheet recalculates, such as when you open the file again the next day.
Example
=TODAY()Breaking a Date into Year, Month, and Day
Because a date is really just a serial number formatted to look like a date, functions can extract individual parts of it. YEAR, MONTH, and DAY each take a single date and return the corresponding numeric component.
Example
=YEAR(A2)Calculating the Gap Between Two Dates with DATEDIF
DATEDIF(start_date, end_date, unit) calculates the difference between two dates in whichever unit you choose: complete years, complete months, or complete days. It is a hidden function, meaning Excel does not show it in the formula autocomplete list, but it still works correctly when typed manually.
Example
=DATEDIF(A2,B2,"Y")- "Y" returns the number of complete years between the two dates.
- "M" returns the number of complete months.
- "D" returns the number of days.
- "YM" returns the remaining months after subtracting whole years.
- "MD" returns the remaining days after subtracting whole months.
Exercise: Excel Functions
What does the function =SUM(A1:A10) calculate?
