0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Calculate the YTD Cost




Issue


I have a spreadsheet listing the different contracts my department has and their values. Each contract has a separate start and end date. For example:
  • Contract 1 on Row #50 has a contract input date of Jan 12, 2010 and Start date of Jan 1, 2010 and end date of Dec. 31, 2010 with a value of $100,000.00
  • Contract 2 on Row # 55 has a contract input date of March 8, 2010, Start Date of April 1, 2010, End Date of March 31, 2013 and a value of $500,000.00


I would like to figure out the YTD cost of each contract in a separate cell. Using the above example:
  • Contract #1 should return a YTD value of $58,333.33 OR ($100,000.00/ 12 months * 7 months)
  • Contract #2 should return a YTD value of $83,333.33 OR ($500,000/ 24 months (term of the contract, difference in start and end dates) * 4 months)

Would you be able to help me with this?

Solution


=((YEAR(TODAY())-YEAR(B2)) * 12 + MONTH(TODAY()) - MONTH(B2) + 1) * D2/((YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)+1)  
B2 : Start Date  
C2: end Date  
D2: amount 

Note that


Solved by rizvisa1
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

This document, titled « Excel - Calculate the YTD Cost », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments