SOLVED

Entering a date on multiple sheets automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-1510108%22%20slang%3D%22en-US%22%3EEntering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510108%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20put%20in%20a%20formula%20to%20enter%20dates%20automatically%20on%20my%20sheets.%20For%20example%3A%20I%20have%2031%20sheets%20for%20the%20month%20of%20July.%20I%20want%20to%20have%20the%20date%20on%20each%20sheet%20in%20the%20same%20cell.%20Is%20there%20a%20way%20to%20do%20this%20without%20having%20to%20enter%20it%20manually%20on%20each%20sheet%3F%20I'm%20using%20excel%202016.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1510108%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510174%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722776%22%20target%3D%22_blank%22%3E%40vtyree%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20mean%20the%20same%20date%20for%20all%20sheets%2C%20you%20may%20select%20all%20shift%20(click%20on%20first%2C%20Shift%2C%20click%20on%20last)%20and%20enter%20the%20date%20into%20the%20cell.%20Unselect%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510180%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510180%22%20slang%3D%22en-US%22%3EIf%20you%20highlight%20all%2031%20sheets%20and%20then%20enter%20the%20date%20in%20the%20cell%20you%20want%2C%20it%20will%20get%20entered%20in%20that%20same%20cell%20on%20all%2031%20sheets.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510191%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722776%22%20target%3D%22_blank%22%3E%40vtyree%3C%2FA%3E%26nbsp%3B%2C%20you%20can%20extract%20the%20day%20from%20the%20sheet%20name%20using%20this%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMID(CELL(%22filename%22)%2CSEARCH(%22%5D%22%2CCELL(%22filename%22))%2B1%2C255)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20use%20the%20Date%20function%20to%20get%20the%20whole%20date%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATE(2020%2C7%2CMID(CELL(%22filename%22)%2CSEARCH(%22%5D%22%2CCELL(%22filename%22))%2B1%2C255))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510247%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510247%22%20slang%3D%22en-US%22%3E%3CP%3EAh%20says%20the%20man%20when%20you%20open%20his%20eyes.%26nbsp%3B%20I%20didn't%20catch%20the%2031%20sheets%20for%20July%20as%20in%2031%20days%20in%20July.%26nbsp%3B%20Good%20catch%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B.%26nbsp%3B%20That%20solution%20is%20good%20IF%20the%20sheets%20are%20named%201%2C%202%2C%203%2C%20etc...%3C%2FP%3E%3CP%3EIf%20not%20you%20can%20use%20this%20formula%20in%20the%20Name%20Manager%20to%20define%20a%20Name%20like%20%22PrevSheet%22%20as%20a%20reference%20to%20the%20same%20cell%20in%20the%20previous%20sheet%3A%3C%2FP%3E%3CP%3E%3DINDIRECT(INDEX(GET.WORKBOOK(1)%2CSHEET()-1)%26amp%3B%22'!R%22%26amp%3BROW()%26amp%3B%22C%22%26amp%3BCOLUMN()%2CFALSE)%3C%2FP%3E%3CP%3Ethen%3A%3C%2FP%3E%3COL%3E%3CLI%3Ein%20sheet%201%20type%201%3C%2FLI%3E%3CLI%3Ehighlight%20sheets%202-31%3C%2FLI%3E%3CLI%3Etype%20in%20%3DPrevSheet%2B1%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510275%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722776%22%20target%3D%22_blank%22%3E%40vtyree%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20simply%20select%20sheets%202-...%20and%20enter%20into%20the%20cell%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATE(2020%2C7%2CSHEET()-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515044%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515044%22%20slang%3D%22en-US%22%3E%3CP%3EI%20mean%2C%20a%20different%20date%20on%20each%20sheet...for%20example%207%2F1%2F20%2C%207%2F2%2F20%20and%20so%20on.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515047%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515047%22%20slang%3D%22en-US%22%3E%3CP%3EOMG%20thank%20you!!!%20It%20worked!!!!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516536%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20date%20on%20multiple%20sheets%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722776%22%20target%3D%22_blank%22%3E%40vtyree%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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. 

8 Replies
Highlighted

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

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

@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))
Highlighted

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

 

Highlighted
Best Response confirmed by vtyree (New Contributor)
Solution

@vtyree 

When simply select sheets 2-... and enter into the cell

=DATE(2020,7,SHEET()-1)
Highlighted

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

OMG thank you!!! It worked!!!! @Sergei Baklan 

Highlighted

@vtyree , you are welcome