Work out a month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have twelve worksheets with 31 rows each for, say,
month 1 to month 12. The idea is to have 12 months each
on a seperate sheet. On the month1 sheet I want to put
the first day of a given month. This could be any month.
I want then the other sheets to be updated and show just
the days for the relevant month. e.g. If I enter 1/1/04
in row 1 of the month1 sheet, month2 sheet should show
dates up to 29/2/04 and in row 30 and 31 it shuld show
nothing. Could you help to construct a formula to just
show the month? Thanks in advance for your help.

Regards
 
Hi
try the following formulas
1. Sheet1:
A1=enter your starting date
A2=IF(MONTH(A1+1)=MONTH(A1),A1+1,"")
and copy this down for 31 rows

2. Sheet2 (and following sheets:
A1:=DATE(YEAR('sheet1'!A1),MONTH('sheet1'!A1)+1,1)
A2=IF(MONTH(A1+1)=MONTH(A1),A1+1,"")
and copy this down for 31 rows
 
Hi

Into cell A1 on sheet Month1 enter the 1st of starting month, and format it
p.e. as Custom "mmmm.yyyy"
Into cell A2 on sheet Month1 enter the formula
=IF(MONTH($A$1+ROW()-2)=MONTH($A$1),$A$1+ROW()-2,"")
and format as Custom "dd"
Copy A2 down to row 32.

Copy sheet Month1 as Month2 (right-click on sheet's tab, select 'Move or
Copy' from dropdown list, be sure you check 'Create a copy', etc.)
On sheet Month2, into cell A1 enter the formula
=DATE(YEAR(Month1!$A$1),MONTH(Month1!$A$1)+1,1)

Copy sheet Month2 as Month3
A1=DATE(YEAR(Month1!$A$1),MONTH(Month1!$A$1)+2,1)
etc. for all 12 sheets.

When you want to make your workbook foolproof, so that entering the date not
the 1st of month doesn't mess up your data, then you have to modify formulas
on sheet Month1.
A2=IF(MONTH(DATE(YEAR($A$1),MONTH($A$1),ROW()-1))=MONTH($A$1),DATE(YEAR($A$1
),MONTH($A$1),ROW()-1),"")
and copy down to row 32.
On other sheets you can leave formulas unchanged.
 
Thank you both. It is very helpful. Thanks.

-----Original Message-----
Hi
try the following formulas
1. Sheet1:
A1=enter your starting date
A2=IF(MONTH(A1+1)=MONTH(A1),A1+1,"")
and copy this down for 31 rows

2. Sheet2 (and following sheets:
A1:=DATE(YEAR('sheet1'!A1),MONTH('sheet1'!A1)+1,1)
A2=IF(MONTH(A1+1)=MONTH(A1),A1+1,"")
and copy this down for 31 rows


.
Click to expand...
 
Back
Top