TODAY Excel Function (Today's Date)

Excel Functions Tutorials

Excel Formulas Cheatsheet
Excel
- Excel Ribbons and Tabs
- Excel vs Access
Financial Functions in Excel
Logical Functions in Excel
TEXT Functions in Excel
Lookup Reference in Excel
Maths Functions in Excel
Date and Time Function in Excel
Statistical Function in Excel
Information Functions in Excel
Excel Charts
Excel Tools
Excel Tips

TODAY Date Function in Excel (Table of Contents)

TODAY In Excel
TODAY Formula Excel
How to Use TODAY Function in Excel?

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

CELL
INDIRECT
INFO
NOW
OFFSET
RAND

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 Excel 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
Person1	5/12/1986	?
Person2	4/17/1991	?
Person3	1/1/1976	?
Person4	8/8/2001	?
Person5	5/8/1988	?

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

=INT((YEARFRAC(TODAY(),B2,1)))

Applying the TODAY formula in Excel to other cells we have,

Output:

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()-DATE(YEAR(TODAY()),1,0)

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
ABC Company	4/2/2018	$6,000.00
XYZ Company	5/31/2018	$5,500.00
Supreme Company	2/28/2018	$9,043.00
Auber Company	9/19/2018	$10,301.00
HCL Technologies	10/29/2018	$11,049.00
Cistern Limited	6/19/2018	$11,232.00
Apollo Group	5/3/2018	$8,133.00
Akola Software’s	6/15/2018	$8,464.00
Alliant Techs stems	3/1/2018	$9,280.00
BFG Technologies	10/11/2018	$10,561.00
Deluxe Corporation	8/30/2018	$10,877.00
Envision Healthcare	8/20/2018	$8,955.00
Concur Technologies	5/29/2018	$8,690.00
Intercontinental Manufacturing Company	7/16/2018	$10,803.00
ITT Corporation	5/16/2018	$9,387.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

=SUMIF(B2:B17,”>=”&TODAY(),C2:C17)

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):

=NOW()-TODAY()

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

=COUNTIF(B2:B21,TODAY())

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.)

=TODAY()-MOD(TODAY()-1,7)

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

TODAY Excel Function (Today’s Date)