dates of month automation

[deepika :excel help]

how do i bring up teh days of of a month across columns. whatever todays'
date be, the formula should just check which month we r and should display
the dates. for eg., when we r in 29/1/2008 the cell should recognise that we
r in month jan and display dates from 31st dec to 3 rd feb(in calender the
first workday of teh month (monday) begins on 31st dec 2007 and the last
sunday of the month is feb 3rd).
so when im in feb, i should get dates from 4th feb to 2nd march. and for
march month it shud be as 3rd march to 30 th march etc till dec.
I should overwrite jan dates by feb dates and when in march my feb dates
should be overwritten by march dates.

In row 2 of teh sheet : from col F to AN
31-Dec-07 1-Jan-08 2-Jan-08 3-Jan-08 4-Jan-08 5-Jan-08 6-Jan-08

like the above eg., 31st dec 2007 starts at column F and this jan month
dates get filled till column AN and when its feb month it shud fill from
column F till column AK

 

[Bernie Deitrick]

Deepika,

Put this into cell F2 (a formula that returns the last Monday of the previous month):

=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY()),1)+2

and in cell G2, use the formula
=F2+1

and copy across for as far as you need - to AK2, formatting all cells as dates.

These formulas will update automatically as the month changes.

HTH,
Bernie
MS Excel MVP

 

[deepika :excel help]

Hi Bernie,
Thank You very much for ur reply. was very useful.
However when i set my system date to feb , then this formula still shows
jan month dates. how do i check if this works for feb and other months?

 

[Bernie Deitrick]

You could put a date into a cell, say E2, and use

=E2-DAY(E2)-WEEKDAY(E2-DAY(E2),1)+2

Then you can test is by putting any date into cell E2 - which is actually how I tested it prior to
posting.

HTH,
Bernie
MS Excel MVP