Replicating the DAYS360 function in excel

[iversen_vasquez]

Hi all, 

I'm trying to replicate the DAYS360 excel function in Alteryx. Below is an example. Start date is 2012-11-27 and  end date is 2017-11-27.

DAYS360 function rounds up the year as 360 days divided into 12 '30-day' months.

DateTimeDiff works the same as how the DAYS function would, but not the DAYS360 function.

 

I'm looking for a function/workaround to get the same value as DAYS360 gives, which in this case, is 1800.

This is my first post here so please be kind to me! 

[MarqueeCrew · Accepted solution]

The formula that I used is pictured below.  

StartDay (Byte) is:

IF DateTimeParse([Start Date],"%d") == '31' or DateTimeFormat(DateTimeAdd([Start Date],1,"day"),"%m") == '03' 
THEN 30
ELSE ToNumber(DateTimeFormat([Start Date],"%d"))
ENDIF

This uses the day number unless it is the 31st or the last day of February.  In those cases, the day becomes 30.

EndDay (Byte) is:

IF DateTimeParse([Start Date],"%d") == '31' or DateTimeFormat(DateTimeAdd([Start Date],1,"month"),"%m") == '03' 
THEN 30
ELSE ToNumber(DateTimeFormat([Start Date],"%d"))
ENDIF

This uses the day number unless the start day is 30 and the end day is 31, then it becomes 30.

Days360 is:

(ToNumber(DateTimeFormat([End Date],"%Y")) - ToNumber(DateTimeFormat([Start Date],"%Y"))) * 360
+
(ToNumber(DateTimeFormat([End Date],"%m")) - ToNumber(DateTimeFormat([Start Date],"%m"))) * 30
+
[EndDay]-[StartDay]

Take the difference in YEARS and multiply that by 360.  Then Add the difference in months and multiply that by 30.  Then add the differences in endday and startday.  

This should give the same result as Excel. (note:  with edited updates from iversen)

Cheers,

Mark

[iversen_vasquez · Accepted solution]

Hi Mark,

Wow, I didn't expect such a quick response, and a great one at that. Thank you so much!

I tried this out, seemed to work at first. Except for a few mismatches. I found that the error was in the formula for StartDay:

IF DateTimeParse([Start Date],"%d") == '31' or DateTimeFormat(DateTimeAdd([Start Date],1,"day"),"%m") == '03' 
THEN 30
ELSE ToNumber(DateTimeFormat([Start Date],"%d"))
ENDIF

The second "OR" condition was trying to take into account February 28 and 29 but instead of adding 1 month to February, it added only 1 day. Therefore what happened was, if the date was, for example, March 3, then March 4 would still satisfy the second "OR" condition and hence return "30" as the StartDay. I figured out what your formula was trying to do and I worked around the desired logic and ended up with a better solution.

IF DateTimeFormat([Start Date],"%d") == '31' 
OR (DateTimeFormat([Start Date],"%m") = '02' 
       AND (DateTimeFormat([Start Date],"%d") == '29' 
           OR DateTimeFormat([Start Date],"%d") == '28'))
THEN 30
ELSE ToNumber(DateTimeFormat([Start Date],"%d"))
ENDIF

 For some reason, DateTimeParse sometimes returns a null value so I used DateTimeFormat for every step for consistency.

Although it took me a while to figure out what was wrong, your response provided a really clear picture of the ideal solution to my initial problem. Thank you so much!

[Mr_Brown]

Hi,

I am trying to apply the same formula, however in the Day360 formula it is showing me the error i.e. "type mismatch in operator +.

Can you please help me out with this