days in month - Ajit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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)
 
Sorry Biff, Its not working! Its giving #Name error.

Biff said:
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
Click to expand...
 
Ajit Munj said:
Sorry Biff, Its not working! Its giving #Name error.
Click to expand...

Think you probably missed out the
significance of Biff's last line said:
Where X = 1 through 7 for Monday through Sunday
Click to expand...

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
 
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)
 
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
Click to expand...

=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
 
Hi Bob!

Versatility!

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

It could be any two dates.

Biff
 
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)
 
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)
 
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)





.
Click to expand...
 
Back
Top