cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerRanger
Resolver II
Resolver II

DateDiff in months with decimal digits

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

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

4 REPLIES 4
HenryARPhillips
Continued Contributor
Continued Contributor

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:

HenryARPhillips_0-1613475048047.png

 



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

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

 

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.

View solution in original post

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

 
Sum(MyDateTable2,MonthCounter)

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (36,438)