Month between 2 dates calculation
I am looking for some help on a formula. I have a loan with an origination date and a maturity date - they are both in Date Field format I am looking to extract the number of whole months between...
Forum Discussion
OK, right, you wanted month and I gave you years.
I did not re-test this formula, but see if it works
'var date SD=[start date];
var date ED=[end date];
(Year($ED)-Year($SD)-1)*12 // start off with 12 times the number of fully completed years
+If(Month($ED)>=Month($SD),12,0) //add 12 months if the last year was fully completed
+ If((Month($ED)>=Month($SD)),Month($ED)-Month($SD),12+Month($ED)-Month($SD)) // add the number of months between the month numbers.
-If(Day($ED)<Day($SD),1,0) // but if the day did not catch up to the start date's day number, then subtract 1.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
I did not re-test this formula, but see if it works
'var date SD=[start date];
var date ED=[end date];
(Year($ED)-Year($SD)-1)*12 // start off with 12 times the number of fully completed years
+If(Month($ED)>=Month($SD),12,0) //add 12 months if the last year was fully completed
+ If((Month($ED)>=Month($SD)),Month($ED)-Month($SD),12+Month($ED)-Month($SD)) // add the number of months between the month numbers.
-If(Day($ED)<Day($SD),1,0) // but if the day did not catch up to the start date's day number, then subtract 1.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
(((12*Year([Maturity Date]))+Month([Maturity Date]))-((12*Year([Origination Date]))+Month([Origination Date])))
This seems to accomplish the correct output also.
Thanks.
------------------------------
Eric Fixler
------------------------------
This seems to accomplish the correct output also.
Thanks.
------------------------------
Eric Fixler
------------------------------
