6 Replies Latest reply: Aug 10, 2017 3:54 PM by Mona Lk RSS

    Understanding the InMonths function

      I'm having great difficulties understanding the InMonths function. I have read the documentation several times and created simple tests, but I cannot find the logic.I was thinking I could use it to check if a date lies inside a range prior to the current month, ie. now when it's November I'd like to mark prior 3 months including August, September, October. In December I'd like to mark September, October and November etc.


      I could use the following:


      InMonth(MyDate, Now(), -3) or InMonth(MyDate, Now(), -2) or InMonth(MyDate, Now(), -1)


      but IMO that's a very ugly solution and not very reusable when I need to include, say, last 15 months.


      Here's my unsuccessful test:


                If(InMonths(3, MyDate, Now(), -1) , 1, 0) AS 'Prior 3 months',
                If(InMonth(MyDate, Now(), -3) or InMonth(MyDate, Now(), -2) or InMonth(MyDate, Now(), -1), 1, 0) AS 'Wanted result';
                Date(MyDate, 'YYYY-MM-DD') AS MyDate,
                [MyDate, Test
                2012-01-01, One
                2012-02-01, Two
                2012-06-01, A
                2012-06-30, B
                2012-07-01, C
                2012-07-31, D
                2012-08-01, E
                2012-08-31, F
                2012-09-01, G
                2012-09-30, H
                2012-10-01, I
                2012-10-31, J
                2012-11-01, K
                2012-11-14, L
                2012-11-15, M
                2012-11-16, N
                2012-11-31, O
                2012-12-01, P];


      I relied on this function because it worked fine last month. Now it doesn't. Could someone please explain me the logic of InMonths?