Topic: Date calculation in Calculated Fields

Hey, I have a table with the date the loan was taken and a column with number of months Interest was paid. I want a calculated field which shows me how many months are remaining. I can't even get past getting the current month number in a calculated field number using SELECT strftime('%m', pt_date)   (and pt_date is the name of the column in the table.)

I just need something like ((year(today) - year(pt_date)) * 12 ) + (month(today) - month(pt_date)) which will give me the total number of months, from which I can subtract the number of months it has been paid which is in another  column.

Thank you!

2 (edited by brian.zaballa 2020-10-29 13:14:14)

Re: Date calculation in Calculated Fields

dhekabai wrote:

Hey, I have a table with the date the loan was taken and a column with number of months Interest was paid. I want a calculated field which shows me how many months are remaining. I can't even get past getting the current month number in a calculated field number using SELECT strftime('%m', pt_date)   (and pt_date is the name of the column in the table.)

I just need something like ((year(today) - year(pt_date)) * 12 ) + (month(today) - month(pt_date)) which will give me the total number of months, from which I can subtract the number of months it has been paid which is in another  column.

Thank you!

try casting it

((cast(strftime('%Y',DATE('now')) as integer) - cast(strftime('%Y',pt_date) as integer))*12) + 
((cast(strftime('%m',DATE('now')) as integer) - cast(strftime('%m',pt_date) as integer)))
brian

3 (edited by derek 2020-10-30 11:44:03)

Re: Date calculation in Calculated Fields

Hi,
Might not be exactly the calculation you're after (and I've not worried about specific days within a month etc) but the attached might give you an idea of how you could approach it.
That said, I'd probably use 2 tables - one table for the loan itself (start and end dates) and one (related) table for the individual repayments, in which case the calculation of payments made, payments outstanding etc would be done completely differently.
Anyway, hope this helps,
Derek.

Post's attachments

Attachment icon numberofmonths.zip 337.4 kb, 232 downloads since 2020-10-30