Can I get no of Sundays and working days of the month?

[Narnimar]

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

 

[Sheeloo]

=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

 

[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]

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(YEAR(A2),MONTH(A2),1))<7,6-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),6)))/7)+1

Do test it out ...

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Herbert Seidenberg]

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