TODAY Function in Microsoft Excel
Part 1: Introduction
Definition
The TODAY function in Microsoft Excel is a date/time function that returns the serial number of the current date.
Purpose
The purpose of the TODAY function is to provide the current date, regardless of when you open the workbook. It is also helpful for calculating intervals.
Syntax & Arguments
The syntax for the TODAY function is as follows:
TODAY() The TODAY function syntax has no arguments.
Return Value
The TODAY function returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations.
Remarks
Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,447 days after January 1, 1900. If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates.
Part 2: Examples
Let’s look at some examples of using the TODAY function in business.
Example 1
Purpose: To display the current date in a cell.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Date |
| 2 | =TODAY() |
Explanation: In this example, we want to display the current date in a cell. The formula =TODAY() returns the current date.
Example 2
Purpose: To calculate the number of days until a future date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Future Date | Days Until |
| 2 | 2023-12-31 | =A2-TODAY() |
Explanation: In this example, we have a future date and want to calculate the number of days until this date. The formula =A2-TODAY() returns the number of days until the future date.
Example 3
Purpose: To calculate the number of days since a past date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Past Date | Days Since |
| 2 | 2023-01-01 | =TODAY()-A2 |
Explanation: In this example, we have a past date and want to calculate the number of days since this date. The formula =TODAY()-A2 returns the number of days since the past date.
Example 4
Purpose: To calculate a person’s age or an item based on the birth date or manufacture date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Birth Date | Age |
| 2 | 1990-01-01 | =YEAR(TODAY())-YEAR(A2) |
Explanation: In this example, we have a birth date and want to calculate a person’s age based on this date. The formula =YEAR(TODAY())-YEAR(A2) returns the age of the person.
Example 5
Purpose: To calculate the number of days left in the current year.
Data tables and formulas:
| A | |
|---|---|
| 1 | Days Left in the Year |
| 2 | =DATE(YEAR(TODAY()), 12, 31)-TODAY() |
Explanation: In this example, we want to calculate the number of days left in the current year. The formula =DATE(YEAR(TODAY()), 12, 31)-TODAY() returns the number of days left in the current year.
Example 6: Using TODAY with IF Function
Purpose: To classify whether a project deadline is overdue, due today, or future owing.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Project | Deadline | Status |
| 2 | Project A | 2023-07-15 | =IF(B2<TODAY(), “Overdue”, IF(B2=TODAY(), “Due Today”, “Future Due”)) |
| 3 | Project B | 2023-07-29 | =IF(B3<TODAY(), “Overdue”, IF(B3=TODAY(), “Due Today”, “Future Due”)) |
| 4 | Project C | 2023-08-10 | =IF(B4<TODAY(), “Overdue”, IF(B4=TODAY(), “Due Today”, “Future Due”)) |
Explanation: In this example, we have project deadlines, and we want to classify whether the deadline is overdue, due today, or future owing. The nested IF function with the TODAY function helps to organize the deadlines.
Example 7: Using TODAY with DATEDIF Function
Purpose: To calculate the years, months, and days between a past date and today.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Past Date | Years | Months | Days |
| 2 | 1990-01-01 | =DATEDIF(A2, TODAY(), “y”) | =DATEDIF(A2, TODAY(), “m”) | =DATEDIF(A2, TODAY(), “d”) |
Explanation: In this example, we have a past date, and we want to calculate the number of years, months, and days between this date and today. The DATEDIF function combined with the TODAY function helps to calculate these values.
Example 8: Using TODAY with EDATE Function
Purpose: To calculate the date a certain number of months before or after today.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Months | Date |
| 2 | 3 | =EDATE(TODAY(), A2) |
| 3 | -3 | =EDATE(TODAY(), A3) |
Explanation: In this example, we want to calculate the date that is a certain number of months before or after today. The EDATE function combined with the TODAY function helps to estimate these dates.
Example 9: Using TODAY with WORKDAY Function
Purpose: To calculate the date that is a certain number of workdays before or after today.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Workdays | Date |
| 2 | 5 | =WORKDAY(TODAY(), A2) |
| 3 | -5 | =WORKDAY(TODAY(), A3) |
Explanation: In this example, we want to calculate the date that is a certain number of workdays before or after today. The WORKDAY function combined with the TODAY function helps to estimate these dates.
Example 10: Using TODAY with NETWORKDAYS Function
Purpose: To calculate the workdays between a past date and today.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Past Date | Workdays |
| 2 | 2023-07-01 | =NETWORKDAYS(A2, TODAY()) |
Explanation: In this example, we have a past date and want to calculate the number of workdays between this date and today. The NETWORKDAYS function combined with the TODAY function helps to calculate the number of workdays.
Example 11: Using TODAY with YEARFRAC Function
Purpose: To calculate the fraction of the year represented by the number of whole days between a past date and today.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Past Date | Year Fraction |
| 2 | 2023-01-01 | =YEARFRAC(A2, TODAY()) |
Explanation: In this example, we have a past date, and we want to calculate the fraction of the year represented by the number of whole days between this date and today. The YEARFRAC function combined with the TODAY function helps to calculate the year fraction.
Example 12: Using TODAY with WEEKDAY Function
Purpose: To find the day of the week for today.
Data tables and formulas:
| A | |
|---|---|
| 1 | Day of Week |
| 2 | =WEEKDAY(TODAY()) |
Explanation: In this example, we want to find today’s day of the week. The WEEKDAY function combined with the TODAY function helps to see the day of the week.
Example 13: Using TODAY with EOMONTH Function
Purpose: To find the last day of the current month.
Data tables and formulas:
| A | |
|---|---|
| 1 | Last Day of the Month |
| 2 | =EOMONTH(TODAY(), 0) |
Explanation: In this example, we want to find the last day of the current month. The EOMONTH function combined with the TODAY function helps find the current month’s last day.
Part 3: Tips and Tricks
The TODAY function can be combined to create more complex formulas. For example, you can calculate someone’s age using the YEAR function.
Remember that Excel stores date as sequential serial numbers so that you can use these numbers in calculations.
The TODAY function does not have any arguments and does not require any input.
The TODAY function will update the date every time the worksheet is recalculated.
If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.