How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

Hi sulax,

Here's my first attempt... I'm assuming (as Melvin asked), it all starts from 1/1/2013...

Also, are the expiration dates only end-of-month dates? what if it's 4/1/2013? Do you want to see 4*375 or 3*375? Or do you want it prorated per day? For example what if it's 4/15/2013? 4.5*375 or 4*375 or 3*375?

Let us know...

Thanks for the replies!

Melvinrobb, I'm trying to find the lease start date. But I think starting from 1/1/2013 is probably a fair assumption. What do you mean what $ value would I want shown?

djapigo, from what I can see it's either an end or beginning of month expiration. So if it was beginning of month, that month would not be included.

From what I can see, what you did is perfect! Thank you so much.

If I need to change the lease start date, what would the process be?

Here you go.

Change this formula in C2: =MIN(IFERROR(DATEDIF(DATE(2012+COLUMN(A$1),1,1),$A2,"m")+1,0),12)*$B2

Then copy to the rest of the cells...

Now I'm getting 0 for every year 1 cost except for the 2019 exp. dates

This is what I have... can I see your new spreadsheet?

It won't let me paste the formulas into the example sheet for some reason, but I have

=MIN(IFERROR(DATEDIF(DATE(2012+COLUMN(F$1),1,1),$F2,"m")+1,0),12)*$G2

for the year 1 cost in H2 where the lease expiration column is F and the lease rate column is G. It looks the same as what you have, but it's not pulling the right numbers

I moved the data in your example sheet over to match the columns that mine were in and it actually stopped working on your sheet...

sulax,

You crack me up... stop changing the columns!

=MIN(IFERROR(DATEDIF(DATE(2012+COLUMN(A$1),1,1),$F2,"m")+1,0),12)*$G2

Make sure the item in bold remain at A$1 for the first entry...

Ah, thanks...I'm still getting used to excel haha.

I really appreciate your help.

No problem... make sure in the future to stick to one sample and don't change the columns and order of things...

Hopefully everything works now...

And thank you for the rep...

Update to this. I now need to figure out how to do this in my spreadsheet for railcars that have a contract that starts in the future. Any ideas?

Can you provide a new spreadsheet and show how you are entering the start date...

I haven't entered a start date yet. The sheet is the same as it was. I can put it in a random column if need be

It's a little messy, but try this...

Note: I gave 2 versions in case how you interpret "Year 1"... does it mean Year 1 = the year the Lease Starts? Or does Year 1 = 2013?

Anyway, I gave both versions...

Let me know if this works for you...

Dennis

Perfect. Thanks!