I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

=DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),1)
will give you the number of days in the month
and
=INT((DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-DAY(DATE(YEAR(A1),MONTH(A1),1)+IF(WEEKDAY(A1)=1,1, 7-WEEKDAY(A1))))/7)+1
will give you the number of sundays

Hopefully someone else will come up with a more elegant solution
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"Narnimar" wrote:

I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

Thanks but I requested total days except Sundays, 1st formula returned 29
days which must be 30days - 4 Sundays for 13/apr/2009 right?
Your 2nd formula is correct. If I need Fridays instead of Sundays How to
make it?

Best regards,
Narnimar.

"Sheeloo" wrote:

=DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),1)
will give you the number of days in the month
and
=INT((DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-DAY(DATE(YEAR(A1),MONTH(A1),1)+IF(WEEKDAY(A1)=1,1, 7-WEEKDAY(A1))))/7)+1
will give you the number of sundays

Hopefully someone else will come up with a more elegant solution
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"Narnimar" wrote:

I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

Yes, I forgot to add 1 to the formula
Use
=DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),1) + 1

For Fridays try
=INT((DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-DAY(DATE(YEAR(A2),MONTH(A2),1)+IF(WEEKDAY(DATE(YEA R(A2),MONTH(A2),1))<7,6-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),6)))/7)+1

Do test it out ...
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"Narnimar" wrote:

Thanks but I requested total days except Sundays, 1st formula returned 29
days which must be 30days - 4 Sundays for 13/apr/2009 right?
Your 2nd formula is correct. If I need Fridays instead of Sundays How to
make it?

Best regards,
Narnimar.

"Sheeloo" wrote:

=DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),1)
will give you the number of days in the month
and
=INT((DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-DAY(DATE(YEAR(A1),MONTH(A1),1)+IF(WEEKDAY(A1)=1,1, 7-WEEKDAY(A1))))/7)+1
will give you the number of sundays

Hopefully someone else will come up with a more elegant solution
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"Narnimar" wrote:

I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

Excel 2007
Table
Structured References
http://www.mediafire.com/file/ui2jji2am23/04_13_09.xlsx

On Mon, 13 Apr 2009 11:33:13 -0700, Narnimar
wrote:

I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

Total number of Sundays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1
&":"& A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))=1))

Number of days except Sundays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1
&":"& A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))<1))

--ron

On Mon, 13 Apr 2009 11:33:13 -0700, Narnimar
wrote:

I have a date in a1 as 13/04/2009. Can I get a formula to return total no of
Sundays in this month of the date? Also Can I get total no of days except
Sundays?
Thanks,
Narnimar

And I saw in a subsequent message you wanted to be able to choose the Weekday
to count.

In the formula I posted:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1
&":"& A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))=1))

the last 1 represents the day of the week, where 1=Sunday, 2=Monday, etc.

Friday would = 6.

or (note where I have DOW for Day Of Week)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1
&":"& A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))=DOW))
--ron