Getting 'Dates'

[Esradekan]

I have a table. B2:M2 is merged and has the year only in it (ie
2002). B3:M3 has the months of the year (Jan to Dec).B4:M34 I want to
contain the relevant dates for the months in row 3 of the table. (ie
columnB4:B34 should contain 1/1/2002 to 1/31.2002). What formula do I
use for that? Only one I know of will just create the date in lower
cells based on current year.

Can anyone help?

TIA

Esra

 

[smartin]

I have a table. B2:M2 is merged and has the year only in it (ie
2002). B3:M3 has the months of the year (Jan to Dec).B4:M34 I want to
contain the relevant dates for the months in row 3 of the table. (ie
columnB4:B34 should contain 1/1/2002 to 1/31.2002). What formula do I
use for that? Only one I know of will just create the date in lower
cells based on current year.

Can anyone help?

TIA

Esra

Cheap shots:

(1) Paste this in B4:M34:

=DATE($B$2,COLUMN()-1,ROW()-3)

(2) This slightly better version erases spurious dates:

=IF(MONTH(DATE($B$2,COLUMN()-1,ROW()-3))<=MONTH(DATE($B$2,COLUMN()-1,1)),DATE($B$2,COLUMN()-1,ROW()-3),"")

 

[T. Valko]

One way...

Enter this formula in B3:

=IF(ROWS(B$3:B3)>DAY(DATE($B$1,COLUMNS($B3:B3)+1,0)),"",DATE($B$1,COLUMNS($B3:B3),ROWS(B$3:B3)))

Copy across to M3 then down to B33:M33

 

[Esradekan]

One way...

Enter this formula in B3:

=IF(ROWS(B$3:B3)>DAY(DATE($B$1,COLUMNS($B3:B3)+1,0)),"",DATE($B$1,COLUMNS($­B3:B3),ROWS(B$3:B3)))

Copy across to M3 then down to B33:M33

--
Biff
Microsoft Excel MVP







- Show quoted text -

Thank you all for your help, problem solved. Appreciate the
assistance.

Esra

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

One way...

Enter this formula in B3:

=IF(ROWS(B$3:B3)>DAY(DATE($B$1,COLUMNS($B3:B3)+1,0)),"",DATE($B$1,COLUMNS($­B3:B3),ROWS(B$3:B3)))

Copy across to M3 then down to B33:M33

--
Biff
Microsoft Excel MVP







- Show quoted text -

Thank you all for your help, problem solved. Appreciate the
assistance.

Esra

 

[WallyWallWhackr]

I have a table. B2:M2 is merged and has the year only in it (ie
2002). B3:M3 has the months of the year (Jan to Dec).B4:M34 I want to
contain the relevant dates for the months in row 3 of the table. (ie
columnB4:B34 should contain 1/1/2002 to 1/31.2002). What formula do I
use for that? Only one I know of will just create the date in lower
cells based on current year.

Can anyone help?

TIA

Esra

While you are getting assistance with your particular problem, check out
how I handled dates and year long views, with a switch for leap years.
Just plug in the year, and all the dates and days of the week fill in.

You could alter that annual table to include your data.

You can alter and continue to use any of my templates.

Give this a look, and you may get some ideas...

http://office.microsoft.com/en-us/templates/TC300063451033.aspx?CategoryID=CT101440991033