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

  1. Thank you very much and its solved my problem

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi,
    I would like to know how to input range of dates in an excel formula to become work week.
    Example:
    04/01/15-10/01/15 to be formulated into week 2.

    Date Wk
    04/01/15 2
    11/01/15 3

  32. Hi
    I am probably repeating a question. I write a text in cell A1 and i want that cell B2 automatically insert the date of that specific date in it. For example on 30 Mar 15 wrote XYZ in A1 then B1 should automatically carry/show 30 Mar 15, and this date automatically taken by B1 should remain unchanged (irrespective of how many time i close or re-open the file). I also do not want to use Ctr+;.
    If it is possible through formula (i.e formula in B1) then plz tell me. If it is possible through VBA then plz tell me VBA code as i don't have any familiarity with VBA code.thanks

  33. Let's say that I have two columns. Column A is filled with dates (the order of the dates is a bit strange for certain purposes - for ex. 15.10.2015, 31.10.2015, 15.11.2015, 30.11.2015 and etc.). In column B i would like to get a certain result - range of dates ordered as they are in column A. It's a bit difficult to explain, so I'll illustrate it with an example:

    sample series of data:
    A1 15.10.2015
    A2 31.10.2015
    A3 15.11.2015
    A4 30.11.2015
    A5 15.12.2015
    A6 31.12.2015
    ... ... ... etc.
    two blank cell containing start and end point for the new series of dates, using the old one:
    from date: 15.11.2015 until date: 15.12.2015
    result in column B:
    B1 15.11.2015
    B2 30.11.2015
    B3 15.12.2015
    B4 31.12.2015

    Is there a way to achieve this result? We're basicaly talking about generating a series of cells in a row, using other series?

  34. I would like a formula that when I put a value in one cell (B) that say Cell (D) will populate with the date and when I save the workbook and close it that the next time I open it the dates don't change to the current day.

  35. I have used the Fill Series function to insert a range of dates, WORKDAY in steps of 0.5 for am and pm. I started the series with a cell "=A2-3" where A2 contained the command TODAY(), the intention was to have a series of dates that automatically updated. Only the first cell updates, the series of dates, running horizontally to the right remain static. Is there a way to achieve what I want?

  36. Really good and helpful

  37. I update an excel sheet daily I want when I go through a any cell of a particular column , the current date should automatically be typed there and it should not change the next day... means that when I go any a cell of that particular column the next day, next days date should be appear.

  38. Hi Svetlana
    Thank you for your time.
    I would like to know if how do I calculate an expiry date if it is going to take place in a certain day (eg) after 14 days after the day of quotation.

  39. I enter 9/13/2015 into a cell and when I tab down to the next cell the date changes to 13-Sep. How can I get excel to stop changing this date?

  40. Hi
    I run windows 7 with Excel 2010
    I have a spreadsheet with the using dates in a column. The format is set to dd-mmm. However, when I enter 12/9, I get 09-Dec, but I want 12-Sep. In other words, I want to enter day and then month not month and then day. How do I do this??

  41. Hi,

    I have a spreadsheet that tracks employee hire dates and on every employee's "anniversary" we like to give them a gift card. Is there a way to take the anniversary date and, once it passes, update the cell to reflect the same day and month but the next year? Like an IF then REPLACE type of function?

    Thank you!

  42. Hi,

    I have got a spreadsheet with multiple dates against set of activities in it. Just wanted to know if there is some kind of function in excel wherein I could insert todays date against eache date and check the actual days passed from the date of the activity.

    I know we can use function like Now/Today. But I want these dates to get autorefreshed tomorrow when i am reopening the sheet.

  43. dear sir i am trying to make excel sheet as we have 4 or 5 box formed on one sheet than for the day i entered no in the box which reflect on the excel cell linked. on next day again the date change the data fill to next matched data cell of next sheet.

  44. Hi,

    Ive got a a spreadsheet with all the days of the year in column A, which runs for several years.
    In column "B" I want to input a specific value next to a specific day of the month only, i.e: the 3rd day of each month for the whole of the spreadsheet much reflect the same value.
    This is for instance when showing monthly payments made on the same date each month over a 10 year period.

    Thank you

  45. Hello,

    I am scanning in two different serial numbers into columns A & B, how do I get the corresponding column C to post the "now" automatically?

  46. Hello,
    I am trying to have a column filled with month and year based on a date range entered in 2 other cells. For example 2/1/2015 (in Cell A1) and 10/31/2015 (in Cell B1), I soul de like C1 to C9 to display Feb 2015 in C1, March 2015 in C2,... Oct 2015 in C9.

    Any help will be apprecciated!

  47. hi thanks for your help. I am trying to auto fill in the target date 59 days from start date i am using =cell +59, but is there a way it can be auto filled without typing or draging the formula from cell to cell

  48. Hello! We are trying to create a spreadsheet that shows the date of the Sunday before the date we are entering. For example:

    We enter 8/26/2015 and in the column next to it populates 8/23/2015

    Any thoughts on the formula or filter we can use for the 'Sunday' column?

    Thank you!

  49. Thanks dear. i create a excel sheet which i write daily data but i want any one enter data but don't change my data .is possible .so please tell me about this thanks.

  50. Good day!

    First off, i want to congratulate you for this informative blog. It has helped me a lot in my office work.

    I have tried one of your suggested formulas and it has worked for one of my reports. I am not so well versed in excel, thus I still have some problems creating my own custom formula. Here is my concern:

    I want to create a formula that would automatically update the month, not per day, using this format 1-Jan-15. =TODAY() will generate daily updates, what I would want is monthly, specifically end of the month. Is this possible?

    Thank you in advance for any input you can share.

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