DateDiff in months with decimal digits

[PowerRanger]

Hi all,

I am trying to find a way to calculate a DateDiff in months but with 2 Decimal digits.

So for example the DateDiff output for "01.01.2013" and "06.03.2013" (Format "dd.mm.yyyy") should be 2,2

Thanks in advance for your help

[HenryARPhillips]

Hi,

What is the logic behind the second 2? As to me the DateDiff in months is just 2?

I mean you could do string manipulation such as:

But I presume the above is not what you are after?

[PowerRanger]

Well, this is required for a calculation where we do need the exact difference in months.
So what I would do manually:

"01.01.2013" and "06.03.2013"

1. 01.012013 - 31.01.2013 = 1 month

2. 01.02.2013 - 28.02.2013 = 1 month

3. 01.03.2013 - 06.03.2013 = 6 days = (1/31)*6 = 0,1935...

==> 2,1935 months ==> 2,2 Months

 

[PowerRanger · Accepted solution]

Hi now came up with this solution:

Set(
    varMonthsDiff,
    DateDiff(
        dtpStartDate.SelectedDate,
        dtpEndDate.SelectedDate,
        Months
    )
);
Clear(MyDateTable);
ForAll(
    Sequence(varMonthsDiff + 1),
    Collect(
        MyDateTable,
        {
            CurrentSequenz: CountRows(MyDateTable),
            StartDate: dtpStartDate.SelectedDate,
            EndDate: dtpEndDate.SelectedDate,
            FirstDayOfMonth: Date(
                Year(
                    DateAdd(
                        dtpStartDate.SelectedDate,
                        CountRows(MyDateTable),
                        Months
                    )
                ),
                Month(
                    DateAdd(
                        dtpStartDate.SelectedDate,
                        CountRows(MyDateTable),
                        Months
                    )
                ),
                1
            ),
            LastDayOfMonth: Date(
                Year(
                    DateAdd(
                        dtpStartDate.SelectedDate,
                        CountRows(MyDateTable),
                        Months
                    )
                ),
                Month(
                    DateAdd(
                        dtpStartDate.SelectedDate,
                        CountRows(MyDateTable),
                        Months
                    )
                ) + 1,
                0
            )
        }
    )
);
ClearCollect(
    MyDateTable2,
    AddColumns(
        MyDateTable,
        "DaysInMonth",
        DateDiff(
            FirstDayOfMonth,
            LastDayOfMonth
        ) + 1,
        "MonthCounter",If(EndDate > LastDayOfMonth,1,RoundUp((1/(DateDiff(
            FirstDayOfMonth,
            LastDayOfMonth
        ) + 1))*(DateDiff(FirstDayOfMonth,EndDate,Days)+1),2))
    )
)

Might not be the cleanest solution biut seems to be working.

[PowerRanger]

And to get the DateDiff out of the Collection I use: 

Sum(MyDateTable2,MonthCounter)