Excel TODAY function to insert today's date and more

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:
Excel TODAY function

4 things you should know about TODAY in Excel

  1. TODAY() is a volatile function, which means that it updates itself continuously every time a worksheet is opened or changed.
  2. 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.
  3. To enter today's date in Excel as a static unchangeable value, use these keyboard shortcuts.
  4. 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.
Formula to insert today's date in Excel

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:
Add or subtract days to/from today's date

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:

date-TODAY()

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:
Get the number of days before a future date

To calculate the number of days since a certain date, subtract the past date from today's date:

TODAY()-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"
Get the number of days since a past date

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:

DATEDIF(past_ date, TODAY(),"m")

To get the number of months between today and a future date, swap the start_date and end_date arguments:

DATEDIF(TODAY(), future-date,"m")

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

Get the number of months between today and a past date Get the number of months between today and a future date

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:

DATEDIF(past_ date, TODAY(),"y")

To calculate years before a future date:

DATEDIF(TODAY(), future-date,"y")

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

Get the number of years since a past date Get the number of years before a future date

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:

YEAR( TODAY())-year_of_birth

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:
Formula to get age from birthdate

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:

MAX(IF(range < TODAY(), range))

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 past date closest to today

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:

MIN(IF(range >TODAY(), range))

For our sample data set, we'd use this formula:

=MIN(IF($A$2:$A$10 > TODAY(), $A$2:$A$10))
Get a future date closest to today

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:

INDEX(range, MATCH(MIN(ABS(range - TODAY())), ABS(range - TODAY()), 0))

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:
Get the nearest date to today

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:
Highlight today's date in Excel

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)

Table of contents

169 comments

  1. Question. I am making a spreadsheet for my work and in columb B are dates, some are blank because we have not got that far. Column C is days sence column B. I use =today()-b2 for row 2 and it shows a number lets say 29. Row 3 Column B has no date yet and Column C has the formula =today()-b3 but it displays 45405. Is there a way to hide 45405 or make blank until I put a date into Column B?

  2. Hi Team, is there a way to get Day as a value from Today function?

    I have tried Text(today(),"dd") but return number as a text.

    Thanks for you help.

  3. Hi :)

    I have a sheet that i use Windings 2 - to add a checkmark.
    And i want to add todays date/time when i check off the cell with the checkbox. (its; "P") '

    How can do that?

    Rows i use: L4 to L1000 - and i want date to appear in row: R4 to R1000

    Appreciate the help!!

  4. PLS ADVISE FORMULA FOR, (A DATE 30 BY TODAY DATE NEED A AUTO TEXT OF "TRU OR FALS".)

  5. Is there a way to make =TODAY() update only if the document is changed? We can manually put in a date when we update the sheet, but people tend to forget.

    • Edit: any time the document is opens, it updates. The article doesn't show a "only if" option

    • Hi!
      TODAY() changes the value when you recalculate formulas and when you open a workbook. The standard features of Excel do not allow you to change TODAY() just when the data changes. This is possible with a VBA macro.

  6. Hello,
    Can you help, I’ve a cell A1 with TODAY entered ( formatted to mmm-yyyy). I’m trying to compare A2-A10 containing sequential month and year formatted the same as A1, to produce a transpose sheet if the correct month and year tally up or produce a 0.
    I’m using IF(A2= A1, “transpose sheet x”, “0”)
    My result alway produce 0 even when A2=A1 eg, Jan-2023=Jan-2023.

    • Hi! What value is written in A2? I assume that text is written there. If you compare the date to the text, condition A2=A1 will never hold. You can convert the date to text with the TEXT function. For example, TEXT(A1,"mmm-yyyy")

  7. I need a formula, where I mark work as done then in the next column it shows the exact date and that can't be changed.

    • Hi!
      To prevent your date from automatically changing, you can use several methods:
      1. Use Shortcuts to insert the current date and time as described above.
      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 understand what he asked for and i need it also.
        i want to add some data to B1 B2 B3 ..etc

        whenever i add data to B1, i want excel to autowrite today's date in A1 static.
        wheenver i add data to B2 , i want excel to autowrite today's date in A2 as static data.

        ctrl+;
        is manuel, can we do it autowrite?

        • Hi! If the recommendations given to you earlier do not suit you, try writing and using VBA code. There are no other standard Excel features.

  8. Is there something for :
    If any changes are made in column A-I then column J will update with the current date?

  9. Hi,
    How to show on the day of the month, in today() formula.
    example: 22 /11/22 ( November 22, 2022), i want to show or extract only the date of month which 22.
    thank you

  10. Is there a way to pull from multiple dates using this? I've been collecting numbers based off of the current date, and have now been asked to pull data by a week instead of daily. I've got TODAY() built into my formula, but want to pull a weeks worth of data without having to change too much of my formula.

      • So, I'm pulling data from another workbook and I've been searching by sheet. Is there a way I could even just change it to search the entire workbook rather than just by the sheet. Each time the month changes, I have to change every formula. Due to the large amount of data, I break it the workbook down month by month. Here's my current formula. So currently I'm using Sep2 which represents September 22. At the end of the month, I'll have to go through about 30-40 formulas and update all of them to Oct2.

        =COUNTIFS('[COVID-19 Positive 2021.xlsx]Sep2'!$Q:$Q, "Confirmed", '[COVID-19 Positive 2021.xlsx]Sep2'!$N:$N,"<11",'[COVID-19 Positive 2021.xlsx]Sep2'!$U:$U,TODAY())

  11. The date returned by the TODAY function in Excel updates automatically when your worksheet is recalculated to reflect the current date, which function I should use so that the return date can be fixed.
    For example when x=y then return date , and this date should be fixed.
    could you please help me in this.
    Thanks in advance

    • Hello!
      To prevent your date from automatically changing, you can use several methods:
      1. Use the recommendations from this article in our blog.
      2. 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.

  12. If the total amount is more than rs. 100000 then payment date will be 15 days after inword date

  13. Hi,

    There are two dates available as mentioned below; which are Received date and the Conclude date, from which i can calculate the turnaround time (TAT) as 18 days (Conclude Date - Received date). However i just want to calculate the TAT, if a date available in the Conclude date if not from Todays date. Appreciate your support on creation of a formula.

    Received Date Conclude Date TAT
    01 Jul 22 19 July 22 18

      • Thanks for your reply.
        1.Just imagine that in the data received column date is mentioned as 01 Jul 22.
        and data was
        Received Date Conclude Date TAT
        01 Jul 22 19 July 22 18

      • Hi,
        Sorry. mistakenly i entered without completing the reply.
        what i want to describe you more clearly is
        1. in the data received column date is on 01 July 2022
        2. in the data concluded column date is mentioned as 19 July 2022
        3. i want to get the number of dates from data received date to data concluded date in the turn around time (TAT)column as the result.
        Next
        1. when the data concluded column date is not available it can be taken the date as todays' date to calculate the number of days for the turn around time column.
        ***Now my requirement is to get the result in the same TAT column (turn around time column)with two different ways i have above mentioned. It means when the date is available in the concluded date column and when the date is not available in the concluded date column.
        Hope now your clear on my question.

          • Hi Alexander,
            It works perfectly as per my requirement. Thank you very much and appreciate your prompt response. Thanks again!!!!

  14. Hello,
    I have a COUNTIF formula to count the number of times employees are scheduled attend training in a date range. The formula I use is fe
    =COUNTIF(Table[@[Wednesday, June 22, 2022] :[Thursday, June 30, 2022]], 'TRAINING")
    As of now I have to manually change the current date every day. Is there a way to update Wednesday, June 22, 2022 to the current date everyday?
    The training category is only one out of 57 categories that I currently update manually.

    • Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. To insert current date, use TODAY function, as described above.

      • Hello Alexander,
        Thank you for your response. I did try using the TODAY function and NOW function but they did not work. The spreadsheet that I am using has the dates in row 1 in long date format. Column A has a list of approximately 500 employees. As the days go on it’s necessary for me to keep changing the first date in the reference to the current calendar day so that I only show the scheduled TRAINING dates for the future.
        I’m not sure if this helps, but any assistance you can provide would be greatly appreciated. Thank you.

  15. Is it possible to have: If past today's date add a predefined text in another cell.

    For Example
    Cell A: Date is yesterday
    Cell B: "Expired" text is automatically added

  16. If in A column has today's date, and there are some text data in b column which needs to be captured in C column only if its a today date,

    How can we do that ?

  17. I have subsheet where, daily dates written from A1 to Z1 row & below that, in row A2 to Z2 respective figure of data on that particular day in number. I want to show the value of today in my main sheet as per the today's date value i have mentioned in my subsheet. Please help.

  18. Hi, Can anyone help, please? If I have column B for due dates and Column C for amounts and I need to sum up amounts base on their days delayed (todays date - due date) to over 120, <120, <90, <60, <30 and current

  19. Greetings,

    How can i freeze the current date in cell B1, using "if" or "ifs" statement?
    For example:
    In cell A1 i have put folding menu, when specific value or text shows up, in cell B1 i need to freeze the current date.
    Now i am using: ifs(A1="Done",today())
    But i suppose it will change each day.

    All kind of support will be useful.

    Thanks in advance!

  20. Hi Alexander Trifuntov, or somebody from the Ablebits Team,

    would you please help me with a function to display time/date in cell "C" if there's an input either in cell "A" *or* "B", as of now I found this formula that works well:

    =IF(A2"",IF(C2="",NOW(),C2),"")

    but it's only when something's input in "A". I haven't been able to modify it to check and work if instead of "A", a value in "B" is also inserted. Much appreciated your help. ?

    • Please, excuse me, a second question: how is it possible to hide what's written in a cell? I received a spreadsheet with values that aren't visible, but only in the formula bar. I thought that letters were changed to white color to match with the background but that isn't the case!

      No matter what color I put the cell no writing appears.

    • 00/01/1900 00:00:00

  21. Sir,

    I need a formula for shift wise timing i.e Shift A, B and C with different time, can you help

  22. I need to check attendance/ leave/ etc. for today. So I use following to find "today" date and it is working.
    =HYPERLINK("#"&CELL("address",INDEX(DATES,MATCH(TODAY(),DATES,0))),"TODAY")

    Now I need to get attendance type (i.e. Duty (D)/ Leave/ Sick..) against the names of people.
    =COUNTIF(G4:G22,"D") In this formula, how to get the details automatically for today instead of G4:G22

    Please help.

    • Hi!
      The information you provided is not enough to understand your case and give you any advice. Unfortunately, without seeing your data I can't understand your formula. Please provide me with an example of the source data and the expected result.

  23. Dear Alexander, Hello

    i need a formula

    I have dates in Column A e.g.

    Column A
    January 8, 2021
    January 7, 2021
    January 6, 2021

    i want formula in Column B to convert Column A Dates into English :Today, Yesterday Last week

    Column A
    January 8, 2021

    Column B "Should be"
    Today

    • Hello!
      The formula below will do the trick for you:

      =IF(TODAY()-A1=0,"Today",IF(TODAY()-A1=1,"Yesterday",IF(TODAY()-WEEKDAY(TODAY(),2)-7<A1,"Last Week",)))

      You can read more about WEEKDAY function in this article on our blog.

      • Thanks alot :) am so happy my problem have been solved! it works :D:D:D:D:D:D

        Thank you Alexander

  24. Hello,
    would like to get a formula for a cell to reflect the sum of values in another sheet for that present date in a table.
    Thank you.

  25. I need the words FILE CLAIM to appear in the cell on a certain date (value in Column B) and STAY there until I remove it.

    Right now, it appears on the date in Column B but disappears after the date passes.
    I tried the following and it didn't work:

    =IF(B2=(TODAY()+20),"FILE CLAIM","")

    Any suggestions? Thanks!

    • Hello!
      If I got you right, you can change the condition. Instead of the = sign, write >=.

      =IF(B2>=(TODAY()+20),”FILE CLAIM”,””)

      Hope this is what you need.

  26. Hey everyone,

    I have a spreadsheet that I use as a master template everyday and save a new copy once I have input the data I need. My issue is sometimes I will open the file at 7pm and save the copy after midnight.

    Is there a way to use TODAY() to generate the day of business and not refresh itself after midnight as 'the next day'? Also can I save the file without having it change the date on me if I open a copy to look back at that days data?

    Thanks for your help!

  27. =TEXT(TODAY(),"DD mm/dd/yyyy")&" " & IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))&IF(MINUTE(NOW())11," pm"," am")

    Gives it all in one cell Example:
    DD mm/dd/yy
    Sun 11/07/21 6:13pm

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =TEXT(TODAY(),"DDD mm/dd/yyyy")&" "&IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))&IF(MINUTE(NOW())*11," pm"," am")

  28. Help Please,

    my current formula for column B =IF(TODAY()-A5<=1460,"Current", "Overdue"). I need column B to say "overdue" if the date is greater then 1 year.

    Thank you.

  29. Hello!
    I would like a formula using today that will also include the day of the week. For, example right now I am using =today()+7 if I have an assignment do the next week, but I would also like to know what day of the week that is. Thank you.

  30. 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?

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

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

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

  34. 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?

  35. 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"

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

  37. 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?

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

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)