Series of dates

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
405
Hi


I have series of sheets named 7n to 8f (7th November 2007 to 8th Feb 2008. I enter date in B1 in 7n sheet and automatically the date plus one day should appear in B1 in all other sheets upto 8f. In between if I add sheets the dates should automatically populated in B1 correctly.

Can any body give me some help please.

Regards,
Prakash
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
For starters, you are going to run into trouble when you get to a month that starts with the same letter as a previous one. One solution could be to name your sheets:
07-Nov
08-Nov
09-Nov
Etc.
Then use this macro to set the dates:
Code:
Sub SetDates()
Dim wb As Workbook
Dim sh As Worksheet
Set wb = ThisWorkbook
For Each sh In wb.Worksheets
    sh.Range("B1") = sh.Name
Next sh
End Sub
 

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
405
Thanks for your reply

Your code is giving the sheet name,7n in B1 cell, but what I want is what ever the date I mention in sheet 7n in B1cell as eg. 7/11/2007, in the subsequent sheets the date should get populated by one day that in 8n sheet B1 cell the date should be 8/11/2007,9n, 9/11/2007, 10n 10/11/2007 etc till the last sheet.

It is something similar to formula previous date+1, what we give in cells,

Is it possible to group all the sheets and give a formula. Because the number of sheets are more.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
The idea of the macro was to place the sheet name into B1. However, if you name the sheets as I suggested, this will automatically be converted to the appropriate date when it gets entered into the cell.
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top