Sorry for delayed reply @TheRobRush
I hadn't considered using DateAdd. In essence the problem occurs because we don't know whether the DateDiff has rounded up or down, building on your suggestion, this is the solution I've found
Explanation:
DateDiff can be used to find the difference between start and end date in months, which could be rounded up or down by the function.
To find out whether it was rounded up or down:
DateAdd can add the number of months returned by DateDiff to the start date to work out what the end date was rounded to
These 2 end dates can be compared using DateDiff (Days) to show whether it was rounded up or down.
If the difference in days is negative then the first datediff function rounded the months down, and we need to add 1 month to the first DateDiff calculation to have the effect of rounding months up
If the difference in days is positive then the first datediff function rounded the months up.
In the app:
I have 2 date selectors, named StartDate and EndDate, and a button with the on select value:
Set(
NumMonths,
DateDiff(
StartDate,
EndDate,
Months
)
);
Set(
RoundedDate,
DateAdd(
StartDate.SelectedDate,
NumMonths,
Months
)
);
Set(
MonthsToAdd,
If(
DateDiff(
EndDate,
RoundedDate,
Days
) > 0,
"0",
"1"
)
);
Set(
MonthsRoundedUp,
NumMonths + MonthsToAdd
)
The MonthsRoundedUp variable contains the answer.
