How to calculate number of Mondays between two dates in Spotfire

How to calculate number of Mondays between two dates in Spotfire

This article talks about how to calculate the number of Mondays between two dates in Spotfire. Suppose you have Date1 as a start date and Date2 as an end date. Create the following calculated columns to calculate the number of Mondays between the start date and end date.

Create Calculated columns :

1). Daydiff = DateDiff("dd",[Date1],[Date2])
2). IntermediateColumn = Ceiling(Integer([Daydiff]) / 7)
3). StartDay = DayOfWeek([Date1])
4). EndDay = DayOfWeek([Date2])
5). NO OF MONDAYS = Case  
when ([StartDay]=1) and ([EndDay]=0) then [IntermediateColumn] 
when ([StartDay]=1) and ([EndDay]=1) then [IntermediateColumn] + 1 

when ([StartDay]=2) and ([EndDay]=0) then [IntermediateColumn] - 1 
when ([StartDay]=2) and ([EndDay]>=3) then [IntermediateColumn] - 1 

when ([StartDay]=3) and ([EndDay]=0) then [IntermediateColumn] - 1 
when ([StartDay]=3) and ([EndDay]>=4) then [IntermediateColumn] - 1 

when ([StartDay]=4) and ([EndDay]=0) then [IntermediateColumn] - 1 
when ([StartDay]=4) and ([EndDay]>=5) then [IntermediateColumn] - 1 

when ([StartDay]=5) and ([EndDay]=0) then [IntermediateColumn] - 1 
when ([StartDay]=5) and ([EndDay]>=5) then [IntermediateColumn] - 1 

when ([StartDay]=6) and ([EndDay]=0) then [IntermediateColumn] - 1 
when ([StartDay]=6) and ([EndDay]>=6) then [IntermediateColumn] - 1

else [IntermediateColumn]
end

See the attached DXP file (Filename: NoOfMondays.dxp) that implements the solution.