Find out saturday within a date range

[Shankar Narayanan T]

Hi,

Can anyone write a formula to find out whether saturday comes within a date range.

Example 1:-

Start date & time : 6th June 2014 (Friday)
End date & Time : 9th June 2014 (Monday)

Example 2:-

Start date & time : 5th June 2014 (Thursday)
End date & Time : 10th June 2014 (Tuesday)

Saturday falls in between both the above scenarios.

Can anyone try ?

 

[shrivallabha]

How are the dates actually written in your spreadsheet? Are they dates or strings like shown above?
If the Start Date or End Date is Saturday then will that be affirmative result or otherwise?

 

[Lori]

=NETWORKDAYS.INTL("06-Jun-14","09-Jun-14","1111101")

 

[Mike H..]

With your start date in A1 and end date in A2 try this

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=6))

or if as your question says you simply want to know if there's a Saturday in the date range try this which returns TRUE or FALSE

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=6))>0

 

[Haseeb A]

Or,

=INT((WEEKDAY(A1-7)+A2-A1)/7)

A1: Start Date
A2: End Date

 

[ashish mehra]

=NETWORKDAYS.INTL("06-Jun-14","09-Jun-14","1111101")

Hi Lori,

What is "1111101"?

Regards,
AM

 

[Debraj]

Hi Asish..

From Excel Help Section..

• Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.
  
  For example, 0000011 would result in a weekend that is Saturday and Sunday.

 

[shrivallabha]

And NETWORKDAYS.INTL is available in Excel version 2010 onwards. Hopefully OP will come back some day and see that good Santa has filled his bag with lots of useful replies .