Date diff round up

[Torque]

Hi 

Is it possible to use date diff to consistently round up to the nearest full month?

e.g. 1 month and 1 day  = 2 months, and 1 month and 25 days  = 2 months

I have tried to find work arounds, such as by seeing whether the day value of the second date is before or after the first, however I've run into issues due to local date format being dd/mm/yyyy (and not mm/dd/yyyy)

Has anyone worked out if or how this can be done?

[TheRobRush]

Because months are not all the same length it would get pretty complex and involve something like calculating first the difference between current date selected and same day of mext month and between that and the same day of next month etc on and on before running the formula to determin difference in days between two selections and comparing that to each group of number of days then if its greater than group 1, 1, and 2, 2, greater than 2 but not 3, 3 and stop there.

Probably better off selecting a set number of days ie) 30 and roundup a MOD of the difference in days & and your preset value for a month

[Torque]

Thanks @TheRobRush for your reply,

I'm unsure what you mean by MOD, are you suggesting I use date diff for days and then divide by 30 and then add a month on top of that?

I figure there's no way of getting the inbuilt date diff using months to just not round at all?

[TheRobRush]

Sorry kind of said that wrong it would be aomwthing like

If(

Mod(DateDiff Value,30)=0, datediffvalue/30,

Sum(datediffvalue/30,1))

On my phone so not sure if syntax is exactly right but mod checkk will see if difference is exactly a multiple of 30 ie 30 60 90 1/2/3 months. If it is just returns that, if not then its between a multiple of 30 so returns the number of months +1

[Torque]

Right, I get you, using that approach the length of February would be an issue as if you have something from 1st Feb to the 2nd March, you've had it for 30 days so formula would say 1 month, but rounded up that should count as 2 months, however reducing the multiple from 30 to 28 would essentially add an extra month to the year so would quickly end up with incorrect values. 

[TheRobRush]

What exactly is the purpose of the calculation, might be better to start there to maybe figure out a better approach

[TheRobRush]

Ok didn't get a response so went ahead and whipped this up when I got to work.

You have a button here, and 2 date selectors. Day 1 & Day 2.

Day 1 is start of range, Day 2 is the End. 

Button has an on select of

ClearCollect(PartialMonthRange,
RemoveIf(    
    ForAll(
            Sequence(
                    DateDiff(
		///SET START DATE
                            'Day 1'.SelectedDate,
        ///SET END DATE
                            'Day 2'.SelectedDate,
        ///RETURN DIFFERENCE IN DAYS
                            Days
                            )
                    ),

        If(
            Or(
            ///Is the date we just passed the same day of month as start?
            Day(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=Day('Day 1'.SelectedDate),
            //Is it the last day in February and is the start date greater than 28?
            And(Day(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=28, Month(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=2,Day('Day 1'.SelectedDate)>28),
            //Is it a 30 day month and is the start day greater than 30?
            And(Day(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=30, Or(Month(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=4,Month(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=6,Month(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=9,Month(DateAdd('Day 1'.SelectedDate,ThisRecord.Value))=11),Day('Day 1'.SelectedDate)>30) 
            ),
            ///Add this Date to our collection
            DateAdd('Day 1'.SelectedDate,ThisRecord.Value))
),
//Remove all the blank records
Value=Blank()
));

///Check the colleciton we just created vs our original End day. If the end day is past the collectiosn last day then add 1 more to the collection as it is a partial month
If(DateDiff(First(Sort(PartialMonthRange,Value,Descending)).Value,'Day 2'.SelectedDate,Days)>0,
    Collect(PartialMonthRange,'Day 2'.SelectedDate))

Comments in the code tell you what does what, and to get the month count after pressing the button just do a countrows of your new PartialMonthRange collection

[Torque]

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.

[TheRobRush]

The example I gave you will always round up. Because its not rounding really its counting a range bit by bit. It even has allowances built in to calculate properly on months of 28,29, and 30 days. When it gets to the final date to check if this is even one day higher than the previous date COLLECTED (a 1 month marker) then it will add 1 extra month to the total it has collected. So you dont have to worry about which way it is rounding in my formula, because there is no rounding occuring