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
--
Knowldege is Power

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

-----Original Message-----
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
--
Knowldege is Power
.

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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.

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

"Biff" wrote:

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

-----Original Message-----
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
--
Knowldege is Power
.

"Ajit Munj" wrote
Sorry Biff, Its not working! Its giving #Name error.

Think you probably missed out the
significance of Biff's last line <g
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.

On Wed, 9 Mar 2005 23:01:03 -0800, Ajit Munj wrote:

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

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

-----Original Message-----
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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.



.

Hi Bob!

Versatility!

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

It could be any two dates.

Biff

-----Original Message-----
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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.



.

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)


"Biff" wrote in message
...
Hi Bob!

Versatility!

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

It could be any two dates.

Biff

-----Original Message-----
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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.



.

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" wrote in message
...
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

-----Original Message-----
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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.



.

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)


"Biff" wrote in message
...
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

-----Original Message-----
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)


"Biff" wrote in message
...
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

-----Original Message-----
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
--
Knowldege is Power
.



.



.