Formula to enter month and year in workbook

[mlv]

Hi

I have a yearly workbook with 12 sheets, one per month.

Each sheet has a header cell (assume A1) that shows the month and year for
that specific sheet.

Assuming I manually enter (for example) 'April 2007' in the month/year cell
of sheet 1 - how easy is it to arrange for sheets 2 to 12 to populate their
equivalent cells with successive months and the year, thus:

Sheet 1 : April 2007 (entered manually)

Sheet 2 : May 2007

Sheet 3 : June 2007

etc, etc...

Sheet 9 : December 2007

Sheet 10 : January 2008 (Note year increment)

Sheet 11 : February 2008

Sheet 12 : March 2008

Please note that the month/year manually entered in sheet 1, cell A1, may
bear no relationship to the current year or date and could be any month of
any year from 2000 onwards.

I would format Cell A1 as either 'General' or 'Text' as I don't want any
Excel date functions interfering with my input.

Thanks

 

[Niek Otten]

<I would format Cell A1 as either 'General' or 'Text' as I don't want any
Excel date functions interfering with my input.>

I wouldn't do it that way, if I were you.

I'd put an Excel date (like 4/1/2007) in A1, format it Custom as mmmm yyyy and use a formula to get the other dates:

=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1)

That will take care of incrementing years automatically

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| I have a yearly workbook with 12 sheets, one per month.
|
| Each sheet has a header cell (assume A1) that shows the month and year for
| that specific sheet.
|
| Assuming I manually enter (for example) 'April 2007' in the month/year cell
| of sheet 1 - how easy is it to arrange for sheets 2 to 12 to populate their
| equivalent cells with successive months and the year, thus:
|
| Sheet 1 : April 2007 (entered manually)
|
| Sheet 2 : May 2007
|
| Sheet 3 : June 2007
|
| etc, etc...
|
| Sheet 9 : December 2007
|
| Sheet 10 : January 2008 (Note year increment)
|
| Sheet 11 : February 2008
|
| Sheet 12 : March 2008
|
| Please note that the month/year manually entered in sheet 1, cell A1, may
| bear no relationship to the current year or date and could be any month of
| any year from 2000 onwards.
|
| I would format Cell A1 as either 'General' or 'Text' as I don't want any
| Excel date functions interfering with my input.
|
| Thanks
| --
| Mike
| -Please remove 'safetycatch' from email address before firing off your
| reply-
|
|

 

[Sandy Mann]

Mike,

First of all Custom format all the A1 cells as "mmmm yyyy" (without the
quotes)

then individually in every sheet apart from the first one enter the formula:
=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1)

but using the appropriate sheet name for the previous sheet in place of
Sheet1!

If the sheet name contains a space then surround the name with single
quotes:
=DATE(YEAR('Start Name'!A1),MONTH('Start Name'!A1)+1,1)

The easest way is to build the formula by entering:
=DATE(YEAR(
then using the mouse to select cell A1 on the previous sheet and XL will
enter the name correctly for you.

Finally enter the start date in the first sheet A1 cell as a real date like
1/1/07 and all sheets will be populated as you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

hi
another way...
Sub MacDoDates()
Dim nam1 As String
Dim nam2 As String
Dim sn As Long
Dim sd As Date
Dim sn As Long

sn = 1
Sheets(sn).Select
sd = InputBox("Enter the start date")
Do While sn <= 12
Sheets(sn).Select
nam1 = Format(sd, "mmm") 'mmmm = full month name
nam2 = Year(sd)
Range("A1").NumberFormat = "@"
Range("A1").Value = nam1 & " " & nam2
Sheets(sn).Name = nam1 & " " & nam2 'remove if needed
If sn >= 12 Then
Exit Do
End If
Sheets.Add After:=Sheets(sn)
sn = sn + 1
sd = sd + 31
Loop
'optional saveas
'ActiveWorkbook.SaveAs Filename:= _
"c:\somedirectory\anotherdirectory" _
& nam1 & " " & nam2 & " " & "yourfilename.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

regards
FSt1

 

[mlv]

Thanks guys, the formulas work fine. However, that leaves me with another
little date problem

Working from the date in cell A1 formatted 'mmmm yyyy', I need to put the
company year into another cell.

If A1 contains 'April 2007', then the company year would need to be shown as
'2007/08', because the 12th month would be March 2008.

However, if A1 contains 'January 2007', then the company year would need to
be shown simply as '2007', as the 12th month is December and the company
year does not overflow into 2008.

Can anyone suggest how this can be done?

Thanks

 

[Sandy Mann]

Try:

=TEXT(DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1),"mmmm
yyyy")&IF(MONTH(Sheet1!A1)>3,"/"&TEXT(DATE(YEAR(Sheet1!A1)+1,1,1),"yy"),"")


By the way your address bounces with a "User unknown"
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

By the way your address bounces with a "User unknown"
That is of course after removing the 'safetycatch'

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

I wouldn't do it that way, if I were you.

I'd put an Excel date (like 4/1/2007) in A1, format it Custom as mmmm yyyy
and use a formula to get the other dates:

=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1)

That will take care of incrementing years
**months**

automatically

Rick

 

[mlv]

Try:

=TEXT(DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1),"mmmm
yyyy")&IF(MONTH(Sheet1!A1)>3,"/"&TEXT(DATE(YEAR(Sheet1!A1)+1,1,1),"yy"),"")

Thanks.

I had to change the formula to:

=TEXT(DATE(YEAR(A1),1,1),"yyyy")&IF(MONTH(A1)>1,"/"&TEXT(DATE(YEAR(A1)+1,1,1),"yy"),"")

to get what I wanted. I only needed the company year shown as '2007' or
'2007/08' (i.e. without the month)

I hope I've modified the formula correctly.

Thanks for pointing me in the right direction.

 

[Niek Otten]

Hi Rick,

I really meant years. December 2006 + 1 month becomes January 2007

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|> I wouldn't do it that way, if I were you.
| >
| > I'd put an Excel date (like 4/1/2007) in A1, format it Custom as mmmm yyyy
| > and use a formula to get the other dates:
| >
| > =DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,1)
| >
| > That will take care of incrementing years
|
| **months**
|
| > automatically
|
| Rick
|

 

[Rick Rothstein \(MVP - VB\)]

I really meant years. December 2006 + 1 month becomes January 2007

Whoops... I read your formula too fast... sorry.

Rick

 

[Sandy Mann]

I hope I've modified the formula correctly.

If it is doing what you want then you have modified it correctly.

Thanks for the feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Hi.
I have made a yearly calendar in which in sheet1 you give the year and in
the next 12 sheet calculate all the months. Its look like weekly planner for
a year.
If you want it give me your email to send you.