10 Replies Latest reply on Jul 7, 2016 5:37 PM by Dan Gagnon

# Find the number of days between two dates

Hi Guys,

I would like to calculate the number of days between two dates in a planning form including my end date in calculation.

I wrote a script and which is working fine except FY14-15 March.  03/09/15 it is not considered in the calculation and hence it impacts the whole year calculation. Please find the below with example

Here I am keeping the start date constant and changing the end date.

Case 1: (Working Fine)

Start Date: 11/29/14

End Date: 03/07/15

Number of days: 99

Case 2: (Working Fine)

Start Date: 11/29/14

End Date: 03/08/15

Number of days: 100

Case 3: (Ignoring March 9 and still showing the result as 100 instead of 101)

Start Date: 11/29/14

End Date: 03/09/15

Number of days: 100

Case 4: (Ignored March 9 and showing the result as 101 instead of 102)

Start Date: 11/29/14

End Date: 03/10/15

Number of days: 101

Case 5: (Ignored March 9 and showing the result as 102 instead of 103)

Start Date: 11/29/14

End Date: 03/11/15

Number of days: 102

And this one day difference is carried on till the end of the year.

I tried with the below functions one by one. But got the same result for all these 3 functions.

"Number of days"=   @CalcMgrdatediff("EndDate","Start date","Day")+1;

"Number of days"=   @HSPDateDiff("EndDate","Start Date")+1;

"Number of days"=   @CalcMgrDaysBetween("EndDate","Start Date")+1;

Here while calculating it is ignoring 9th March 2015 from the calendar. What is the magic here. Can someone reveal it?  And while doing testing I randomly found this error. Not sure where else in the calendar it will give this error.

And also advise me with the correct script to find the number of days between dates in HP Version 11.1.2.4

• ###### 1. Re: Find the number of days between two dates

I am seeing the same behavior..  this is not something that i would expect, you should drop an SR with Oracle

i did test all 3 methods and saw the same result on all 3, example below:

• ###### 2. Re: Find the number of days between two dates

Dan, try "DayOfMonth"

Regards

Celvin Kattookaran

• ###### 3. Re: Find the number of days between two dates

That did not work, looks like an invalid Date Part, it validates, but i don't get any value from the function.

the issue is happening with 3 different functions though, not just CalcMgrDateDiff, its also there with HSPDateDiff and CalcMgrDaysBetween

i did a couple more tests with other dates that did not include March 9, 2015 and never saw the same 'missing date'

i also one large range from 1-1Jan2015 to 31-Dec-2020 and saw only the single missing day in the result.

• ###### 4. Re: Find the number of days between two dates

i also one large range from 1-1Jan2015 to 31-Dec-2020 and saw only the single missing day in the result.

If OP doesn't need his function to work with historical data then I guess he's fine.

Still, a super weird bug.

• ###### 5. Re: Find the number of days between two dates

Well "DayOfMonth" works in some date functions and some has Day. I'll let the Calc Manager team know.

Regards

CK

• ###### 6. Re: Find the number of days between two dates

Oh this was an interesting one indeed. Guess what it is because of the day light saving. So

so if you look at 2014 March 10 onwards, you'll see the same issue.

Good thing is Calc Manager team is already looking into this. Ohhhhhhh that took a while to figure out.

Regards

CK

• ###### 7. Re: Find the number of days between two dates

I knew we were in good hands when you replied here, glad to see you got to the bottom of it.

I actually did a google search for 9-May-15 in my testing trying to see significance of that day, but not the 8th, good catch.

• ###### 8. Re: Find the number of days between two dates

Thank you all for your quick replies. And thank you Celvin for revealing the magic which made me crazy for the last 2 days. I am surprised to see this weird bug.

Cheers!!!

• ###### 9. Re: Find the number of days between two dates

I have uploaded a cdf jar file to the calc manager forum.

calcmgrcdf.jar

Try and see if that resolves the issue.

-Sree Menon

• ###### 10. Re: Find the number of days between two dates

Looks to have resolved CalcMgr functions

Thanks for the quick fix Sree