TODAY Date Function in Excel (Table of Contents)
TODAY In Excel
Excel TODAY date function in is categorized as Date/Time Function that returns the current system date. The function in Date/Time category enables us to analyze and work with date and time values in formulas. TODAY function in Excel was introduced in Excel 2007.
TODAY Formula in Excel
Below is the Formula of TODAY() function:
Explanation of TODAY Formula in Excel
TODAY Formula in excel doesn’t take any argument
TODAY formula in Excel picked up the current date of the system. When we change the current date of the system to another date, the TODAY in Excel recalculated its value in cell B2 and picked up the changed current date in B3.
TODAY function in Excel also belongs to a special class of function called Volatile functions. Excel recalculates a volatile function whenever it recalculates the workbook, even if the TODAY formula that contains the function is not involved in the recalculation.
The TODAY in Excel represents an example of a volatile function because it recalculates the current date every time Excel calculates the worksheet. Other volatile functions include
As a side effect of using these volatile functions, Excel always prompts you to save the workbook when you close it, even if you made no changes to it. For example, if you open a workbook that contains any of these volatile functions, scroll around a bit (but don’t change anything), and then close the file. Excel asks whether you want to save the workbook.
You can circumvent this behavior by using the Manual Recalculation mode, with the Recalculate before save option turned off. Change the recalculation mode in the Calculate section of the Formulas tab in the Excel Options dialog box (choose File->Options).
TODAY function in Excel is updated whenever the worksheet is calculated. For example, if you enter either of the preceding formulas into a worksheet, the TODAY formula displays the current date. When you open the workbook tomorrow, though, it will display the current date for that day (not the date when you entered the formula). If we save the workbook containing Excel TODAY function and open it after 5 days, it will automatically show the current date of that day without any change to the formula.
TODAY in Excel is very useful when we deal with date and time. For example
We have a list of 5 Persons given with their date of birth and we need to calculate the current age of each person.
|Name||Date of Birth||Age|
To calculate the Age of the Person we will use TODAY, YEARFRAC and INT function. YEARFRAC function computes the year fraction representing the number of whole days between the start date and end date. It takes three arguments.
So the TODAY formula in excel to calculate the age will be
Applying the TODAY formula in Excel to other cells we have,
How to Use TODAY Function in Excel?
Excel TODAY date Function is very simple and easy to use. Let understand the working of TODAY in excel by some examples.
TODAY in Excel Example #1
If we want to calculate the number of days of a year for the current date, for example, the excel today’s date is 08/1/2018 and the user wants to calculate the number of days till the current date.
So, the total number of days till the current date is 213
Now using the Excel TODAY date Function, Year and Date function we can calculate the number of days of the year for the current date:
The TODAY Formula in Excel is:
TODAY in Excel Example #2
A Service based company named SS Brother Solutions provides the maintenance for Printers. The company has the list of clients with an annual maintenance contract’s (AMC) end date and the amount of AMC for the year 2018. The Manager has been asked to provide the total amount of AMC pending for the current year from the current date.
|Client||AMC End Date||Amount|
|Alliant Techs stems||3/1/2018||$9,280.00|
|Intercontinental Manufacturing Company||7/16/2018||$10,803.00|
|Regions Financial Corporation||6/24/2018||$7,687.00|
For the upcoming months Aug, Sep… till Dec the manager needs to calculate the AMC amount pending.
There are 5 companies whose AMC will be collected on the given AMC end date.
In order to calculate the total amount pending we will use the SUMIF and Excel TODAY date Function to compute the amount pending for the current year
The TODAY Formula in Excel will be
So, the total AMC amount pending for the upcoming dates is $51,743
Displaying the Current Time Using TODAY Excel Function
TODAY formula in Excel displays the current time as a time serial number (or a serial number without an associated date):
You need to format the cell with a time format to view the result as a recognizable time. The easiest way is to choose Home->Number->Format Number and then select Time from the dropdown list.
You can also display the time, combined with text. The Excel TODAY date Function that follows displays this text:
The current time is 3:56 AM.
=”The current time is “&TEXT(NOW(),”h: mm AM/PM”)
TODAY in Excel Example #3
We have a list of items with their purchase dates and we need to find the count of the items purchased on the current date.
So, in order to find the total count number of the item purchased on a current date, we will use the COUNTIF and TODAY Excel function
The TODAY Formula in Excel will be
Determining the date of the most recent Sunday
You can use the following TODAY formula to return the date for the previous Sunday. If the current day is a Sunday, the Excel TODAY date Function returns the current date. (You will need to format the cell to display as a date.)
To modify this formula to find the date of a day other than Sunday, change the 1 to a different number between 2 (for Monday) and 7 (for Saturday).
You can download this TODAY Function in Excel template here – TODAY Function Excel Template
This has been a guide to TODAY Function in Excel. Here we discuss the TODAY Formula in excel and how to use Excel TODAY date Function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –