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

  1. Greetings,
    Hi would like to know from You
    I Set date in my excel book and i want a formula that work with date for example
    today 1-1-2017 and my figure is 100
    next day when i open my log date will be 2-1-2017 and figure turn to 200 automatically.
    Please help for this issue

  2. Can I insert the start date of year in cell .. A1 then show me the end date of year in cell A2
    Ex.
    A1=1/1/2017 A2=31/12/2017

  3. Hi All,

    I am currently working a excel formula to input a Revision_Date in column A then it will auto generate the number of days per month(only weekdays) on column B(January), column C(February) and so on depending on the current date. Example if today is April 17, 2017 and my input in Column A is Feb 13, 2017, it will compute number of days from Feb 13 to Feb 28 (which is 12 days) to be generated on Column C(Feb). Then on March which is complete since it covers the whole month until today on Column D(Mar). Then comes to April to compute the days from April 1 to April 16 on Column E(Apr). Can someone help me as I am really not familiar with excel. Hoping someone can share any formulas/techniques on how to do it. Thanks in advance.

    1. Hi, Gerry,

      for us to be able to help you better, we need to see how the columns are named and arranged. Please, send us an example of your workbook with the source data and the result you want to get to support@ablebits.com.

  4. Hi All,

    I am currently working a excel formula to input a Revision_Date in column A then it will auto generate the number of days per month(only weekdays) on column B(January), column C(February) and so on. Can someone help me as I am really not familiar with excel. Hoping someone can share any formulas/techniques on how to do it. Thanks in advance.

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

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

  7. Hi,
    i want to create a form for multiple employee for stock inventory. so i want if any employee enter his emp.code than his last balance auto show in excel form and if he give material request then automatically calculate from his last available balance.

    Can you help me please

  8. Using the + (plus) symbol does not work. (I used the + symbol on my numeric keypad.) Rather, using the = (equal) symbol works fine.

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

  10. Hi,
    I tried your following formula but it returned 0/1/1900 0:00

    =IF(B2="yes", IF(C2="" ,NOW(), C2), "")

    Appreciate if you can point out where the problem could be.

    Thanks and Regards

    1. Hi, Sunny,

      to help you with your task, we need to know what you entered into the cells. You can try to explain here in more details, or send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Don't forget to add the link to this article and your comment.

  11. the same with other comment "I need your help with regards to a simple query and tell me if it is possible.

    I have a data sheet what i need is that if I enter any value in A1 it automatically updates the current time in B2 which doesn't change later on and stays permanent so i should know at what time that entry in A1 was made. Your prompt response will be highly appreciated."

  12. I want to have each cell in a column have a drop down list box that only includes the current time up to 23:39????

  13. Hi to all. I need to put DATE, CURENT TIME in separate columns. Date and time had to be current(from computer clock) BUT unchangeable.

    how to do that?

    thanks

  14. HEY EVERYONE,

    I JUST WANT TO PUT A DATE IN ONE CELL AND WANT THE OTHER 6 OR 7 CELLS SHOW THE PREVIOUS DATES IS IT POSSIBLE IN EXCELL

  15. How to do that if I fill the cell by numbers or touch the cell, next cell should be filled by current date automatically..?

  16. Is there a way that I can see what date I inputted data into a particular cell in a worksheet without having a separate column for a date (e.g to see what when a payment was made)

  17. I need your help with regards to a simple query and tell me if it is possible.

    I have a data sheet what i need is that if I enter any value in A1 it automatically updates the current time in B2 which doesn't change later on and stays permanent so i should know at what time that entry in A1 was made. Your prompt response will be highly appreciated.

  18. could not calculate years entering date at two cells,as the answer is '#value' comment 'with wrong date type'. Kindly help.

  19. Is it possible to automatically add a new column with the current date in front of column "B" everyday at a specific time?

    For example, at 2am PST today column B would become column C and a NEW column would be column B that would auto-populate with today's date (Monday January 30, 2017.)

  20. Hi what is the formula for calculating the number of days from today's date and the date selected..thank you!

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

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

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

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

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

  26. This is exactly what I was looking for.

    Thank you!

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

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

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

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

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

  32. hi madam

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

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

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

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

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

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

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

  39. Hello Ms. Cheusheva,

    Nice work.

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

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

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

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

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

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

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

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

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

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

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

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