Counting number of Saturdays between two dates

[Steve_C]

Hi fellas,

If I have two dates in different cells (say start date in B1, and end date in C1) what formula do i type in D1 to count the number of saturdays included in the period.

Thanks in advance for any help, sorry if its a bit basic.

Steve

 

[rrdonutz]

Originally posted by Aladin:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=6))

would count Saturdays between A2 and B2 which house true dates with A2 <= B2.

Aladin, I think your (slick!) formula should read:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=7))

for Saturdays, or:

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

You realize, of course, that your formula fails for dates greater than June 5, 2079! (Most of us will be dead or doddering around with canes by then, anyway. ) Also, the formula I proposed above can be simplified, too; for Saturdays it becomes:

=INT(C2/7)-INT(B2/7)+INT(WEEKDAY(B2)/7)

where B2 & C2 house Start & End dates as before.
--Tom

 

[Aladin Akyurek]

Originally posted by Aladin:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=6))

would count Saturdays between A2 and B2 which house true dates with A2 <= B2.

Aladin, I think your (slick!) formula should read:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=7))

for Saturdays...
--Tom

That's right.

 

[kcmuppet]

The number of weekend days in the period divided by 2 could also work I suppose...though perhaps not as accurate

=(B2-A2-(NETWORKDAYS(A2,B2)))/2

 

[MARK858]

@kcmuppet, these days you would probably use the NETWORKDAYS.INTL function (it wasn't around when Aladin posted)

Book1
	B	C	D	E	F
1	01/01/2021	24/02/2021		8	7.5
Sheet1

Cell Formulas
Range	Formula
E1	E1	=NETWORKDAYS.INTL(B1,C1,"1111101")
F1	F1	=(C1-B1-(NETWORKDAYS(B1,C1)))/2