Endeavoring to extract a more precise number of months and days between dates

[Archaeopteryx]

Hi,

I'm attempting to take a duration between two dates and parse that duration out into months and days. 

I'm using these formulas, which are nearly precise except when the dates cross July and August. I'm assuming because those two months are 31 days each. 

This is [Duration in Months]:

DateTimeDiff([End Date],[Start Date],"months")

This is [and days duration]

round(DateTimeDiff([End Date],[Start Date],"days")-([Duration of contract in months]*30.5),1)

I rounded up the average number of days in a month from 30.4166666 to 30.5. and am rounding up the whole equation. But when the dates cross over July and August, a day gets dropped. 

Is there a more precise method to getting exact days that are the remainder after the number of months have been calculated? 

thanks

Chris

[rfoster7]

So I'm not sure I follow. You have two days. Rather than difference in days you want to format the different to show "X months and Y days" where a Month is defined as 30.5 days?

So if you have a start date of 2019-06-10 and an end date of 2019-08-14. The difference in days is 65. So you would want it to show "2 Months and 4 days"? 

If I have that right, then you would need formulas like this

Did I get your intention right? I guess, in my mind because Months are arbitrary in length, I would tend to just give the difference in pure days or in a fraction of a year. 

[Garrett]

I suspect there is probably an easier "mathy" way to do this that doesn't involve creating a bunch of superfluous data, but what first came to my mind was to generate a row for each day and then use the rows to calculate "complete months" and "remainder days":

[Archaeopteryx]

Hi,

I'm not sure that multiplying and dividing by 30.5 is right as it is one of the likely culprits throwing off the accuracy.

I'd like to take a date range between two dates, i.e. 2017-04-29 and 2019-12-27 and derive 31 months and 28 days. 

[Archaeopteryx]

Hi Garrett 

Thanks for your reply. 

I would expect to see in Record 1, one month and 11 days. 

[rfoster7]

Oh okay. 

So

the difference between 1/1/2019 and 3/4/2019, even though it is 62 days, you want it to say 1 month and 3 days.

the difference between 7/1/2019 and 9/4/2019, even though it is 65 days you also want it to say 1 month and 3 days. 

I get it now. 

Try these formulas:

if datetimeday([Start Date]) < datetimeday([End Date]) then
datetimediff([End Date],datetimeadd(datetimetrim([End Date],'month'),tonumber(datetimeday([Start Date]))-1,'days'),'days')
elseif datetimeday([Start Date]) = datetimeday([End Date]) then
0
else
datetimediff([End Date],datetimetrim([End Date],'month'),'days')
+ datetimediff(datetimetrim(datetimeadd([Start Date],1,'month'),'month'),[Start Date],'days')
endif

[Archaeopteryx]

Hi,

Thank you for replying. 

when this formula is presented with a date span of 2018-03-12 --> 2019-09-18, it yields the correct number of days, 555; but the month/day breakdown says, 18 months and 17 days; when it should be 18 months 6 days. 2019-06-06 --> 2019-11-29, yields the correct number of days, 176; but the month/day breakdown says, 5 months and 28 days; when it should be 5 months and 23 days. 

Many of the date range breakdowns are correct though. 

Let's keep trying. And I mean me too as well. 

thank you,

Chris

[danilang]

Good Morning @Archaeopteryx (Great user name, BTW)

Any formula based on average months lengths is guaranteed  to be inaccurate at some point during the year.  Luckily, Alteryx counts the actual calendar days between dates to calculate both the month and day differences.  You can combine these operations into one formula tool to give you the proper results

The first formula is the standard DateTimeDiff to give the number of months between 2 dates.  The second formula finds the difference from the start+the number of months calculated in the 1st formula to the end date.

Here are the results for all the dates ranges that you called out throughout the thread

Dan

[Archaeopteryx]

Danilang,

Thank you. This solution is simple and elegant. May I ask how much different the formula would be for a Start Date that is the Current Date? I have noticed that if I replace the Start date with DateTimeNow(), the number of days seems to count the current day in the calculation. Static dates are perfectly calculated however. 

Thanks also for the complement on the user name. It barely escaped scrutiny when I first signed on to the Community. 

Chris

[Archaeopteryx]

Danilang,

Actually, I have that backwards. A day is dropped when the Current Date is used as DateTimeNow().