Getting 'Dates'

E

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
 
S

smartin

Esradekan said:
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

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
 
E

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

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
 
W

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top