How to calculate number of Mondays between two dates in Spotfire
book
Article ID: KB0081379
calendar_today
Updated On:
Products
Versions
Spotfire Analyst
All
Description
This article talks about how to calculate the number of Mondays between two dates in Spotfire.
Environment
All Supported Operating Systems
Resolution
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.
Issue/Introduction
How to calculate number of Mondays between two dates in Spotfire.
Attachments
How to calculate number of Mondays between two dates in Spotfireget_app