Sequential Dates Over Multiple Worksheets

**RevDaniel** · 08-09-2009, 12:13 PM

Good Morning All,

I hope everyone is having a Blessed Day.

I am having problem with getting a sequential date across multiple worksheets. The date is in the same cell in all work sheets. For example I have 31 worksheets for everyday of the month. The date is in cell B1 in every worksheet. When I put in the date in Worksheet 01 for the first day of the month I would like all of the dates to follow in sequence in all of the other work sheets.

I started with a this Function:

[code]
=MAX(Sheet01!B:B)+1
[code]

The problem is that the date starts with January 01 1900. As you can see I need it to populate first day of the month (example: August 09, 2009).

I tried the =DATE but it did not work.

Any help would be greatly appreciated

Reverend Daniel

**mrice** · 08-09-2009, 02:13 PM

It could be as simple as

=Sheet01!B1+1 on sheet02 and =Sheet02!B1+1 on sheet03 etc.

Alternatively, to avoid having to create a separate version of the formula on each sheet, you could use the following user defined function.

Please Login or Register  to view this content.

To use this, open the VBA editor (Alt F11) , add a new module (right click on the appropriate VBA project - insert- module) and paste the above in.

Then add the formula

=AddSequentialDate("B1",B1)

to each B2 on sheets 02 onwards. The function works out which sheet it is on and adds one to the sheet before.

Hope this helps.

**RevDaniel** · 08-09-2009, 09:21 PM

Martin,

Thank You Very Much!!!

The first solution is the one I ended up going with. I tried the VB code but unfortunately, I was unable to get it to work. I am in the middle of classes right now and am unable to spend a whole lot of time trying to figure out what I am doing wrong. I will definately give it another try later.

Thanks for the help

Reverend Daniel

**CraigRichard** · 03-02-2013, 07:51 PM

I just wanted to thank you for such an excellent post Martin. I am a complete novice with excel yet have managed to get the VB solution to work. I have been wanting to have sequential dates for my home made diary for a number of years. This should save me a lot of time!
No need for my little brother to write each day by hand now!

**Snookem** · 05-23-2013, 02:54 AM

Originally Posted by mrice

It could be as simple as

=Sheet01!B1+1 on sheet02 and =Sheet02!B1+1 on sheet03 etc.

Alternatively, to avoid having to create a separate version of the formula on each sheet, you could use the following user defined function.

Please Login or Register  to view this content.

To use this, open the VBA editor (Alt F11) , add a new module (right click on the appropriate VBA project - insert- module) and paste the above in.

Then add the formula

=AddSequentialDate("B1",B1)

to each B2 on sheets 02 onwards. The function works out which sheet it is on and adds one to the sheet before.

Hope this helps.

Okay so I've been trying the above to do the same thing on my spreadsheet but can't get it to work...

I have 31 tabs for 31 days of a particular month in A1 (yes I changed B1 to A1 when I tried the above) and want to put one date in the first tab so the date automatically fills in for the rest.

My tabs are numbered 1 to 31 from right to left (so the most recent date shows first)

Help?

**arlu1201** · 05-23-2013, 03:44 AM

Snookem,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Snookem** · 05-26-2013, 11:48 PM

Originally Posted by arlu1201

Snookem,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

No worries, thanks