Today’s Date

What is the Today() function?

When you are too busy with work, a simple thing such as remembering the current date can be pretty tricky. On top of that, there could be instances when, despite a built-in clock in your computer, you might end up checking your cell phone for the current date.

But it is understandable - Our jobs are demanding, many things are going on in our lives, and at the end of the day, we are just looking forward to being home! 

When the mind is distracted, having enough knowledge of a simple function could be helpful. Using the function in Excel, you can return the current date in your reports. 

Today's Function in Excel is categorized as a Date & Time function and exactly does what the name suggests. It returns the current date in Excel.

The function is not just limited to returning the current date. It can be used to find the days or months between the current date and certain other days. The syntax for the function is straightforward:

=TODAY()

When you begin the function with an equal(=) sign, type in the function's name and end the argument with closed empty brackets "()." So if you are working on one of your financial models, you can easily just type the function, and Excel will display the current date as:

However, there is one crucial thing that you must remember after using the function. 

If you save the Excel and open it tomorrow, it will display the current date as 4/2/2022. That means Excel dynamically adopts the date for the function without you making any changes. 

Even if the date does not update tomorrow, you must ensure the Excel formula calculations are not set to 'Manual' in the options. 

If it's set to 'Manual,' you can change it to 'Automatic' or press F9 in the case of 'Manual' settings to calculate the result for the formulas in the spreadsheet.

Today's date as a static, unchangeable value

We have stressed several times the fact that Excel can accomplish the same task in several different ways. Each way of achieving the task has its benefits in what you do as well as its drawbacks. 

Now, if you need an Excel file where the current date should not change even if you open it after a couple of days, then you can use a keyboard shortcut for a 'static' current date.

All you need to do is press the Ctrl + semicolon key( ; ) on the keyboard, and Excel will return the current date for you.

The difference is quite evident: the value returned using the Ctrl + semicolon key (;) is similar to hardcoding the current date in Excel, so when you next open the file, the date does not change.

Changing the date format

The issue with using the function could be that all your existing data for dates might be in a different format, say 1st April 2022. 

If you prefer a specific format for the date, all you need to do is highlight the cell consisting of the date and press the keyboard shortcut of Ctrl + 1 or right-click on the cell and select the 'Format Cell' option from the menu. 

This will open up the format cells dialog box as:

Here, you can scroll down and select the desired format for the date and create any custom date format in the ‘Custom’ category. Once done, click on OK. One of your new formats should look as illustrated below:

Different ways to use the TODAY() Date function

There is no limit to using this function. If you need to find the difference between the current and a particular day in the past or future, you can use the function. You can also find a specific date by subtracting the number of days. 

You can combine it with other functions, such as EDATE, which adds months to a given date in Excel.

1. Finding the difference of days between TODAY() and a particular date

Suppose you must submit a project on 5th May 2022 to the clients. If the date is 2nd April 2022, and you want to find out how many days you have before the deadline, then you can subtract the latter date from the former to calculate the number of days you have.

You have the deadline date as 05/05/2022 in cell C2, while in cell C4, by using the formula, you will get the current date(which for us is) 04/02/2022.

Now all you need to do is subtract cell C4 from cell C2 such that C6 = C2-C4 to give you the result:

Suppose that the IT filing's last date was 03/31/2022 and the current date is 05/05/2022. You can also determine how many days have gone by since your delay in tax payment. This can further help you to determine the additional penalty interest levied on the tax payments.

Similarly, subtracting the deadline date from today’s date, we get the result of 35, meaning it has been 35 days since our tax payments were delayed.

2. Finding the date in the past or future

You must have heard iconic dialogues in movies where the kidnappers demand ransom in X days. “WE HAVE YOUR KID. DO NOT LET THE COPS KNOW. YOU HAVE 25 DAYS TO TRANSFER 100 BITCOINS IN XXXX XXXX WALLET”. 

Counting the days in the future if the current date is 1st April 2022 is relatively easy. But what if the date is 22nd April or 18th April (calculating in mind makes our brain fuzzy)? 

In movies during times like this, the investigative team brings in their calendar, counts the number of days in the future, and marks a big X on the date.

In Excel, you can find the deadline day in just two steps:

• Input current date
• Subtract the number of days

So let's say that if one of your clients says to get the project done in twenty-one days, all you need to do is type the formula in cell C2 and hardcode the value 21 in cell C4.

To find the deadline date, add both the cells, i.e., =C2+C4, to give you the result as illustrated below:

Now you know the deadline for the project can determine whether to release the project early or not depending on if the deadline day falls on the weekend. 

Similarly, if you need to find a date in the past, all you need to do is subtract the date from days gone by using the formula =C2-C4 to give the result:

3. Excluding weekends from your day's calculations

The WORKDAY function allows you to exclude the weekends in finding a particular date. 

To be honest, nesting the function inside this WORKDAY gives more appropriate deadline days for the client's project rather than just using the former function since it skips Saturdays and Sundays.

So, if you assume that the clients expect you to complete the project in 21 working days from now, you can use the formula =WORKDAY(TODAY(),21) to give the result as illustrated below:

By using a negative sign before the day's argument in the formula, you can also find the dates in the past.

4. Skipping months or years from today in the past or future

Suppose that you buy medicines manufactured with today’s date. If the medicine can be consumed three months from its manufacturing date, what is its expiry date?

You can easily find solutions for such expiration dates, maturity dates, and other due date problems by combining the TODAY() and EDATE functions.

If the shelf life of the medicine is three months from the current date, all you need to do is use the formula =EDATE(TODAY(),3) to give you the result as 

This means the medicine will expire on 2nd July 2022, and you will need to dispose of it after that. Similarly, by multiplying 12 by the month's parameter of the EDATE function, you can find the expiry or maturity date after n years.

If you purchase bonds now, the bond's maturity date will be given by the formula =EDATE(TODAY(),3*12) to provide you with the result as 2nd April 2025, which will be your maturity date for the bonds.

If you need to jump months or years in the past, you must give a minus sign before the month's argument.

Another function that works well with the TODAY() function - is the EOMONTH function or EDATE’s younger sibling (since it is the modified version of the EDATE function). Unlike the EDATE function, EOMONTH returns the last day of the month in the past or future. 

To all the HRs, suppose that if a candidate joins your organization on 15th April 2022 and the probation period is three months, you can determine when the candidate becomes a full-time employee at the organization.

All you need to do is use the formula =EOMONTH(TODAY(),3)+1 to give you the result:

Since EOMONTH gives the last day of the month, you need to add one at the end to provide the beginning of the following month. If you need to read more about EDATE and EOMONTH functions, check out our dedicated article on how you can add months to dates here!

Today's Date: Practical Example #1

Suppose you have a series of dates in Excel and need to find the date closest to the current date. The data that you have in Excel is illustrated below:

To find the date closest to the current date, you will use the combination of MAX, IF, and TODAY functions.

The formula will be =MAX(IF($B$3:$B$12<TODAY(),$B$3:$B$12)), which will give you the result in cell E4 as:

This formula finds the closest date from the past from a range of dates. If you need to find the closest date from the future, you just need to flip the less-than sign (<) to greater than sign (>), and the MAX function is replaced by the MIN function.

Consider the below data where you need to find the nearest date from the future:

Here, we will use the formula =MIN(IF($B$3:$B$12>TODAY(),$B$3:$B$12)) in cell E4 to give us the result:

From all the dates in column B, 2nd May is nearest to our current date, which at the time of writing the article is 2nd April 2022.

Today's Date: Practical Example #2

Some of the datasets usually do not have the age of people but rather have their birthdate.

It could be a problem if you are looking to group people into several age groups since you cannot directly group them based on birth year. So what can you do?

All you will need is a combination of two functions - YEAR and TODAY to find the age from the person’s birth year. 

If the birth year is, let's say, 1997, then the formula to find the age is =YEAR(TODAY())-C2 to give you the result as 25.

Even if you have the birth date in MM/DD/YYYY format, you can still find the age difference even up to the day. 

The formula becomes a bit long, but the logic is quite simple. The formula that you will be using is:=YEAR(TODAY()) - YEAR(C3) &" Years"&" "&MONTH(TODAY()) - MONTH(C3) &" Months "&" "&DAY(TODAY()) - DAY(C3)&" Days" to give you the result as

A bit of concatenation will take you a long way in writing complex formulas!

Researched and Authored by Akash Bagul | Linkedin

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Excel Solver
• Excel Shortcuts
• Histogram
• Slicer in Excel
• VLOOKUP