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

[Doors]

Hi All,

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

Thanks,

[nkadonaga · Accepted solution]

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

[WillowMobileSys]

Natalie, Very nicely written!!

[pravse]

Please welcome @natalie to the AppSheet community (a brand new member of our engineering team at Google). Her first community response :]

[Aleksi]

Something like this should solve the year issue…
SWITCH(WEEKDAY([START]),
1,FLOOR((HOUR([END]-[START])/24+6)/7),
2,FLOOR((HOUR([END]-[START])/24)/7)+1,
FLOOR((HOUR([END]-[START])/24+WEEKDAY([START])-2)/7)
)

[gutoferrari]

Hi!

Which parameter I need change to calculate number of Tuesday?