You’re offline. This is a read only version of the page.
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
Was struggling with this, and finally settled for
Share
Report
All responses (
Answers (var nbrMonths = YEARFRAC(startDate, endDate) * 12
var result = DIVIDE(something, nbrMonths, 0)
Was much easier than i thought 🙂
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
And to get the DateDiff out of the Collection I use:
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.
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,
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?
#1
WarrenBelz
146,518
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,751
Most Valuable Professional
