5 Replies Latest reply on Dec 19, 2013 2:15 AM by Shawn Wallwork

# Date Difference between to date based on 360days in Year

Hi

How can i get the Date difference based on 360 days in Yaer.

I currently i am using Datediff formula but am not able to freeze the number of dates in year to 360.

Bhujang

• ###### 1. Re: Date Difference between to date based on 360days in Year

Hi Bhujang,

I am not clear with your requirement. Please post a sample workbook(.twbx) or screenshot of your requirement. Are you looking for calculation to find difference between two date? Which dates in your dataset are excluded? So, its better to attach a sample file.

BTW, here is something that may help you : - http://community.tableau.com/thread/104481

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 2. Re: Re: Date Difference between to date based on 360days in Year

Hi prashant,

Thanks for your prompt response

Please find the attached workbook and excel data with solution what i am looking for in Tableau.

Note- Workbook is developed in 8.

Regards

Bhujang

• ###### 3. Re: Date Difference between to date based on 360days in Year

Hi Bhujang,

Which dates you want to excluded from this dataset?  Difference between dates from January to April is showing correct data but difference from January to August is showing difference of 2 from original value, so its difficult for me to understand that on which criteria you are excluding dates. Can you please elaborate? Another question is that you want to show repetition of dates on the basis of MCO? You want to show exactly like the below table?

 South Africa 1/1/2014 31/12/14 360 South Africa 1/1/2014 31/12/14 360 South Africa 1/1/2014 31/12/14 360

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 4. Re: Re: Date Difference between to date based on 360days in Year

Prashant,

If u look at the below image ( excel formula =days360, here no.of days in year is hard coded to 360days) so the aggregated number for nigeria is 679 and in tableau its 685( because we didn't hard coded to 360days)

so i am looking for formula in tableau to replicate the same formula what is there in excel.

• ###### 5. Re: Re: Re: Date Difference between to date based on 360days in Year

Change your pill aggregation from SUM() to MAX(). And change your calc to this:

DATEDIFF('day',[Start Date],[Current end date]) -

(IF YEAR([Current end date])=YEAR([Start Date]) THEN 4

ELSE

(((YEAR([Current end date])-YEAR([Start Date])))*4)

END)

(See attached.)

--Shawn

PS: Not sure how leap years will be handled.