by The tutorial shows how to insert today's date in your worksheets and demonstrates a few other uses of TODAY function in Excel.
You want to put today's date in some cell? Or you wish to highlight the current date in your Excel calendar? Or you'd like to find the date closest to today? All this can be done by using the Excel TODAY function and this tutorial will teach you how.
Excel TODAY function - syntax and basic uses
The TODAY function in Excel does exactly what its name indicates - returns today's date.
The syntax of the TODAY function is as simple as it could possible be - it does not have any arguments at all. Whenever you need to insert today's date in Excel, just enter the following formula in a cell:
=TODAY()
You can format the value returned by TODAY in any built-in or custom date format. For example, this way:
4 things you should know about TODAY in Excel
- TODAY() is a volatile function, which means that it updates itself continuously every time a worksheet is opened or changed.
- If a TODAY formula does not update automatically, most likely automatic recalculation is turned off in your workbook. To turn it on again, go to the Formulas tab > Calculation Options, and select Automatic.
- To enter today's date in Excel as a static unchangeable value, use these keyboard shortcuts.
- If you want to insert current date and time, use the NOW function instead of TODAY.
How to insert today's date in Excel
There are two ways to enter the current date in Excel - a formula and shortcut. Which one to use depends on whether you want a static or dynamic value.
Excel formula for today's date
The value returned by the TODAY function updates automatically, so the below formula is useful if you want the worksheet to always display the current date, regardless of when you open it.
=TODAY()
To clarify what kind of date that is, you can concatenate TODAY() with some explanatory text, for example:
=CONCATENATE("Today is ",TEXT(TODAY(), "mmmm dd, yyyy"))
Because in the internal Excel system dates and times are stored as numbers, concatenating text with the TODAY() formula directly would result in a meaningless string like "Today is 42965". To avoid this, we nest Excel's TODAY function within the TEXT function to display the date in the desired format.
Shortcuts to get today's date in Excel
The inset today's date as an unchangeable timestamp that won't automatically update the next day, use one of the following keyboard shortcuts:
- To inset today's date: Ctrl + ;
- To insert the current time: Ctrl + Shift + ;
- To enter the current date and time: Ctrl + ; then Space and then Ctrl + Shift + ;
How to use TODAY function in Excel - formula examples
As you already know, the main purpose of the Excel TODAY function is to get today's date. Aside from that, you can use TODAY() in combination with other functions to perform more complex calculations based on the current date. Below you will find a few examples of such formulas.
Add or subtract days to/from today's date
To add or subtract a specific number of days to/from the current date, use a simple arithmetic operation of addition or subtraction, respectively.
For example, to add 7 days to todays' date, use this formula:
=TODAY()+7
To subtract 7 days from the current date, go with this one:
=TODAY()-7
To exclude weekends (Saturday and Sunday) from your calculations, nest the TODAY function within WORKDAY that deals with weekdays only:
To add 7 workdays to today's date:
=WORKDAY(TODAY(), 7)
To subtract 7 workdays from today's date:
=WORKDAY(TODAY(), -7)
The following screenshot shows the results:
Tip. To display the calculated date correctly, set the formula cell's format to Date.
Get the number of days before or after a certain date
To calculate how many days are left before some data, subtract today's date from the future date you are counting toward:
The date can be supplied directly to a formula in the format that Excel can understand, or by using the DATE function, or as a reference to the cell containing the date.
For example, to find out how many days are left till December 31, 2017, use one of these formulas:
=A2-TODAY()
=DATE(2017,12,31)-TODAY()
="12/31/2017"-TODAY()
All three formulas tell us that at the moment of writing (August 17, 2017), 136 days were left till the end of the year 2017:
To calculate the number of days since a certain date, subtract the past date from today's date:
For example, to find out how many days have passed since January 1, 2017, use one of these formulas:
=TODAY()-A2
=TODAY()-DATE(2017,1,1)
=TODAY()-"1/1/2017"
Tip. If the result is not displayed correct, be sure to apply the General format to the formula cell.
Calculate months since or before a certain date
To get the number of months between today and a past date, use the DATEDIF(start_date, end_date, unit) function with the past date in the start_date argument, TODAY() as end_date and "m" unit denoting months:
To get the number of months between today and a future date, swap the start_date and end_date arguments:
With the date of interest in cell A4, use the following formulas to calculate time difference in the number of complete months:
To calculate months since a certain date in the past:
=DATEDIF(A4,TODAY(),"m")
To calculate months before a certain date in the future:
=DATEDIF(TODAY(),A4,"m")
 |
 |
Calculate years since / before a certain date
The formulas to calculate years based on today's date are similar to the ones discussed in the above example. The difference is that you use "y" unit to get the number of complete years between today and another data:
To calculate years since a past date:
To calculate years before a future date:
Assuming the past/future date is in cell A4, you should be good with using these formulas:
The number of complete years since date:
=DATEDIF(A4,TODAY(),"y")
The number of complete years before date:
=DATEDIF(TODAY(),A4,"y")
 |
 |
For more information about the DATEDIF function, please see Excel DATEDIF - calculate difference between two dates.
Get age from birthdate
If you know someone's year of birth, you can subtract that year from the current year to find the person's age:
For example, if the person was born in 2000, you use the following formula to get his/her age:
=YEAR( TODAY())-2000
Or, you can enter the year of birth in a separate cell and reference that cell in your formula:
You can learn a few other age calculation formulas in this tutorial: How to get age from date of birth in Excel.
Find the nearest date to today
If you are curious to know which date in a list is closest to today's date, use one of the following array formulas to find it out.
Get a past date closest to today
To find a past date nearest to the current date, first "filter out" the dates greater than today, and then use the MAX function to get the largest date among the remaining ones:
With the dates in cells A2 to A10, the formula would go as follows:
=MAX(IF($A$2:$A$10 < TODAY(), $A$2:$A$10))
Get a future date closest to today
To find the nearest future date, identify the dates that are greater than today, and then use the MIN function to get the smallest date among them:
For our sample data set, we'd use this formula:
=MIN(IF($A$2:$A$10 > TODAY(), $A$2:$A$10))
Get any date closest to today
To get the nearest date in a mixed list of past and future dates, use the classic INDEX MATCH formula with a couple of modifications:
Here's how this generic formula works:
- MIN(ABS(range -TODAY())) part. First, you subtract today's date from each date in the range of dates. Then, the ABS function returns the differences as absolute values without regard to their sign. The MIN finds the minimal value, which goes to INDEX MATCH as the lookup value.
- ABS(range -TODAY()) part. You subtract today's date from the range of dates and return an array of absolute values. This array is where INDEX MATCH searches for the lookup value.
In this example, the formula takes the following shape:
=INDEX($A$2:$A$10, MATCH(MIN(ABS($A$2:$A$10 - TODAY())), ABS($A$2:$A$10 - TODAY()), 0))
The screenshot below shows the result:
Note. All three formulas to get the nearest date are array formulas, so they should be completed by pressing Ctrl + Shift + Enter.
How to highlight today's date in Excel
When working with a long list of dates or designing your own calendar in Excel, you may want to have the current date highlighted. To have it done, create a conditional formatting rule with this formula:
=B2=TODAY()
Where B2 is the left-top-most cell of the range to which the rule applies.
The result may look something similar to this:
The detailed steps to set up a conditional formatting rule can be found here: How to create a conditional formatting rule based on formula.
To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. Thank you for reading!
Practice workbook for download
Excel TODAY formula - examples (.xlsx file)
More examples of TODAY formula in Excel
For more examples of using the TODAY function in Excel, please check out the following tutorials:
169 comments
Hi,
I need a formula for today date in Excel and if I open the same sheet then Date should remain same as yesterday..
Hello Avinash!
To enter today's date in Excel as a static unchangeable value, use these keyboard shortcuts.
Excellent information expressed in the simplest of manners. Thanks.
I am trying to keep track of my savings. I have a current balance, a standard monthly addition and a balance-to-date column. I want the balance-to-date to add the standard monthly addition on the 27th of every month for different savings. Can you assist?
Hello Jason,
Thank you for your feedback! If you want to increase the value in let's say A1 to a particular amount (1000, for example), you may do it with the help of the following formula:
=IF(TODAY() = DATE(YEAR(TODAY()), MONTH(TODAY()),27), A1+1000,A1)
Or just replace 1000 with the necessary value or cell reference and hit Enter. It'll do the trick.
Worked like a charm. Thank you very much Alexander! Keep safe.
What formula do I use to where it automatically sets the date for that week using a week to week time sheet
Hello Angel!
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Hi I am looking for a formula for:
If I have a dates in column A. Column B, sometimes it is either left blank or has a date.
I want to populate a formula that shows how long from today's date OR if there's a date in Column B, therefore it should choose that date in Column B, but not today's date, so if Column B had no date, it will choose today's date.
Is that possible?
Hello Pham!
Please use the following formula
=IFERROR(IF(B1>A1, B1-A1, TODAY()-A1), TODAY()-A1)
When i insert =today() formula date come yesterday.excel change date in day time 12pm.i check my region in cell and formate of time date all are correct.but in Excel or google sheet have problem.have any solution?i am thankful to you for answering me.
I want a cell to default to the current date that row was completed. If I use TODAY(), it changes every day. Row 1 would be completed on 1 day, row 2 completed on another day. The only way the date would ever change is if that row was modified. Thanks in advance
=Today() default format(mm/dd/yyy) can be changed to dd/mm/yyyy?
How do I do an age analysis on Excel?
Created Date Time Age Analyse (TODAY)
21/06/2019 12:19
hello experts,
is there any formula to get the monthly date from cell A1 to A30/31?
for example:
A1= 1/10/2019
A2=2/10/2019
.
A31 =31/10/2019
Hi Chanu,
It sounds like Excel's AutoFill feature is exactly what you are looking for. For more information, please see How to auto fill dates in Excel.
Hi, how to make a formula that counts the items that have yesterday and today's date?
I am attempting to use the formula as indicated above, however the result is displayed as #VALUE!
I am using the following string:
=MIN(IF($E$5:$E$9>TODAY(),$E$5:$E$9))
Any idea where I am going wrong?
I'd like excel to calulate the time between two dates to show how long someone was on a waiting list but when the second date is blank I'd like excel to calculate using today's date. I can do those two functions separately but not sure how to do them both together?
Hello, can anyone please help. I have a list of renewal dates and I’m looking for an excel formula which will highlight in yellow, the dates which are less than 1 month from today. Thank you
Hi,
I'm always looking at prior day and getting it =TODAY()-1. How can I get the Fridays date on Monday by using TODAY() formula?
Hi,
I need like this formula,
D36 is 15-Aug-2019
=IF(AND(D36>TODAY(), AND((=TODAY()-D36)>=0, (=TODAY()-D36)<=10))),"Inside","Outside")
When i use this one getting error, so please explain where i did mistake?
thank you
I WOULD TO HAVE ACCESS TO POWER PIVOT,POWER QUERRY COURSE,POWER B TUTORIAL
how to freeze =now() or =today()? It must not change when we entered anywhere in cells
=if(A2="",today(),"1")
Hi I am using the TODAY formula in a roster to highlight the current date on a roster however it is highlighting the incorrect date, the date highlighted is two days ahead of the correct date.
Hello- can really use help with this.....I have a list of assets in one column with just a number representing the value of how old each asset is.....I need a formula for another column- YEAR BUILT- so I need a YEAR formula that I can combine with TODAY()......I just can't figure out how to do it....I need to use TODAY(), because this would be an ongoing list
Hi
Please help
If I input on a cell number '1' then the current date will show, if '0' the cell leave blank.
Thanks for help