Solved: Count the number of Mondays between [StartDate] an... - Page 2

Doors · 03-11-2020 08:13 AM

Hi All,

What expression would I use to count the number of Mondays between [StartDate] and [EndDate]?

Thanks,

nkadonaga

Assuming you want to include [StartDate] and [EndDate] in the count if either/both of them are Mondays, then try the following:

WEEKNUM([EndDate])
-WEEKNUM([StartDate])
-IF(WEEKDAY([StartDate])>2, 1, 0)
+IF(WEEKDAY([EndDate])>1, 1, 0)
+(YEAR([EndDate])-YEAR([StartDate]))*52

Here’s the tests I tried that worked (dates are formatted mm/dd/yy):

StartDate	EndDate	Result
3/1/20	3/8/20	1
3/2/20	3/9/20	2
3/2/20	3/10/20	2
3/1/20	3/1/20	0
3/7/20	3/7/20	0
3/2/20	3/2/20	1
12/29/19	1/3/20	1
12/29/19	1/10/20	2
12/29/19	1/3/21	53

The expression doesn’t always work when StartDate and EndDate are in different years, since it assumes 52 Mondays per year. Some years (2018, 2021, 2024) have 53 Mondays per year, so this calculation will be wrong if you try multi-year ranges:

StartDate	EndDate	My result	Should actually be
12/29/16	3/10/20	166	167
12/29/90	3/10/20	1519	1524

View solution in original post

Count the number of Mondays between [StartDate] and [EndDate]