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

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

  2. Good morning. I'm using the =IF(B2"",IF(C2="",NOW(), C2),"") formula and it's been working fine for a long while. Now all of a sudden it stopped working even though the formula is unchanged. I just get the circular reference warning. Any ideas on why and how to fix it?

  3. Dear expert,
    I have quarry that i selected two raw but + mark no showing right side of row
    so that i have not drag date, please suggestion for setting ??

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

  5. am trying to create a phone log for a busy office . I need the date and time to auto fill when I take a call to record the callers information and nature of their call . I cannot seem to get it working to autofill. Any help is much appreciated.

  6. Hello,

    I am am working with months, days, and numbers (not dates). I am wondering if there is a formula to recognize all 'Mondays' per month? In this case I will not have to select each cell manaully for a new month.

  7. hi need help in excel formula:
    I want to auto date my column/cell.
    if B1 is 1, Autodate A1.
    I will appreciate your help. Thanks

  8. Please I am trying to use excel as a time sheet for HR. I want to be able to calculate the actual time worked for each day, while deducting break periods. Break time however is deducted based on number of hours worked, e.g for 5hours or less it is 20mins break, 6 to 8 hours it is 25 hours and so on. Please help.

    Thanks

  9. Hi,
    i like to know if i can get a list of day and date in separate columns if i enter a third column with the current month. And every month if I change the month and year, the day and date should also change.

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

  11. I have a problem please give me a guide how to slove.

    I am working in a logistices maintaing 200 vehicle records, in the excel sheet with their documents. there i gave all the due date off all the vehicles,

    I want to know that there should be the highlight when the due comes near to 7days before and when it expires the due date cell should be change totally to red colour.

    (Its should happen totally based on daily date accordinly)

    Kindly please advice me

  12. I have an excel calendar made up-- I have a database with names and birthdates on it--
    I would like the calendar like on jan 21 of the month to pick up the database match and put that name on the date of jan 21 is this possible. the database is on the same sheet as the calendar

  13. Hi

    I have a formulae for date in cell A2 = Today()-Day(Today()), which gives me the last day of previous month whenever I open my spreadsheet. Now in cell B2 I want a first date for current month with static number 01 at the end. Eg.
    A2= 7/31/2016
    B2= 8/1/2016 01
    How to derive B2 date with 01 at the end. Currently for B2 I am using EOMONTH(A2,0) which gives me 8/1/2016 but I am not able to input 01 at the end of the date with any formula, it converts the date in to a number.

    Please help.

  14. i am trying to make an invoice template with an automatic date stamp

  15. Hi, whenever i try to autofill dates it get incremented by years. For e.g i want the columns to have sequential dates, but mine get sequential in years. When i try the autofill option, it doesnt show me choice of fill days or fill work. It just has fill series, fill format etc.

  16. hay..

    i wants to do something in excel , i want to match today date from a column wants to use the value of different column with different row.

  17. Hi, I want to generate a series of dates every 15th of the months and if 15th is Saturday or Sunday, moved to later date. Is there any formula for excel that can handle such conditions? Thanks-Glenn

  18. Your IF formula works freat, but it is not giving current date. Please help

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  39. Hi Team,

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

    Thanks!

    Sanjeev

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

  41. Thank you very much and its solved my problem

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

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

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

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

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

  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. It is really useful information.
    Thank you Svetlana Cheusheva for resolving all queries.
    With that only I completed my difficult task.

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

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

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