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
Hello, I would like a formula that will autopopulate todays date in cell A2 when cell A1 is filled with any value/text. Is there a formula I could use?
Hi!
Use the TODAY function as an argument to the IF function, which will check the value of cell A1.
=IF(A1<>"",TODAY(),"")
I have dates in column a, I need dates in column b that represent the date in column a conditionally. If date in column a is today or before then column b needs to be today’s date. If the date in column a is in the future then column b needs to stay that date.
Hi!
If I understand your task correctly, the following formula should work for you:
=IF(A1 < = TODAY(),TODAY(),A1)
I have 20 executives Names in column A and entered their date wise plan for sales for month row 1
( Column wise B to AE ) now I want to check cumulative plan at any random day,
here I want formula to show me addition in Column AF from day 1 to till that day and automatically update when I open spread sheet next day.
Pl help
Hello!
You may find this article useful - How to do a running total in Excel (Cumulative Sum formula)
If this is not what you wanted, please describe the problem in more detail.
Good afternoon,
I am having an issue that hopefully you could help with. I currently have a spreadsheet to track when I need to return to a product to perform the next step. Each step has a different length of time. Currently, I input the length of the step time in column B. Column E has the current time using "=Now", Column D calculates the time the step will finish "=SUM (B1 + E1)". I then manually enter the time in Column A so the time will not change when the spreadsheet updates. I would like to not have to manually enter the completion time (Column A) each time I enter a new step time length, yet I do not want to have to change the current time (Column E) each time either. Is there a solution? Thank you for any help you can provide.
Hello!
If I correctly understood the problem, you need to insert the current date so that it does not change.
I recommend using this comment.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Can anyone help? How can i default a date in to a cell one year on e.g. cell 3A = 15/03/2021 and i want cell 3B to default to 15/03/2022?
Hello!
I am assuming it is about cell A2 and A3.
Here is the article that may be helpful to you: How to add years to date in Excel.
I hope I answered your question.
Hi,
I need to write a formula to print the current date in the same cell once someone starts typing in it. For example, if today is 3/13/2021, and someone starts typing in this cell, I want the cell to then automatically fill in the current date, then a dash (or some other kind of separator) and then it will have their text displayed.
If I wanted to type "No additional updates" in a cell, the cell would then print "3/9/2021 - No additional updates"
Hello!
It is impossible to solve your problem using an Excel formula. You need to use VBA.
Good Morning,
I have tried to find an answer to the question below, within the above thread, but cannot find the solution.
We use excel to create quote, and I have used the =today formula, but do not want it to change when we open the quote on a future date - is there a method to do this
TIA
Neil
Hello!
To prevent your date from automatically changing, you can use several methods:
1. Use Shortcuts to insert the current date and time
2. Use the recommendations from this article in our blog.
3. Replace the date and time returned by the TODAY function with their values. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V.
I hope it’ll be helpful.
Suppose I have taken a loan of Rs. 10,000. I have to pay 1,000 per month to the owner on a particular date. Let, I taken Rs. 10,000 on Jan 21. 1,000 per month will be deducted on the last date of every month automatically from my bank. What formula should I apply in excel to show the outstanding?
Hello
I have an excel sheet with numbers in column V and dates in column P. The formula
=SUMIFS(V2:V90,P2:P90,"17/12/2020") gives the correct answer but when I try to use the TODAY() function such as =SUMIFS(V2:V90,P2:P90,"=TODAY()") all I get is zero. Today's date is 17/12/2020. How do I use the TODAY() function to get the right answer? I need it to be flexible so I can use TODAY()-1, TODAY()-2 etc. as well.
Thank you for any help.
You can use: =SUMIFS(V2:V90,P2:P90,"="&TODAY())
Hi,
I have a list of doctors to visit. A1 shows me the name of doctors and B1 calculate my visit to that doctor from my visit list which I have on another sheet. Now I want C1 to show the date when i visit that doctor 1st time and D1 to show the date when I visit 2nd time.
I tried using today function but it changed the date of C1 which I dont want.
Please guide
Hi there, and thank you very much for all the hints above. Although I have a question:
how could I simply insert this result:
Tue. 22 Feb 2021
this should be semi-automatic. i.e. it should be easy to make a table of such string.
Hello!
You can apply custom date format:
dddd"." dd mmm yyyy
You can learn more about date format in Excel in this article on our blog.
I hope it’ll be helpful.
Hello, I have a spreadsheet where there are contract end dates entered in column F. I would like to set up a rule, so that any contract end date that is earlier than TODAY's date is highlighted. It sounds like a simple thing to do but I've looked at different sources and tried setting up some conditional formatting but still haven't managed to make it work for me. Thank you.
Hello!
Here is the article that may be helpful to you: Excel conditional formatting for dates.
Hope this is what you need.
I know how to use the today function and have been using it successfully for years. Why would one of my workbooks suddenly refuse to calculate any formula tied to the today function? Before you give the obvious answer, I've checked to make sure calculations are set to automatic and have saved/reopened the workbook. Also, all my other formulas are working and, up until a couple of days ago, the today formulas were working as well.
I hope someone has the answer as I use this workbook daily. Thanks.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice.
Try opening your file on a different computer. Perhaps the problem is with the date and time settings.
Good evening,
I have a Cell A1 which is master cell that filled every day with sum data based on multiple selections from the document and based on Today's date which is in Column B1:B100 A1 is pasted in Column C1:C100 next to today's date and so the next day so that in Column C I have a history of data every day. Something like at the end of the dat Column C cell next to it's date is frozen with the value.
I managed to come this far: =if(B1:B100=today(),$A$1,....) and this is it, I can't keep the value frozen as the day passes.
Can anyone help?
Thank you in advance.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
What is today ?
Greetings!
I am creating a spreadsheet, I know how to make today's date appear in a cell and update every day that i open the spreadsheet. I would also like the day of the week to appear in the next cell over that corresponds with the date, which would also update every time i open the spreadsheet. How do i do this? Thank you in advance.
Hello Josie!
Read the section above carefully - How to insert today's date in Excel. How to determine the day of the week, read here.
I hope my advice will help you solve your task.
Hi
please tell me how can get data & time only first time like if I use now() function so it will go to change but I don't want change I just want one timing which time I put text in columns
Hello Gurpreet!
Replace the formulas with values. Just copy the cell(s) with your date (CTRL+C), then use Paste Special (CTRL+ALT+V) to insert values.
I'm working with a spreadsheet to track scholarship. Is there a way for me to have column/cell that updates with the static date of when it was awarded or is going to the field and pressing CTRL+; the only option.
Hello Mel!
There are two ways to write a static value to a cell: 1. You do it yourself manually. 2. This is done by the program using VBA
Hello
I'm using the following formula =(VLOOKUP(MAXIFS(Form1!$B:$B,Form1!$I:$I,$B20),Form1!$B:$BJ,15,0))
to return a value based on the most recent date (in column b). I now need to return a value from the same column, but for the next most recent date. Basically the values which are being returned are meter readings and I need to be able to return the most recent and the one prior to that so that I can subtract one from the other and calculate a volume.
Would be most grateful for any assistance.
Hello Stephen!
You use the formula
MAXIFS(Form1!$B:$B,Form1!$I:$I,$B20)
to search for the most recent date. Replace it with the formula
LARGE(IF(Form1!$I:$I=$B20,Form1!$B:$B,0),1)
To search for the previous date, replace 1 with 2. That is
LARGE(IF(Form1!$I:$I=$B20,Form1!$B:$B,0),2)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi can someone please help. I want a cell to have today's date in, TODAY(), only if another cell is completed. So we only need today's date if a start date has been entered already. Can someone help me with the formular. Many thanks
Hello Donna!
If I understand your task correctly, the following formula should work for you:
=IF(A1 <> "",TODAY(),"")
I hope this will help