-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Counting number of Saturdays between two dates
- Thread starter Steve_C
- Start date
Chitosunday
Well-known Member
- Joined
- Jul 14, 2003
- Messages
- 1,023
=INT((+C1-B1)/7)+IF(WEEKDAY(B1)+MOD(C1-B2,7)>=7,1,0)
Upvote
0
Chitosunday
Well-known Member
- Joined
- Jul 14, 2003
- Messages
- 1,023
Please disreguard my previous report which i use b2 instead of b1, this is the correct one
=INT((+C1-B1)/7)+IF(WEEKDAY(B1)+MOD(C1-B1,7)>=7,1,0)
=INT((+C1-B1)/7)+IF(WEEKDAY(B1)+MOD(C1-B1,7)>=7,1,0)
Upvote
0
Iridium
Well-known Member
- Joined
- Jul 15, 2002
- Messages
- 2,831
Example from http://www.cpearson.com/excel/DateTimeWS.htm
Number Of Mondays In Period
If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
HTH
Number Of Mondays In Period
If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
HTH
Upvote
0
Upvote
0
The formula in cell D2 is:
=INT((C2+7-A2)/7)-INT((B2+7-A2)/7)+INT(WEEKDAY(B2+7-A2)/7)
which is slightly shorter than C. Pearson's ARRAY formula that gives the same results:
=SUM(IF(WEEKDAY(B2-1+ROW(INDIRECT("1:"&TRUNC(C2-B2)+1)))=A2,1,0))
Both formulas rely on 1=Sunday, 2=Monday, etc.
--Tom
=INT((C2+7-A2)/7)-INT((B2+7-A2)/7)+INT(WEEKDAY(B2+7-A2)/7)
which is slightly shorter than C. Pearson's ARRAY formula that gives the same results:
=SUM(IF(WEEKDAY(B2-1+ROW(INDIRECT("1:"&TRUNC(C2-B2)+1)))=A2,1,0))
Both formulas rely on 1=Sunday, 2=Monday, etc.
--Tom
| MrExcel82403.xls | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Day | Start | End | Count | Sum | ||
| 2 | 1 | 01/08/03 | 31/08/03 | 5 | |||
| 3 | 2 | 01/08/03 | 31/08/03 | 4 | |||
| 4 | 3 | 01/08/03 | 31/08/03 | 4 | |||
| 5 | 4 | 01/08/03 | 31/08/03 | 4 | |||
| 6 | 5 | 01/08/03 | 31/08/03 | 4 | |||
| 7 | 6 | 01/08/03 | 31/08/03 | 5 | |||
| 8 | 7 | 01/08/03 | 31/08/03 | 5 | 31 | ||
| 9 | 1 | 01/08/03 | 15/08/03 | 2 | |||
| 10 | 2 | 01/08/03 | 15/08/03 | 2 | |||
| 11 | 3 | 01/08/03 | 15/08/03 | 2 | |||
| 12 | 4 | 01/08/03 | 15/08/03 | 2 | |||
| 13 | 5 | 01/08/03 | 15/08/03 | 2 | |||
| 14 | 6 | 01/08/03 | 15/08/03 | 3 | |||
| 15 | 7 | 01/08/03 | 15/08/03 | 2 | 15 | ||
| 16 | 1 | 01/10/03 | 03/10/03 | 0 | |||
| 17 | 2 | 01/10/03 | 03/10/03 | 0 | |||
| 18 | 3 | 01/10/03 | 03/10/03 | 0 | |||
| 19 | 4 | 01/10/03 | 03/10/03 | 1 | |||
| 20 | 5 | 01/10/03 | 03/10/03 | 1 | |||
| 21 | 6 | 01/10/03 | 03/10/03 | 1 | |||
| 22 | 7 | 01/10/03 | 03/10/03 | 0 | 3 | ||
Sheet6 | |||||||
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=6))
would count Saturdays between A2 and B2 which house true dates with A2 <= B2.
would count Saturdays between A2 and B2 which house true dates with A2 <= B2.
Upvote
0
Similar threads
- Question
- Locked
- Question
