DateDiff in months with decimal digits

(0) Share

Report

Posted on by PowerRanger

3,458 Super User 2024 Season 1

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

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Community Power Pla... on at

Like (0)

Report

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?

Was this reply helpful? Yes No
PowerRanger 3,458 Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

PowerRanger 3,458 Super User 2024 Season 1 on at

Like (2)

Report
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.

Was this reply helpful? Yes No
PowerRanger 3,458 Super User 2024 Season 1 on at

Like (1)

Report

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

Sum(MyDateTable2,MonthCounter)

Was this reply helpful? Yes No
daisaacs 4 on at

Like (0)

Report

So I implemented this exactly as is into my app, and the only issue I'm seeing as that when I add the initials dates in for Both Start and End Date, the DateDiff outputs the correct value.

When When Testing - I update the End date again (by 1 day), and can see the output change correctly (IE: bother the number value and decimal values updated correctly).

However if i do the exact same thing for the Start Date, the number value, changes correctly, but the decimal always stays the same for some reason

Examples

1. Initial Values Set (Where Term in Months is the Date Difference)

2. When I update the Expire Date - the value for Term in Months updates

3. If I revert back - it updates again correctly

4. However no when i update the start Date - only the integer portion of the 'Term in Months' updates NOT the decimal portion ()decimal always remains the same

Example 1
Example 2

Was this reply helpful? Yes No
i-technology 2 on at

Like (0)

Report
Was struggling with this, and finally settled for
var nbrMonths = YEARFRAC(startDate, endDate) * 12 var result = DIVIDE(something, nbrMonths, 0)

Was much easier than i thought 🙂

Was this reply helpful? Yes No