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

  1. Hello possible to enter a number auto copy paste into Excel from the site in
    Different time

  2. Hi I need a date selection list like calender to select the date where we can select any date & search the date. Is that feature is available.

  3. Hi, Svetlana.. Thank you so much. This really helps me a lot !

  4. What formula to use if you wants to know age calculation.
    E.g. born on 15th May 2010 and I want to auto calculate age to the date of 31st Dec 2020 ?
    Please help. Thank you

  5. how to in cress date automatically. example - A1 -TODAY , in B1- i need next day date

  6. This is exactly what I was looking for.

    Thank you!

  7. Can you explain the formula for getting the years between two days?
    Example: The number of years between today and Dec 1-1960
    Thanks for your help

  8. Hi,

    I've tried auto-filling a column of dates, but it increases the year for each cell (i.e. 5/12/16 -- 5/12/17 -- 5/12/18) instead by day. On the 'autofill options' drop down list there aren't the options for fill days, weekdays, months etc.

    Any advice? Thank you

  9. I have tax invoice template set in Excel, is there a way that the date or print out date is auto generated?

  10. Hi, I would like to know how to make a cell automatically show the next July 1st from another's cell's date shown. In other words, I have a due date that is always July 1st, so I want the formula to automatically display the next July 1st from the date I input in the preceding cell.

    So if A1 says (01/01/2016), I want A2 to automatically display (07/01/2016), but if B1 says (07/02/2016), then B2 should display (07/01/2017).

  11. My receiving group documents daily incoming materials on spreadsheet. They have to write the date for everything. Is there a formula that can auto populate the date once they enter data on that row. I tried =if(C1=",NOW()*C1). but this did not work?

  12. hi madam

    we unable to get in formulated cell , what can we do for getting date

  13. Hi Ms. Svetlana Cheusheva,

    I have a set of data on sheet 1 and I have a search box made for the data in sheet 1 I have created the below Macro but I can't get it to run on my workbook
    I would like the macro to start searching in sheet1 in row 4 for a chemical name in column C or product code in column D
    and if the term in Column C or D matches I want it to copy the whole row to sheet 2

    please advise
    Thanks

    'If value in search_box = Matches anything in column C or D, copy entire row to Sheet2

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    'Start search in sheet1 row 4
    LSearchRow = 4

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2

    'Select row in Sheet1 to copy
    Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
    Selection.Copy

    'Paste row into Sheet2 in next row
    Sheets("Sheet2").Select
    Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
    ActiveSheet.Paste

    'Move counter to next row
    LCopyToRow = LCopyToRow + 1

    'Go back to Sheet1 to continue searching
    Sheets("Sheet1").Select

    End If
    Next I

    please advise thanks

  14. Hey, Svetlana, lots of thanks for your effort.
    you have gave description how can we make timestamp for interring value or value change in a cell directly. But this formula don't work for whose cell, that's value is change through link.
    Suppose, I have to record timestamp in cell B1 where value of cell A1 is changed. Here, cell A1 is linked with (A1=C1+D1) cell C1 & D1. is this formula works for timestamp in this circumstances. I don't think so:(
    Is there any function available to record timestamp of a linked cell value change?
    Your urgent feedback will be highly appreciable. :)

  15. I have entered date unevenly
    A1=10/5/16
    A2=5/01/2016
    A3=30/6/2016
    How can get them ranging from jan to dec.??

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

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

  18. Hi Ms. Svetlana Cheusheva,

    How can I make the date and time update automatically?
    For example:

    "The P/N ########## was right based on Agile on 10/20/2016 @ 8.00" is in 1 cell.

    The "10/20/2016 @ 8.00" need to be updated based on current date and time.

  19. Hello Ms. Cheusheva,

    Nice work.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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