Date Calculations

[smarynuk]

Hello,

Can someone help me with this. I have a spreadsheet to track vehicle buyouts, each vehicle has a row and the columns would look something like this;

Vehicle Number, Start Date, End Date, Monthly Amount, Term

00698 - Ford, 1-Sep-10, 1-Sep-14, $1,238.16, 48

What I would like to do for each vehicle is add 2 new columns with Remaining Months and Buyout where the remaining months is calculated each time the spreadsheet is opened and the buyout is simply calculated by using the remaining months multiplied by the monthly amount.

I keep running into trouble with the date calculations.

Thanks

 

[Gerald Higgins]

Is "Remaining Months" to be constantly recalculated using today's date ?

How do you deal with part months ?

And what exactly should the results be for your example, if today's date is 5th Feb 2012 ?

 

[shawnhet]

You should be able to use something like this -

=(YEAR(C1)-YEAR(TODAY()))*12+(MONTH(C1)-MONTH(TODAY()))

However, be advised that this expression relies on the change on the calendar month not the number of days. For instance, a current date of February 29th that ends on March 1 will give a number of months as 1 whereas a current date of February 5th with an end date of February 29th will give a number of months as 0.

This may not be what you are looking for, but it can be fixed if necessary.

Cheers,