how to make dates progress through worksheets in a workbook

Hi,

I am trying to make a workbook where the weekly worksheets dates progress.

ie sheet 1 1/1/2016 to 7/1 2016
sheet 2 8/1/2016 to 14/1/2016
and so on

Do you want those dates in individual cells in Sheet1, Sheet2 etc., or are they shown as ranges in one cell in each sheet?

Pete

Hi Pete,

I need them in individual cells.

thank mark

And will you just be using the default sheet names as you create them, i.e. Sheet1, Sheet2, Sheet3 etc., or do you have a different naming convention in mind?

Pete

I was going to name them by date ie 1 july, 8 july etc

In a new file you should rename the sheets using that convention, and then you need to save the file using an appropriate name, and then you can put this formula in A2 of the first sheet:

=DATEVALUE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)&2016)

Format as a date in the style your prefer, then in A3 you can have this formula:

=A2+1

and copy that down to A8. Then you can copy the block of cells A2:A8 into the other sheets. If you need to add new sheets you can just CTRL-drag one of the other sheets, and then rename it with the appropriate date. Or you could just insert a new sheet, rename it, and then copy the block of formulae from another sheet. The attached file shows this.

Hope this helps.

Pete

Thank you Pete

that worked a treat.

Mark

You're welcome, Mark - glad it worked for you.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete