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 11. Total comments: 442

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

  2. can you help me to make below 4 letters without repeat codes.
    is there a way to fix repeat codes automatically.
    what i need to do make 170,000 random codes using A-Z with out any repeat codes.

    HTER
    NRLE
    HCIC
    HELI
    HTER
    SAVO
    TSRE
    HVCT
    HTER
    HCOT
    BART
    HMOR
    COPA
    HNAZ
    HDIN
    VALL
    HINT
    BART
    ARTE
    GRPL
    HORE
    HCLB
    HSEL
    BART
    HCOS
    HTIZ

  3. Hello ! Every day the value of A1 are changing, B1 is the dates of the month, while C1, where I want to record the changes:

    01 Jun 789
    02 Jun 465
    03 Jun xxx

    So how to automatically input the change on the next date?

    Thank you in advance Slava

  4. There is only one trick copy & paste that i know apart from that if any trick you know pls explain me...

  5. Hello Svetlana,
    Is there any trick to copy Date and Time from somewhere to excel sheet. I have email & i want to copy of a perticular email date & time in excel.Means I just want to get that if a person opened email(that i sent him) in which time & date he opend email that time and date i want to copy in excel.for this is there any trick. pls reply...

  6. I have a debt repayment schedule that has a start date of April 17, 2016. I want to have the payment schedule to change automatically with whatever date is put into the start date.
    ie. April 17, 2016 May 17, 2016 June 17, 2016
    then when changing the start date to say April 25, 2016 it all updates accordingly
    ie. April 25, 2016 May 25, 2016 June 25, 2016

  7. i have to prepare a assingment that we have 365 days of year we have to make payment to vendor on daily basis except sunday how to calculate that thing in the excel is it possible

  8. 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), "")

  9. how to change "yes" to range of numbers? for examnple >100.. thank you

  10. Greetings! I am at the end of my wits to autofill two dates as in
    4/4
    4/7
    4/11
    4/14
    4/18
    etc.
    How?!
    THANK YOU.
    Andrew

  11. I read the article and found it very helpful. I kinda understand why the circular references if risky. so I tested an other formula to insert timestamp namely =IF(B2"",NOW(),"") and it seems to work just fine

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

  13. Hello,

    I am wondering how to make it so that I can just enter the number of the day in the date column, and have the year and month auto populate with it. For example, just to enter the number 14, then have it fill with March 14th 2016.

    Thank you!

  14. I want excel to add automatically the date and time of an entry as soon as I fill one field in a row. Every time I enter a date to a row the Excel has to add the date and time of that entry in that row withour changing other dates and time in other rows. How to do that?

  15. Hi...
    I wanted to auto populate date series as
    01-03-16
    01-03-16
    02-03-16
    02-03-16
    How to do it...?

  16. I input the date of an invoice in an spreadsheet. Exactly two weeks from the date on the invoice, the invoice is due. Is there a formula so this date is calculated automatically, once the original invoice date is input in the spreadsheet ?

  17. I use excel to do weekly reporting for my self employed income tracking.
    I have 1 workbook that I use, and sheets in the workbook ending on Saturdays.
    Is there a way that I can write the formula to paste across all 52 weeks, to be able to auto-populate the cell for the week ending date?

  18. Hi,

    I put in my specific dates (that are random) to graph them and when I click the graphing tool, it changes the dates. Instead of having dates: 2/28, 2/30, 3/2, 3/14, the graph changes them to all be two days apart (2/28, 2/30, 3/1, 3/3 etc.). How do I change the graph to only contain the dates I specify?

    Thank you.

  19. Hi Team,

    Looking for a way wherein hidden rows having dates automatically unhides on TODAY()-1 basis.

    Thanks!

    Sanjeev

  20. I usually have to fill in 3 cells each day
    1. Today's date
    2. Yesterday's date
    3. The day before yesterday's date
    And I need to use this file everyday. How can I set Excel so that every time I open the file these 3 cells are auto-filled?

    Thank you

  21. Thank you very much and its solved my problem

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

  23. Is there a way to create a formula to provide a number of days that have elapsed from a particular date based on today's date?

    For example, in Cell "A1" I have a date (10 FEB 15).
    In Cell A2, I have a formula that calculates how many days have elapsed since "A1's" date and today's date.

    I imagine it would look like =NOW()+A1. However everytime I do that, I get a weird number.

    Thanks.

  24. Does anybody know how to insert time by using dropdown box?

  25. Hi, forget about it, I missused the circular formula, it actually worked... regards!

  26. Hi Svetlana, first of all thanks for the tutorial, it's explained clearly.

    Just one thing (and sorry if it has already been asked), Is there a way (without using VBA) to "replace" a formula with it's result?, I have a an online excel spreadsheet where some people work on it, and I want to automatically insert date and time as soon as they start typing the info (of course, the time cell will be protected).

    I hope you can help me, my best regards.

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

  28. It is really useful information.
    Thank you Svetlana Cheusheva for resolving all queries.
    With that only I completed my difficult task.

  29. Dear Svetlana,
    Thank you very much for all your excellent comments and professional advises.
    I use Excel 2003 with a spanish keyboard and in order to insert today's date and current time it is a bit different as explained above. Here is how it works:
    Ctrl + Shift + ; inserts today's date
    Ctrl + Shift + : inserts the current time
    Using the semicolon (;)gives the "date" and the colon (:) the "time".
    Maybe not many use Excel 2003 nowadays but I thought to add something to your wonderful post and be able to greet you personally.
    Please confirm

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

  31. Hello, could you please help me to do this.
    [Item] [Received] [Date] [Time] [Send check on (Calendar)
    Nike-CT Yes ? ? ?

    by clicking yes date should automaticly appears on c2 and time on d2. and calendar appears on e2 to choose a day to send a check.

    Thank you

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

  33. 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), "")

  34. Dear Svetlana
    How to insert a date picker content control in excel sheet like word.
    can you help me with this.
    kind regards
    Eyad

  35. Hello,

    I just want to clarify logical(if) options, if a1=khan, b1=30, what is the formula of c1(b1>30, true means the actual cell of al(=a1), false means plain or dot anything. How to get the answer.
    Please Help!

  36. How can i make one cell with all dates is there with auto fill?

    1. Hello, Michelle,

      For us to be able to assist you better, please describe your task in more detail.

  37. Hello,

    What I'm trying to do is get the current date to appear on each new page automatically....

    So when one fills the current excel sheet and it starts on the next one, I would like the date to pop up in the same spot on the new sheet automatically.

    I also have a current header that I would like to fill with a colour but not sure if I can do that in 2013 and if so...how does one do that??? ( I currently use a .psd image to fill it with the logo on the .psd file but would like to just add the logo in and fill the header with colour)

    Please help :(

    1. Hello, Lindsay,

      Sorry, we don't know a formula that could do this. Most likely a VBA macro is needed in this case.

  38. I am trying to format excel spreadsheet to do this:
    date entered in A1 then B1 auto populates the date 7 days later.
    It should look like this, A1= 1/11/16, B1 should auto populate 7 days including the date entered so it should show 1/17/16 to reflect the 7 days including the start date. What formula can I use for this.

    1. Hello, Bryan,

      Please enter your initial date to A1 and the result to B1. Select these two cells and use the fill handle to auto-populate the column. Excel will automatically apply the correct increment.

  39. hi
    I would like to know is there any formula in where in a cell I type todays date in it and adjacent cell auto fill 5 working day ahead of that date for example If I put 04/01/2016 in A1. in B1 autofill 08/01/2016 which is the 5th working day.
    any formula or any advice please

    1. Hello, Faisal,

      Please enter your initial date to A1. Enter this formula to B1 and copy it across the column:
      =WORKDAY(A1,1)

  40. Hi,
    I do the book keeping for the clinic I work at and I need Excel to populate 1st, 2nd, 3rd, etc. in a row (consecutively). Is there a way I can do that? I have Microsoft Excel 2010 and Windows 7. Any help is appreciated.

  41. Hi, is there a way to use the NOW function to update ONLY if there has been a change to the worksheet not on opening it? Right now the date pops to todays date on open. These are individual sheets within a workbook that each one needs its own "last changed date".
    Thanks!

    1. Hi Tracey,

      The NOW function updates its value every time the workbook is reopened or recalculated, and there is no way to make it work differently. Your task can be accomplished by using VBA - you input a certain value to a certain cell and specify when (on which event) it shall be updated.

  42. For All people wanting to change a field and have a date/Time stamp that will not change here is an example of a Macro that can achieve this.

    'This sub will activate if there is a change anywhere on the sheet.
    'it must be put in the VBA Projects under the sheet you want to watch.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range

    'This sets the range of cells you want to actually look at to see if the value in these cells have changed.
    'this allows you to watch certain fields.
    Set WatchRange = Range("E2:F10000")
    Set IntersectRange = Intersect(Target, WatchRange)

    On Error GoTo err_chk
    If Not (IntersectRange Is Nothing) Then

    'DO SOME CODE HERE THAT YOU WANT TO RUN WHEN A CHANGE HAPPENS.
    'THIS COULD BE ACTUAL CODE HERE OR A REFERENCE TO ANOTHER SUBROUTINE.
    'EXAMPLE: 1 ACCESSING ANOTHER SUBROUTINE
    'Copy_With_AutoFilter2
    'EXAMPLE: 2 INSERTING CODE.

    ' Dim DATETEXT As String
    ' 'Now() is the function to return the date and time. if you want just the date use Today()
    'DATETEXT = Now()
    ''this gets the active workbook and selects the sheet by the Name of the sheet(Main) and put a range cell in where you want the Datetext placed.
    'ActiveWorkbook.Sheets("Main").Range("C2").Select
    ' ActiveCell.FormulaR1C1 = DATETEXT

    End If

    On Error GoTo 0

    Exit Sub

    ' Error Handling
    err_chk:
    If Err.Number = 13 Then
    Err.Clear
    Exit Sub
    Else
    MsgBox Err.Number & vbCrLf & Err.Description
    End If

    End Sub

  43. Hi all ,
    I would like to ask for your help.
    I have an Excel worksheet to manage products license .
    I have a column that shows number of days that
    License product will end.
    e.g : 55 Days

    I have another column that shows fixed date
    e.g 06/12/2015

    I would like to achieve two formulas.
    - For fixed date column :
    Automatic update of the date every 24 hours

    - On license expiration days:
    Decreasing of the number automatically every24 hours by 1

    Thanks ,
    Rafael

  44. Hi,
    I need your help!
    How can I enter many values linked to one date. For example, I have a date 1/12/2015 in row A1. Data on B1,B2,B3, B1=1000,B2=2000,B3=3000. I want date on A1 represent all values B1:B3. I want to sum up all values.

  45. hi,
    i want calculate two working and if the second column do not fill in. There system will use the today date to deduct with the date i fill it. How should i set the formula ?

  46. Hi,
    I want to prepare a sheet for issuing call letter daily 800 personnel. There are 50000 personnel for such sheet. How i can auto fill date

  47. Hi,

    Your answers are very useful to us. Really Thanks for that.

    I have one sceniro i.e., I have 3-4 columns in the excel. if i change or if i update one column, is there any option that get automatically get the Todays date in a Report date Column.

    If you have VB Script if you have any..!

    Thanks inadvance.

  48. Hi

    I want to create a worksheet to do the date calculation based on the priority.

    Example.

    A column labeled as Priority -- Valid Values are High, Low and Medium.

    B Column labeled as Item requested date

    C column labeled as Target Delivery date and Date has be to updated automatically based on the A and B values.

    if the A2 value is High and B2 value is today's date.. C2 value has to be updated as 7 days after B2 value.

    Can we able to accomplish this in excel sheet.

  49. How to stop today() from updating date on saved columns or rows?

  50. Hi Svetlana!
    I daily put some numbers in 3 to 4 consecutive rows in a column. All I want from excel is to have the record which day I put a particular number in that column, and then add the values I entered in a day.
    Is there any formula in excel for that?
    Kindly help even if it is possible with only VBA.

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