What is the “TODAY Function”?
The TODAY function in Excel returns the current date. By default, the date is returned in a serial number format because Excel stores dates as serial numbers. The cell format needs to be changed to “Date” to display the function’s result in a date format.
When used with other functions, the TODAY function has multiple applications in financial analysis. For example, it can be used to calculate the age of unpaid invoices or employees’ tenure.
Key Learning Points
- =TODAY is an Excel function that returns today’s date
- The function returns the date in a serial number format. The cell format needs to be set to date in order to view the result in a date format
- The TODAY function is useful to find out dates for events a certain number of days, months, or years away from today’s date
- When combined with the WORKDAY function, the TODAY function can help calculate a specific number of working days (before or after) away from today
- The TODAY function, when used with the EOMONTH function, can help calculate the month-end date for a specified month away from today. This combination is useful for payroll and invoicing.
- The function’s syntax has no arguments. It includes the phrase “TODAY” followed by an empty and closed parenthesis
The syntax of TODAY function has no arguments. The function includes the phrase “TODAY” followed by an empty parenthesis. You need to use a closed parenthesis in order to return the date.
Important Point about the TODAY Function:
- The function calculates the current date automatically whenever a user opens a sheet. If there is no change in the date, press F9 to force the sheet to update the date.
Find out today’s date using the TODAY function.
Just to recap on how excel calculates dates, when we enter the formula, the date is displayed as a serial number. In Excel, every date is a serial number starting from 01/01/1900, which it considers as “1” or the first date. The date of writing this blog is 44,224 days after the 01/01/1900. By changing the cell format to “Date”, the data in the cell updates to a date format. This can be done using the “Number” format section under Home. Alternatively, you can right-click on the cell and change the format using the format cells option.
This is the result after changing the cell format to date. We have used the “short date” format, but Excel has various other formats to display a date.
We have been asked to calculate the age of invoices based on the information below.
To calculate the invoice age, we subtract the invoice date (calculated using the TODAY function) from the invoice aging date.
The invoice age will get refreshed automatically based on the date changes in C10. It is important to note that the C10 cell is locked using the dollar ‘$’ signs in order to make copying formulas easier.
*If you download our workout, remember that your numbers will not be the same due to the difference in today’s date.
Using the results returned by the TODAY function, we have been asked to calculate the following:
- 5 working days after today
- 3 months after today’s date (end of the month as payments for this business become due at month-end)
To get these results, we will use the WORKDAY and EOMONTH functions along with the TODAY function.
First, we find today’s date using the TODAY function (B26).
Next, we use the WORKDAY function which is used to calculate a date x working days away from a specified date. We want to calculate 5 days away from today’s date, so we supply these two arguments (B26 for today’s date and C23 for 5) to the WORKDAY function. The output is displayed in C26.
The EOMONTH function calculates specified months (end of the month) away from today’s date. We supply today’s date (B26) and 3 (D23) as arguments to this function to get 30-Apr-21 (D26) as the result.