Days in Calender

[Guest]

I have entered 01.02.2005 in A1 and format the cell as "mmmm, yyyy"
to show as "February, 2005". Now I want excel to calculate
the no. of days in Feb, 2005 and show the days as Mon, Tue,
Wed...etc from cell b2 to ab2 and date as 1,2, 3 ... from c3
to ac3. If I put 01.03.2005 in A1 to show March, 2005, excel
should calculate 31 days and show the days as Mon, Tue etc.
from b2 to ae2..
Thansk
Ajit

 

[JE McGimpsey]

one way (your ranges are a bit confusing, but you should be able to
adapt):

B2: =IF(B3<>"",B3,"")
B3: =IF(MONTH($A1+COLUMN()-2)=MONTH($A1),$A1+COLUMN()-2,"")

Format B2 as

Format/Cells/Number/Custom ddd

Format B3 as

Format/Cells/Number/Custom d

Copy B2:B3 across to AE2:AE3

Format B1 as mmmm,In article

 

[Arvi Laanemets]

Hi

B2=IF(MONTH($A$1+COLUMN()-2)=MONTH($A$2),$A$1+COLUMN()-2
,"")
and format as Custom "ddd";
C2=IF(MONTH($A$1+COLUMN()-2)=MONTH($A$2),$A$1+COLUMN()-2
,"")
and format as Custom "d";

Select B2:C2, and drag to right for 31 columns (31, when you may use some
other month in cell A1)

 

[Guest]

Thanks JE, its amazing! But will you pl. explain me the why column()-2 is used?
How does it work? I am not able to work out?
Ajit

 

[Guest]

Thanks Arvi, its amazing! But will you pl. explain me the why column()-2 is
used?
How does it work? I am not able to work out?
Ajit

 

[Arvi Laanemets]

Hi

Let's analyze the formula for B2 for case a value is returned, i.e. the
formula processed will be
=$A$1+COLUMN()-2
The COLUMN() function returns the current column number - for cell B2 it
returns 2
2-2=0
So in cell B2 the formula returns
=$A$1+0
i.e. the same date, as entered into A1, is returned
The conditional part of IF in formula compares months of 2 identical data -
of-course they always are same (sorry, but I just discovered an error in my
formula - there must be MONTH($A$1))

Now let's make a similar analyse for C2
When the contition test is passed, for this cell COLUMN() returns 3, 3-2=1.
When 1 is added to some date, it means a day is added - so we get next date
(02.02.2005 for your example).
Again a conditional check is made - as both dates 01.02.2005 and 02.02.2005
are from same month, the check passes, and the date is returned.

So goes it on - in each column the number added to staring day is increased
by 1. In cell AC2 the date 28.02.2005 is returned. In next (in cell AD2)
column, the returned date will be 01.03.2005 - so the conditional check
returns false, and an empty srting is returned instead of date. The same for
rest of cells in same row (AE2:AF2).