Comments on: Excel TODAY function to insert today's date and more

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. Continue reading →

Comments page 2. Total comments: 169

  1. Sir,

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

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

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

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

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

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

        Thank you Alexander

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

  5. 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!

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

  6. 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!

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

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

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

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

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

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

    1. Hi!
      If I understand your task correctly, the following formula should work for you:

      =IF(A1 < = TODAY(),TODAY(),A1)

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

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

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

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

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

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

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

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

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

    1. You can use: =SUMIFS(V2:V90,P2:P90,"="&TODAY())

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

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

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

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

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

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

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

  24. What is today ?

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

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

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

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

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

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

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

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

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

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

      =IF(A1 <> "",TODAY(),"")

      I hope this will help

  30. Hi,
    I need a formula for today date in Excel and if I open the same sheet then Date should remain same as yesterday..

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

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

      1. Worked like a charm. Thank you very much Alexander! Keep safe.

  32. What formula do I use to where it automatically sets the date for that week using a week to week time sheet

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

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

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

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

  36. =Today() default format(mm/dd/yyy) can be changed to dd/mm/yyyy?

  37. How do I do an age analysis on Excel?
    Created Date Time Age Analyse (TODAY)
    21/06/2019 12:19

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

  39. Hi, how to make a formula that counts the items that have yesterday and today's date?

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

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

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

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

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

  45. I WOULD TO HAVE ACCESS TO POWER PIVOT,POWER QUERRY COURSE,POWER B TUTORIAL

  46. how to freeze =now() or =today()? It must not change when we entered anywhere in cells
    =if(A2="",today(),"1")

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

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

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

  50. If have =+TODAY()+1 in cell that will give me tomorrow date, I want it will stop automatically when month will be over on 30th or 31st

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