How to determine number of Saturdays in a date range?

**casper_1981** · 03-09-2010, 02:02 PM

Hi,

I want to determine number of saturdays in a date range, i.e if we are provided with a start date and a end date then how can we calculate the number of staurdays b/w those two days?

Start date & End date can be a week day or a weekend.

Please help.

Regards,
Casper

**darkyam** · 03-09-2010, 02:09 PM

Where A1 is start date and A2 is end date, =INT((A2-A1)/7)+IF(MOD(A2-A1,7)-WEEKDAY(A1),1,0)

**NBVC** · 03-09-2010, 02:12 PM

Courtesy of daddylonglegs.. hopefully I don't get it wrong...

=INT((WEEKDAY(A4-7)+B4-A4)/7)

where the Red 7 represents Saturday.. in a Week that starts off as Sunday equals 1...

Where A4 is start and B4 is End

**6StringJazzer** · 03-09-2010, 02:38 PM

Originally Posted by darkyam

Where A1 is start date and A2 is end date, =INT((A2-A1)/7)+IF(MOD(A2-A1,7)-WEEKDAY(A1),1,0)

I did some testing on both solutions plus my own (see below). The above formula appears to overstate the result by 1 for ranges starting on Tuesday when the number of days is a multiple of 7, 1 added to a multiple of 7, or 1 subtracted from a multiple of 7.

My version:

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