1 Reply Latest reply on Apr 11, 2018 2:29 PM by Patrick Van Der Hyde

How to get No. of Days by selecting From Date and To Date Parameter??

Hi All,

When I select parameter of current start date and current end date, for the resource Kumar should get 10 Days based on excluding StartDate and EndDate in a given table. Please anyone help me on this calculation.

Example 1 :

 Parameter Current Start Date 31-07-2017 Current End Date 27-08-2017 Dataset Resource RescourceCreatedDate Start Date End Date Days Kumar 24-02-2014 14-08-2017 17-09-2017 10 Kumar 24-02-2014 27-11-2017 03-12-2017 Kumar 24-02-2014 25-12-2017 01-01-2050

Example 2 :

 Parameter Current Start Date 31-07-2017 Current End Date 15-12-2017 Dataset Resource RescourceCreatedDate Start Date End Date Days Kumar 24-02-2014 14-08-2017 17-09-2017 70 Kumar 24-02-2014 27-11-2017 03-12-2017 Kumar 24-02-2014 25-12-2017 01-01-2050

The formula which I used for calculating No. of Days is :

No of working days

{ FIXED [Resource]: AVG(DATEDIFF('day',[Start2],[End2])

-

datediff('week',[Start2],[End2],'sunday')

-

datediff('week',[Start2],[End2],'saturday')+1)}

Start 2

IF [Resource Created Date]>[Current End Date]

then

[Current End Date]+1

ELSE

MAX([Resource Created Date],[Current Start Date])

END

End 2

IF [startdate]<=[Current Start Date]

then

[Current Start Date]-1

ELSEIF

ISNULL([startdate])

THEN

[Current End Date]

ELSE

MIN([enddate],[Current End Date])

END

Excluded :week Ends (Saturday and Sunday)

• 1. Re: How to get No. of Days by selecting From Date and To Date Parameter??

Hello Praveen,

I see no one has replied.  There are some great resources found on this FAQ - FAQ: Open & Close Dates

Please review and if you can not find a solution that works for you, please let us know.  Be sure to share a packaged workbook so we can directly assist.

Packaged workbooks: when, why, how

Thank you

Patrick