• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Determine SUNDAY between 2 dates

Ehtisham Ali

Ehtisham Ali

Member
Hi Fellas,

I have 2 dates and I want to determine the numbers of SUNDAY coming in between.

For Example:
Date1:
5/9/15 9:31 AM
Date2:
5/27/15 3:00 PM

Now for these dates their are 3 SUNDAY in between. Please suggest a formula for that.
 
=INT((A2+WEEKDAY(A1-1)-A1)/7)

1 for Sunday, 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday and 7 for Saturday
 
Hi Ali,

Just another formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(L5)&":"&INT(L6))),2)=6))

L5 has start date & L6 is End date.

Regards,
 
Hi Ali -

so one more for the sake of fun..

SUM(IF(WEEKDAY(B1+ROW(INDEX(A:A,1):INDEX(A:A,(B2-B1)+1))-1,1)=1,1,))

To be acknowledged with CTRL + SHIFT + ENTER

Where B1 is start date and B2 is end date
 
Somendra Misra said:
Hi Ali,

Just another formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(L5)&":"&INT(L6))),2)=6))

L5 has start date & L6 is End date.

Regards,
Click to expand...

SM Sir,
Good Day...

Just a small confusion...

Your's giving 5 instead of 4.
for example if:
Start Date = 01/05/2015
End Date = 30/05/2015

Regards,
 
If you have Excel 2010 or a later version you can use NETWORKDAYS.INTL function, e.g. with start date in A2 and end date in B2 you can use this formula

=NETWORKDAYS.INTL(A2,B2,"1111110")

The "1111110" part dictates which days to count, it starts with Monday and a 1 indicates not counted, 0 is counted
 
You must log in or register to reply here.
Back
Top