days in month - Ajit

[Ajit Munj]

If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of
days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month?
Ajit

 

[Biff]

Hi!

Here's one way:

A1 = 2/1/2005
A2 = 2/28/2005

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X))

Where X = 1 through 7 for Monday through Sunday

Biff

 

[Bob Phillips]

Here is a way that doesn't need both dates, and A1 can hold any date in the
month

=4+(DAY($A$1-DAY($A$1)+35)<WEEKDAY($A$1-DAY($A$1)-C1))

where C1 holds the day number (1 =Mon, 2=Tue), etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ajit Munj]

Sorry Biff, Its not working! Its giving #Name error.

Hi!

Here's one way:

A1 = 2/1/2005
A2 = 2/28/2005

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X))

Where X = 1 through 7 for Monday through Sunday

Biff

 

[Max]

Sorry Biff, Its not working! Its giving #Name error.

Think you probably missed out the

Where X = 1 through 7 for Monday through Sunday

Plug a number (1 to 7) into the function to replace the "X",
for e.g.:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1))

which'll return 4

 

[Bob Phillips]

This has been bothering me . Having to enter two dates, and the first and
last date of the month is not user friendly IMO (users make mistakes, lots
of them).

This can probably be done better, but here is an alternative on Biff's
formula that addresses both issues

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1)+1&":"&DATE(YEAR($A$1),MON
TH($A$1)+1,0))),2)=X))

X is as defined by Biff

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of
days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month?
Ajit

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+desired_week_day))+35)>7)

Assumes your date is in A1.

For desired_week_day substitute
1 for Sunday
2 for Monday
etc.
or a cell reference with those numbers in it.


--ron

 

[Biff]

Hi Bob!

I've seen this formula before. I'm sure it works but I
haven't tried it out yet. But just looking at it I would
like to know the logic behind 4+ and +35.

Biff

 

[Biff]

Hi Bob!

Versatility!

It doesn't have to be the first and last date of any month!

It could be any two dates.

Biff

 

[Bob Phillips]

Hi Biff,

When I tried your original formula, it counted the number of a particular
day between those dates, whereas I read the OP to say the number in the
month. Subtle but different by my reading.

Regards

Bob

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Biff,

The 4 is the base point, every month will have at least 4 instances of each
day in that month.

The 35 is used (5 weeks x 7 days) to get outermost 5 week date from teh
start of the month, which is then compared to the weekday of the last day of
the previous month less the day number being checked. If this is True, it
has the effect of adding 1 to the base point, that is identifying if there
are 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Got it! Thanks Bob.

Biff

-----Original Message-----
Hi Biff,

The 4 is the base point, every month will have at least 4 instances of each
day in that month.

The 35 is used (5 weeks x 7 days) to get outermost 5 week date from teh
start of the month, which is then compared to the weekday of the last day of
the previous month less the day number being checked. If this is True, it
has the effect of adding 1 to the base point, that is identifying if there
are 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)





.