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

Hi All,

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

Thanks, :slight_smile:

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
12 Likes

Natalie, Very nicely written!!

2 Likes

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

5 Likes

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)
)

2 Likes