Product: TIBCO Spotfire®

**Problem:**

How to calculate number of Mondays between two dates in Spotfire

**Solution:**

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.

## Comments

0 comments

Article is closed for comments.