How to Find Saturday & Sunday Dates in Formula

[webmax]

Hi,

Kindly find the attached file and tell me how to find Saturday & Sunday Dates in Formula
as output attached.

With regards
Shahul

 

[Debraj]

Try this..

=IF($C$2+ROW(A1)*7-WEEKDAY($C$2)>=$C$3,"",$C$2+ROW(A1)*7-WEEKDAY($C$2))

 

[Xiq]

Try this..

=IF($C$2+ROW(A1)*7-WEEKDAY($C$2)>=$C$3,"",$C$2+ROW(A1)*7-WEEKDAY($C$2))

Hi Deb!
Nice formula, though your solution in the file doesn't output the first sunday if the input is on a sunday

 

[Xiq]

I guess you can replace the formula (and copy down) in C8 with:

=IF($C$2+ROW(A1)*7-WEEKDAY($C$2)+1>=$C$3,"",$C$2+ROW(A1)*7-WEEKDAY($C$2)+1)

 

[webmax]

Hi if start date and end date is the same date for example 21 Sep 2013 is a start date and 21sep 13 is a end date it is not working.

 

[NARAYANK991]

Hi Shahul ,

Try this in B8 :

=SMALL(IF(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=7,ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3))),ROW(A1))

Try this in C8 :

=SMALL(IF(WEEKDAY(ROW(INDEX(B:B,$C$2):INDEX(B:B,$C$3)))=1,ROW(INDEX(B:B,$C$2):INDEX(B:B,$C$3))),ROW(A1))

Both are array fomulae to be entered using CTRL SHIFT ENTER.

If you want to avoid error values , use an IFERROR around both formulae.

Narayan

 

[webmax]

hi sir

I i need the formula to count of how many saturday and sunday falls in between dates.

 

[NARAYANK991]

Hi Shahul ,

Try this for the count of Saturdays :

=SUM(IF(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=7,1))

For the count of Sundays :

=SUM(IF(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=1,1))

Both are array formulae , to be entered using CTRL SHIFT ENTER.

For non-array formulae , use :

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=7))

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=1))

Narayan

 

[Xiq]

...
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=7))
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))=1))

Narayan

Or combined:

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$C$2):INDEX(A:A,$C$3)))={1,7}))

 

[Sajan]

Here is one more idea:
=(EndDate-StartDate+1)-NETWORKDAYS(StartDate,EndDate)