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

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

Hi,

I'm making a spreadsheet that needs to figure out the yearly cost for a given rail car that is being leased. I have a column F that is the lease expiration date (i.e. 9/30/2013) and a column G that is the lease rate (i.e. \$375). The next 7 columns (H-N) are labeled Year 1 Cost, Year 2 Cost, etc. My question is, how do I put a formula in those cells that will calculate the yearly cost based on the number of months that the lease will exist in that year? So for 9/30/2013 the column for year 1 cost would have to recognize that there would only be 9 months in that year. Then the column for year 2 cost would have to recognize that it had already expired in 2013, so it would be 0.

Any help would be enormously appreciated. Thanks!  Register To Reply

2. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

Do you have an entry for the lease start date? in the example given what would be the lease start date, and what \$ value would you want to have shown for your example in "year 1", "year 2", etc.......  Register To Reply

3. ## Re: 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...  Register To Reply

4. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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?  Register To Reply

5. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

djapigo,

When I apply your formulas to my spreadsheet, the values are coming up wrong. For example, I have a car where the lease rate is 750/month and the expiration is 5/31/2013, but it's coming up as 4500 for the first year (rather than 3750) then 3900 for the second year (rather than 0). Any ideas?  Register To Reply

6. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

you can't copy the formula exactly... study it and see where you have to change it...

Can you send a sample spreadsheet so that we can help align the formulas correctly...

Don't send sensitive info... change the data to be more generic...

repost: click on Go Advanced... then Manage Attachment...  Register To Reply

7. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

Here you go.  Register To Reply

8. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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...  Register To Reply

9. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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

10. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost  Register To Reply

11. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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  Register To Reply

12. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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...  Register To Reply

13. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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...  Register To Reply

14. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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

15. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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...  Register To Reply

16. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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?  Register To Reply

17. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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

18. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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  Register To Reply

19. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

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  Register To Reply

20. ## Re: How to Calculate the Number of Months From a Given Date and Apply a Monthly Cost

Perfect. Thanks!  Register To Reply