Forum Discussion

vtyree's avatar
vtyree
Copper Contributor
Jul 08, 2020
Solved

Entering a date on multiple sheets automatically

I'm trying to put in a formula to enter dates automatically on my sheets. For example: I have 31 sheets for the month of July. I want to have the date on each sheet in the same cell. Is there a way to do this without having to enter it manually on each sheet? I'm using excel 2016. 

excel
    • jbalderas's avatar
      jbalderas
      Copper Contributor
      Dec 05, 2023
      where do you enter that? and i need it to be dated format like below 7/1/24,7/2/24. etc. but I need it dated not just 1,2,3, for dates.
      • vtyree's avatar
        vtyree
        Copper Contributor
        Dec 05, 2023

        jbalderas please see attached. Enter it in the box for the date you wish to use. Change the number to what ever month you are using. 

         

  • TheAntony's avatar
    TheAntony
    Iron Contributor
    Jul 08, 2020

    vtyree , you can extract the day from the sheet name using this formula:

    =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255)

     

    Then use the Date function to get the whole date:

    =DATE(2020,7,MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255))
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 08, 2020

      Ah says the man when you open his eyes.  I didn't catch the 31 sheets for July as in 31 days in July.  Good catch TheAntony .  That solution is good IF the sheets are named 1, 2, 3, etc...

      If not you can use this formula in the Name Manager to define a Name like "PrevSheet" as a reference to the same cell in the previous sheet:

      =INDIRECT(INDEX(GET.WORKBOOK(1),SHEET()-1)&"'!R"&ROW()&"C"&COLUMN(),FALSE)

      then:

      1. in sheet 1 type 1
      2. highlight sheets 2-31
      3. type in =PrevSheet+1

       

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 08, 2020
    If you highlight all 31 sheets and then enter the date in the cell you want, it will get entered in that same cell on all 31 sheets.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 08, 2020

    vtyree 

    If you mean the same date for all sheets, you may select all shift (click on first, Shift, click on last) and enter the date into the cell. Unselect sheets.

    • vtyree's avatar
      vtyree
      Copper Contributor
      Jul 10, 2020

      I mean, a different date on each sheet...for example 7/1/20, 7/2/20 and so on. SergeiBaklan 

Resources