Trying to Compute Months between two Dates

[VGC_Innovation]

I used these formula to calculate number of months between two dates.
Whereas,
Start Date - LastPrincipalChangeDate
End Date - DateofTransaction

Expand for the Expression Used

IF(YEAR([LastPrincipalChangeDate])=YEAR([DateofTransaction]),
MONTH([DateofTransaction])-MONTH([LastPrincipalChangeDate]),

IF((YEAR([LastPrincipalChangeDate])+1)=YEAR([DateofTransaction]),
(12-MONTH([LastPrincipalChangeDate]))+MONTH([DateofTransaction]),

(12-MONTH([LastPrincipalChangeDate]))+MONTH([DateofTransaction])+
((YEAR([DateofTransaction])-YEAR([LastPrincipalChangeDate])-1)*12)
))
+
IF(
Day([LastPrincipalChangeDate])<=DAY([DateofTransaction]),
,0,-1)

Then this pops-up, Can anyone enlighten me?

When I tried to put the IF statements in different VCs, it seems Appsheet won’t allow me to multiply the difference in years by 12.

Appreciate anyone’s response

Regards,
Edison

[Steve]

Perhaps not significant, there’s an extraneous comma:

[Steve]

VGC_Innovation:

When I tried to put the IF statements in different VCs, it seems Appsheet won’t allow me to multiply the difference in years by 12.

That suggests you tried just the following in a virtual column?

((YEAR([DateofTransaction])-YEAR([LastPrincipalChangeDate])-1)*12)

If not, would you?

[VGC_Innovation]

Hi Steve!

Thanks for showing your interest on my question! Appreciate it a lot.

Steve:

That suggests you tried just the following in a virtual column?

((YEAR([DateofTransaction])-YEAR([LastPrincipalChangeDate])-1)*12)

If not, would you?

Yes I did that, and I feel like that seems to be the problem.

Steve:

Or just try this ( TEST THOROUGHLY!!! 😞

(
  ((year([DateofTransaction]) * 12) + month([DateofTransaction]))
  - ((year([LastPrincipalChangeDate]) * 12) + month([LastPrincipalChangeDate]))
)

Amazing that you’ve come up with a shorter formula! However,
when I tried it out, it still gives me the same error. I dissected your formula, and found out that (please expand each bullet):

Straight multiplication to a YEAR(Date) was ok. No error in the apps

But subtracting two value results of a multiplied YEAR() gives me the app error.

On the other hand, adding two value results of a multiplied YEAR() works, Why then does it not work in subtraction?

• (sorry I can’t show you the app view on the side as it contains sensitive info.)
  
  

When I try to input a text with a date format manually, the subtracting them works

But then again, when I try to convert my Dates to TEXT just to mimic the above formula, The app gets an error. Tho the expression says it's not erroneous. The App says error again.

• 

And to add some context, I’m not sure if these are factors for the error?

DateofTransaction is an actual Column in my database, Date Column Type and not DateTime

• Auto Compute doesn’t have any formula, except for initial value - TODAY()
  
  

LastPrincipalChangeDate is a Virtual Column, Date Column Type and not DateTime (computed by getting the max Date of a specific table, but should be lower than DateofTransac)

• All else doesn’t have anything
  
  

This is the Info of the VC using the calculation

I’m just concerned the fact that, there’s nothing in the INFO > ERRORS to guide me on how I can troubleshoot this issue when I get that error in my app.
And that, the expression builder seems to tell me that, my expressions are not erroneous.

Maybe this is a bug?

Or it would really be helpful if anyone can give me an alternative formula to compute for months. I tried some month calculation formula in this forum, but were not working as I intended (e.g. some only limits it’s count to a year)

[Steve]

I suspect the problem is related to LastPrincipalChangeDate being a virtual column. Please provide a screenshot of its complete app formula expression.

[VGC_Innovation]

SCREENSHOT of the Expression builder in the LastPrincipalChangeDate

EXPAND FOR THE EXPRESSION USED

MAX(

SELECT(Daily Loan Transaction[DateofTransaction],
AND(
[PNNo.]=[_THISROW].[PNNo.],
[DateofTransaction]<[_THISROW].[DateofTransaction],
OR(NOT([LoanReceivable]=0),ISNOTBLANK([LoanReceivable]))
))
+
LIST([PNNo.].[DateAvailed])
)

Basically, what I’m trying to compute here is to get the max date in the same table , with the following condition:

• the filtered table should be lower than the transaction date of the current row.
• the filtered table should not have records with LoanReceivable column valued at 0. Meaning there was a transaction for the Loan Receivable either an increase or decrease in LoanReceivable.
• the filtered table should have the same ParentKey (PNNo.) with the current row.

Then after those are filtered, get the MAX date. together with the Date Availed under the Parent Table (in cases, there’s no record under the filtered table.

Since you’ve mentioned it, I tried using ANY instead of MAX and it worked. Maybe then, I’m using MAX expression incorrectly? although the expression builder doesnt meantion of any errors.

Although, I'm not sure if the error lies in LastPrincipalChangeDate alone. ,I tried TODAY() instead of DateofTransaction. Just to see if LastPrincipalChangeDate is the error. And when I did that, it works, and there was no error... so I don't know already haha.

Thank you for taking the time to solve this with me.

[Steve]

VGC_Innovation:

Thank you for taking the time to solve this with me.

So it’s working as you want?

[VGC_Innovation]

Hi steve,

No not yet, was just appreciating your effort to help me out until now.

[Steve]

Going back here, what happens when you click the Test button?

[VGC_Innovation]

It's the same error showing

And when I click that ? button, it leads me nowhere in the Errors and Warnings, just plain page.

[Steve]

VGC_Innovation:

It’s the same error showing

Please do the following:

1. Create a virtual column with an app formula of YEAR([DateofTransaction]). Use the Test button and review the results for unexpected values. Do not Save the expression or leave Expression Assistant.

2. Replace the expression from (1) with YEAR([LastPrincipalChangeDate]). Again, use the Test button and review the results for oddities. Do not leave Expression Assistant.

3. Repeat the process again with the expression YEAR([DateofTransaction]) * 12.

4. And repeat again with the expression YEAR([LastPrincipalChangeDate]) * 12.

5. Press the Cancel button to leave Expression Assistant.

6. Delete this new virtual column–it was only for testing.

Stop at the first step that produces unexpected results and post a screenshot that demonstrates those results.

VGC_Innovation:

And when I click that ? button, it leads me nowhere in the Errors and Warnings, just plain page.

Yeah… That’s not surprising…

[VGC_Innovation · Accepted solution]

Steve:

Please do the following:

1. Create a virtual column with an app formula of YEAR([DateofTransaction]) . Use the Test button and review the results for unexpected values. Do not Save the expression or leave Expression Assistant.
2. Replace the expression from (1) with YEAR([LastPrincipalChangeDate]) . Again, use the Test button and review the results for oddities. Do not leave Expression Assistant.
3. Repeat the process again with the expression YEAR([DateofTransaction]) * 12 .
4. And repeat again with the expression YEAR([LastPrincipalChangeDate]) * 12 .
5. Press the Cancel button to leave Expression Assistant.
6. Delete this new virtual column–it was only for testing.

Nothing wrong with the above, but, OH MY GOODNESS, Finally Figured it out! The problem was not the Formula. But the other VCs referring to the VC containing the formula. My bad, I went all through this trouble just to find that out.

To sum things up, I used your shorter expression and added some expressions to consider the day of the months.

(
((year([DateofTransaction]) * 12) + month([DateofTransaction])) - ((year([LastPrincipalChangeDate]) * 12) + month([LastPrincipalChangeDate]))
)
+IF(
OR(
Day([LastPrincipalChangeDate])<=DAY([DateofTransaction]),
AND(MONTH([LastPrincipalChangeDate])=MONTH([DateofTransaction]),
YEAR([LastPrincipalChangeDate])=YEAR([DateofTransaction]))
)
,
0,
-1)

FINALLY, it’s now resolved. Sorry for the trouble I caused you, and thanks for your help Steve! Appreciate it very much!

Well at least, I get to learn a shorter expression from you! Haha

[Steve]

OUT-FRIKKIN’-STANDING!!! Well done!

[Steve]

For my own clarity, I reformatted your expression. I’m including it here just for my reference:

(
  IF(
    (
      YEAR([LastPrincipalChangeDate])
      = YEAR([DateofTransaction])
    ),
    (
      MONTH([DateofTransaction])
      - MONTH([LastPrincipalChangeDate])
    ),
    IF(
      (
        (YEAR([LastPrincipalChangeDate]) + 1)
        = YEAR([DateofTransaction])
      ),
      (
        (12 - MONTH([LastPrincipalChangeDate]))
        + MONTH([DateofTransaction])
      ),
      (
        (12 - MONTH([LastPrincipalChangeDate]))
        + MONTH([DateofTransaction])
        + (
          (
            YEAR([DateofTransaction])
            - YEAR([LastPrincipalChangeDate])
            - 1
          )
          * 12
        )
      )
    )
  )
  + IF(
    (
      DAY([LastPrincipalChangeDate])
      <= DAY([DateofTransaction])
    ),
    ,
    0,
    -1
  )
)

[Steve]

Or just try this (TEST THOROUGHLY!!!😞

(
  ((year([DateofTransaction]) * 12) + month([DateofTransaction]))
  - ((year([LastPrincipalChangeDate]) * 12) + month([LastPrincipalChangeDate]))
)