Comments on: How to insert dates in Excel - add today's date, auto fill a column with dates

This tutorial demonstrates various ways of entering dates in Excel. See how to insert today's date and current time as static or dynamic values, how to auto populate a column or row with weekdays, and how to auto fill random dates in Excel. Continue reading →

Comments page 2. Total comments: 226

  1. Hi

    How do I have my dates automatically generate after inputting the first date ? i.r in row 4 I have 1/5/23 then in 37 I want the date 2/5/23 and then 70 and so on.. without having the manually type the dates in. Thank you

  2. Hi, i am looking to add a fixed date the following month based on the date in a cell is there an easy way?

  3. Hi, I am working on a qualifications spreadsheet to allow a reader to view when a workers qualifications are going to expire. Next to each name there is an expiry date which has been hyperlinked to a document (the qualification). I have been requested to colour code these expiry dates with the below:

    red- when the date has expired
    orange- when the date has 3 months remaining
    green- when the date has more than 6 months remaining.

    I need the links to automatically change, so turn from green to orange for example, on its own.

    please can you help with this? Thanks

  4. Hi there,
    I have a problem and don't see anyone having a similar quastion above. I have 20.000 rows with dates from a database and I need to insert the missing dates between the rows.I have sorted the column by date from oldest to newest. I can do it manually but it will take ages. Is there any other way to do it ? or any suggestions?
    Alexander

      1. thank you very much sir. I will look up to it right now

  5. I love this community; it saves me many times a week it seems...

    I have a problem I've yet to find a solution to though... I need to fill multiple rows (7) with today's date, then the next 7 with tomorrows date, etc. for a full year.

    So,
    1/26/23
    1/26/23
    1/26/23
    1/26/23
    1/26/23
    1/26/23
    1/26/23
    1/27/23
    1/27/23
    1/27/23
    1/27/23
    1/27/23
    1/27/23
    1/27/23

    Etc..

    Please tell me there's a method that isn't manual?

    Thanks in advance!

      1. Alex!

        I can't thank you enough! Not only for the answer, but for the resource so I can learn this function more on my own.

        A debt of gratitude to you :-)

        1. On a side note... as soon as you have Mac versions of Ablebits, I will gladly download and pay :-)

  6. I am looking for a way to autofill a cell with a date two weeks in advance of current date when the cell before it has an input of 'Yes'.

      1. I'm a little new on this, I'm trying to find or get a formula for inventory, Sheet1 (Inventory) and sheet2 (weekly Inventory)
        I have the date setup on sheet1 to change by Date
        Here come the problem
        I will like for counts or inventory to pass from inventory sheet , to Sheet2 by date

        Example: on Friday, 100 marbles and 50 table cloth was sold, it is on B5 and B6 on Sheet1
        on Saturday 10 marbles and 10 table cloth, on sheet2 (Weekly) I will like that to be on Friday on E5 and E6
        and Saturday on H5 and H6 so I can Sum the week
        keep in mind that I will remove the Inventory from sheet1 to reuse it the following day
        but don't want the info on Shee2 to change.
        Not sure if I'm explaining correctly, but I need some help on this.

          1. Thanks, for the fast responds
            I think I use the wrong term, and did not explain my self correctly, not data but the information (Numbers) of sales
            I re-wrote the question to try to explain it a little better. Thanks in advance.

            Example:
            On Friday, I sold 100 marbles and 50 table cloth, - It is on B5 and B6 on Sheet1
            On Saturday I sold, 10 marbles and 10 table cloth,
            On sheet2 (Weekly) I will like that to be on Column Friday on E5 and E6 and on Column Saturday it's on H5 and H6,
            so I can Sum the entire week
            keep in mind that I will use Sheet1 Inventory on the daily bases, so I will change the amount Sold
            but don't want the info on Shee2 to change, since the date is now Saturday and Friday sheet should not be affected by Saturday sales.

            Not sure if I'm explaining correctly, but I need some help on this.

            1. Hi!
              You can solve the problem using formulas if you replace the formulas with their values. You can try writing a macro that copies your data to different cells on Sheet2 depending on the day of the week.

            2. I think I explain it a little better here, !Sorry

              I'm a little new on this, I'm trying to find or get a formula for inventory, Sheet1 (Inventory) and sheet2 (weekly Inventory)
              I have the date setup on sheet1 to change by Date
              Here come the problem
              I will like to have the inventory to pass from Sheet1 , to Sheet2 by date.

              Example:
              On Friday, I sold 100 marbles and 50 table cloth, - It is on B5 and B6 on Sheet1
              On Saturday I sold, 10 marbles and 10 table cloth,
              I will like that information to be on Column Friday on sheet2 on E5 and E6 and on Column Saturday it's on H5 and H6,
              so I can Sum the entire week
              keep in mind that I will use Sheet1 Inventory on the daily bases, so I will change the amount Sold at the end of the day
              but don't want the info on Shee2, Column E to change, since the date is now Saturday, Column H, and Friday Column should not be affected by Saturday sales because the date has change to Saturday,
              Column H, is Saturday Column.

  7. Hi,

    I'm trying to create an excel template that will update dates based on when I create the sheet to be the previous 2 Mondays. (this is to create a bi weekly report)

  8. Hi, I have worked out the formula I need but I am unable to get the data to auto-populate when I type into one of the columns.

    My formula works is in 2 columns, the first one works out a date 3 months on, the 2nd column works out a date 6 months on.

    Example
    Column B 1st Column 2nd Column
    02.12.2022 (date 3 months on from B2 is calculated) (date 6 months on from B2 is calculated)

    When I type a new date into column B, I would like the dates to automatically populate in the 1st and 2nd columns. The formula works fine but I can't get it to automatically do it when I type a new date under the last one in B. I have checked all the obvious things in options and calculation being set to automatic.

    I hope you can help.

  9. Anyone can help me how to apply in excel like date of expiration in contract and remind me the date in the same day occur.

  10. I have a contact sheet with websites & passwords I would like to add a "last updated" column to it. How do I have excel automatically updated the date in that column whenever anything is changed on the row for the contact?

      1. Got it, Thank you Alexander Trifuntov (Ablebits Team) for you help!

  11. I'm creating a document that needs to show due dates of assignments, and would like it to auto fill based on how many days the assignment is due. Example: I will have a place for today's date, below is a list of assignments that range from 3 days to 10 months that are due, and I would like it to auto fill when I put todays date in when each assignment is due. Can anyone help?

      1. Hood day Hope you can help me?

        i would like to calculate interest from selected dated - that I can do BUT
        i would like to have a description Colum I used the following "formula" Colum B has the dates
        =CONCAT("Interest on outstanding capital from ",B51," to ",B52)

        but i get this as a result Interest on outstanding capital from 42527 to 42571

        i have colum B as a date Culum - Short date

        i hope im clear with my query

        Thank you in advance

        Awie

  12. Dear all,
    I don't know Excel have function as below or not:
    1. Production plan at cell: A1:A1= 24Boxes ( When 8:00AM)
    2. Would like to set up function at B2:B2 (need to setup function to capture data from A1:A1 when 9:00AM)
    3. After 5:00PM Update Actual output=22Boxes into A1:A1, But no need Data of B2:B2 change(Still need 24boxes)
    *** The question: How can i do?

    1. Hi!
      You can use the IF function to update the value of a cell after 09:00 or some other time. Try this formula:

      =IF(NOW()>TIME(9,0,0),A1,"")

      If this is not what you wanted, please describe the problem in more detail.

  13. my date appears as 1/0/1900. what am i doing wrong?

  14. Hey,
    I am trying to find a way of getting the excel to return the date when a specific field in a table is set to 'yes'. So whenever I set 'J' cell to yes I need the spreadsheet to show the day it was set to yes in another column.
    Below is the formula I came across but it is not exactly what I expected.

    =IF(J4="yes",TEXT(TODAY(),"mm/dd/yyyy"),"") function kind of works but my issue is that I need the excel to show the exact date when 'J' cells are set to yes.

    Any feedback would be highly appreciated.
    Thank you

  15. Hello, back in October 2021 you wrote a formula for me to convert this type of date stamp : Jan 10, 2022 07:02 AM to 10/01/22. This worked well but did not work where the date was a single digit eg. Jan 4, 2022 04:50 PM. You gave me the updated formula but unfortunately it has been over written. Can you re-cap how to amend this formula to cater for single digit dates?

    =DATE(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",2),1)+1,4), VLOOKUP(LEFT(A2,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A2,FIND(" ",A2,1)+1,2))

    Thank you in advance, Laura

      1. That's great thank you so much

  16. how can I automatically get 2nd December by typing 2/12 (dd/mm) instead of 12/2 (mm/dd)?
    Thanks

  17. How do I generate a series of dates in a column between a specific range that only provides Tuesday and Thursday or Monday and Wednesday? Thanks.

    1. Hi!
      Write the date in cell A1, which is Monday. In cell A2, write down the formula

      =IF(WEEKDAY(A1,2)=1,A1+2,A1+5)

      Copy the formula down the column.
      You can learn more about WEEKDAY function in Excel in this article on our blog.

  18. My difficulty is if i chose cell from another sheet which have "DATE" formula. And i want to change that date into another date to in the cell in which i have to do the modification. So which formula i should use, So i get direct result. like cell have today date (13/11/2021) and i have to change it into 15/11/2021 in the another cell but by keeping reference of the cell which have todays date.

  19. How to auto populate Monday of the week for next few years.

    1. Choose your first date (a date when day is Monday) and then use auto fill series option with step of 7. It will give you date of next Monday and so on.

  20. Good day. I want to auto update the date in a cell, each time I update my sheet. For instance, the date must update in A1 when I enter a value in B1. Then tomorrow when I enter a value in B2, that day"s date must auto update in A2.

    Please help.

    Kind regards
    Werner

  21. I need to make a auto timer with date and time, if time for the next day it’s not changing in date

    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.

  22. Ok. I am looking for dates to auto populate based on a loan term and how many days left till the end of each month.

    Want the formula to know if the loan term will be 24 months (2 yrs) and or 36 months (3 yrs).
    Would like to create a formula that takes into account todays date, determine how many days left till the end of the month, once the 10 day mark is reached before the end of the month want the dates to auto populate to the next cycle.

    For example using two year term.

    Cell A1 = Loan Payment Date (Example value (06/01/2021)
    Cell B1 = Loan End Date before (Example value (07/01/2023)

    but since todays date is 5/25/2021 want the formula to recognize it is < 10days till end of month and change the Loan Payment Date to 07/01/2021 and the Loan End Date 08/01/2023.

    I know its possible just don't know how to create the formula.

    1. Hi!
      An Excel formula can change the value only in the cell that it is pasted in. If you need to change the content on the cell that has some value in it, you’ll need to use a VBA macro.

      1. There is a way to do it without using a macro. I guess you are not that advanced in Excel as you would like to think you are.

  23. Sir, i am using =IF(B2"", IF(C2="" ,NOW(), C2), "") this formula and having a problem for last few days.

    Problem is:
    When I go into the spreadsheet, every day take the current date for my last few entries .

    I m using google spreadsheet.

    could you pls give me solution

    1. Hello Noman,

      NOW in Google Sheets is a volatile function meaning it recalculates itself each time any change is made in a spreadsheet. There are 2 ways to change this behavior:

      1. Open the spreadsheet, go to File > Spreadsheet settings > Calculation and pick one of three existing settings for Recalculation.
      2. Enter static dates and time manually or using the following keyboard shortcuts:
        • Ctrl+; for date
        • Ctrl+Shift+: for time

  24. i use this but its not working
    =IF(B1="yes",IF(C1="yes",NOW(),""),FALSE)

    apple yes 0/1/1900
    banana yes 0/1/1900
    grape yes 0/1/1900

    1. Hello!
      If I understand correctly, you want to replace the value in C1 with the current date using the formula. We have said many times on our blog that this is only possible with a VBA macro.

  25. i want your help please...
    i want to enter the specific month and year on A3, and i want to automatic fill A7 with the next month of A3, how will i do it?
    Thanks

  26. Hi,
    i want to make one column text eg:Active or Expired according to current date in another column.
    is there any formula or format?

    1. Hello Mubashir!
      Please use the following formula

      =IF(TODAY()>A1,"Expired","Active")

      If there is anything else I can help you with, please let me know.

  27. Hello there, I am wanting to auto generate the current date and time "NOW" function every time I enter data into another cell.
    Example: If I enter data into cell A3 I would like it to automatically generate "Now" function into cell B3 as I continue to enter data in the A column.
    Did I miss something that you already covered?

    1. Any update on this?

  28. Hello, I am creating an attendance sheet. I need help auto populating days of the week in one area of the form (days area) and months and year (in month and year area) for future months. I wish to create the form in advance so I would need the future month. Can anyone help :)

    1. Hello,
      have you created one??

      if yes please share the process

  29. If I give the date in Excel sheet the day has to be updated accordingly. Can you guys please let me know the formula.

    Example: if I enter 2/27/18 then Tuesday has to be updated.

    1. Hello, Yash,

      If your task is to get a day of the week from your date 2/27/18, please enter one of the following formulas in the adjacent cell:

      =TEXT(A1, "ddd")

      =TEXT(A1,"dddd")

      Where A1 is the cell that contains your date. The first formula displays the short form of the day, i.e. Tue, and the second formula returns the full day name, i.e. Tuesday.
      Please also note that the day names you'll get after applying the above formulas will have the text format.

      Hope this is what you need.

  30. By the way, great site.
    In Excel, I have the date in one cell (05/09/2017) and in another cell I have the time (01:43:00 PM).
    How can I combine both these to show, for example:
    05/09/2017 01:43:00 PM
    in the one cell so I can do date and time arithmetic?
    I have set up a custom format (ddd, mmmm dd, yyyy hh:mm:ss) to display the result already.
    Many thanks.....

    1. Hello, Greg,

      supposing that A1 contains date and B1 - time, use the following formula:
      =VALUE(A1)+VALUE(B1)

      If you need to combine them with the format, please try the following:
      =TEXT(VALUE(A1)+VALUE(B1),"ddd, mmmm dd, yyyy hh:mm:ss")

  31. time should change automatically in excel. Any formula for that Action.

    1. =now()

  32. I write a date in a column and want to automatic update of date when I open the sheet in next days. how can I do this.

    1. Hi, vinod,
      if you want the document to show you "today's date" (e.g. the date of your working with the file), try using the following formula:
      =TODAY()

  33. I generate reports on a daily basis, in cell AH60 I have =NOW() In Cell I56 i have =AH60 formatted to read yymmdd. What I want to be able to do is have it read yymmdd-### (ex.170331-001) with the number of my choosing to generate a report number. how can i add it. If tried to make it in the custom area but it keeps telling me its an invalid format...HELP!!! Please

    1. Hi, Daniel,
      unfortunately, custom settings won't help here, try using something like the following in the cell:
      =TEXT(I56,"yymmdd")&"-"&ROW()
      You can change the last parameter to make it return whatever you wish. Hope it will be of use!

  34. How do you put the current (automatically updating) date in the footer of an excel document, not in numbers but in words (example March 24, 2017, instead of 3/24/2017)?

  35. I want to input the date as 30.12.2016, but system does not recognize as date. Please support.

    1. Hi Kalwar,

      For the system to be able to recognize a date, enter it in the default format (one that displays on the Home tab, in the Number Format box, for Short date), and then apply the following custom date format to the cell: dd.mm.yyyy

  36. Hi
    I want to insert only sundays of a year in excel how to do it ?
    Kindly help me

    1. Hello Arham,

      You can use the "Series" auto-fill feature, as explained in "Auto insert every 2nd, 3rd or Nth day":

      - Enter the first Sunday date in the top cell.
      - Select that cell, right-click on the fill handle, drag it down to as many cells as you want, and then release.
      - In the context menu, click Series, and set the Step value to 7, telling Excel to auto fill every 7th day, i.e. only Sundays.

  37. Hello Ms. Cheusheva,

    Thank you for this helpful post. I was wondering is there a way to have cells auto-populated with a calendar date series based on the day of the week?

    Ex: Friday: [07-Oct-16] [14-Oct-16] [21-Oct-16] [28-Oct-16] [04-Nov-16] etc

    Thank you so much!

    1. Hello Tinker,

      You can use the "Series" auto-fill feature, as explained in "Auto insert every 2nd, 3rd or Nth day":
      - Enter the first Friday date in the top cell.
      - Select that cell, right-click on the fill handle, drag it down to as many cells as you want, and then release.
      - In the context menu, click Series, and set the Step value to 7, telling Excel to auto fill every 7th day. Done.

  38. Can I make an entry in one like cell A and excel automatically gives me the date and time I made that entry in cell B??

    1. I know this post is old, but if anyone else is trying to solve this problem, here is the solution that worked for me:

      In the cell you want it to show

      =IF(ISBLANK(A1),"",NOW())

      With the formatting set for date and time under Format Cells.

      1. THANK YOU SO MUCH

      2. works like a charm,
        thanks ronny

  39. I have quarry Ex: If have entered a date in A3 as 01-01-16, and i would like to know the correct date of 6th Month in B3, like showing as 01-06-16. can u help me with the formula

    1. Hello Munwar,

      You can use the following formula:
      =DATE(YEAR(A3), MONTH(A3), 6)

  40. I have a problem please give me a guide how to solve.

    i want update every day automatically pending days anyone help me

    1. =(TODAY()-B2)/365

  41. Hey Svetlana,

    Look i'm using this circular formula which you have prescribed above for the conditonal timestamp =IF(D6="yes", IF(E6="",NOW(),E6), "").
    Now my concern is, instead of putting 'Yes' or any specific word, i want it to give result when i put anything (it could be any number, text or anything i type in coloumn).
    Please advise.
    Thanks!

    1. Hi Bharat,

      In your formula, just replace "yes" with a non-empty cell condition, like this:

      =IF(D6<>"", IF(E6="",NOW(),E6), "")

  42. Morning,

    How do I add date and time eg 6/20/2016 6:00:00 AM and if I drag it below it must be 6/20/2016 6:10:00 AM until where I want it to end.

    I hope it makes sense

    1. Try =Now()

  43. I WANT TO INSERT DATE IN EXCEL CELL AND IT SHOULD BE UPDATED AUTOMATICALLY BECAUSE EVERYDAY I NEED TO TAKE PRINT OUT OF THE SHEET WITH CURRENT DATE. EVERY DAY I AM INSERTING CURRENT DATE. IF ANY FORMULA IS THERE TO UPDATE DATE & TIME AUTOMATICALLY.

    1. Hi you can simply use =Today()

  44. So, maybe I am just not as knowledgeable as I thought...

    I am putting together a database, I manually enter info into columns A - G. I want Column H to automatically fill in today's date, the date I entered the info into the other columns without typing it out, or typing =TODAY() every single time. It's pretty random, some days, I have to add 30 or 40 entries to my database, other days its just 5 or 10 entries. I just want to be able to sort it from newest to oldest... It would save me a TON of time. Can you help?

    1. Basically, same question as Leo in Post 77... did this thread die? I don't see any answers...

      1. Hello Jerrod,

        If my understanding is correct, you want to add a time stamp to some cell as soon as any data is entered in another cell in the same row. If so, you will find the solution in this section: How to insert today's date & current time as unchangeable time stamp

        The above example explains how to add a timestamp to column C if a corresponding cell in column B contains "yes". If that can be any other text or number, then modify the formula in this way:

        =IF(B2<>"", IF(C2="" , NOW(), C2), "")

  45. The above formula looks great (not any comment but the page) I need this for a number value I replaced yes with and I am not getting anything. I want the date when a number value is entered

    1. after with *

  46. Hello,
    I am creating a game library excell file. So i should be able to enter in a cell today's date, and in the next, today's date + 14 days later. So that it is clear when the item was borrowed, and when it is due.
    i tried the formula =TODAY()+14 but is is not working..

    Thanks!!
    melanie

    1. Hello Melanie,

      =TODAY()+14 is the right formula for this task. How exactly is it not working on your sheet? An error? Wrong result?

  47. how can i populate date in a cell where a certain character or number is entered.
    like
    i have a time sheet
    Employee Number Pay Code Hours Work Date
    176 100 9 17/02/2016
    in next day when i enter pay code that days date should appear automatically.
    any way plz suggest.

  48. Hi Team,
    I have need help to lock only one row/column in excel sheet
    pls.suggest.

    1. Hi Hari,

      If you need to freeze a row or column so that you can always see its content as you scroll back and forth in the sheet, check out the following tutorial: How to freeze panes in Excel to lock rows and columns.

      If you need to lock a row or column to prevent their contents from deleting or editing, do the following:
      - Unlock all cells on the sheet: press Ctrl+A to select all cells, open the Format Cells dialog > Protection tab > uncheck the Locked box).
      - Select the row or column you want to lock.
      - Open the Format Cells dialog again and check the Locked box. This will lock the selected row or column only.
      - Protect the sheet to enforce this setting (Review tab > Changes group > Protect Sheet).

  49. Hi Team,

    Just wondering if there is a way when entering a date for e.g. in Cell A1 31/01/2016 in Cell A2 Sunday will added.

    1. Don't Worry found it.

      =TEXT(A1,"dddd")

  50. i copy your formula =IF(B2="yes", IF(C2="" ,NOW(), C2), "")and paste it in c3 but it did not work? so wrong did i do?

    1. Hi Sam,

      You should paste this formula in C2, and then copy the formula cell down so that the cell references get adjusted for row 3. If you want it for row 3 only, then change the references accordingly:

      =IF(B3="yes", IF(C3="" ,NOW(), C3), "")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)