Hi Excel Community,
I consider myself an advanced user of Excel but even this formula has me a little stumped. I have a data sheet with various lines of contracts with varying contract term dates (start and end). Three different scenarios - start in 2016 and end in 2017, start in 2017 and end in 2017, start in 2017 and end in 2018.
I need to calculate the number of 2017 months in all scenarios. I think my formula needs to be like this:
If start date year does not equal 2017 and end date year equals 2017 then calculate # of months end date has in 2017.
Otherwise, if start date year does equal 2017 and end date equals 2018 then calculate # of months start date has left in 2017.
Using the formula, I realized that I missed a scenario as it worked for every other date scenario provided. However, when the dates start in the middle of the month like 8/15/17 - 11/15/17 the formula is resulting in 4 months instead of 3 months.
=DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+1
Removing the +1 will correct the formula for this scenario, however, make it incorrect for other scenarios like start date 11/16/16, end date 1/31/17 only counting 2017 months, resulting in 0 months.
O2 - start date
P2 - end date
Any ideas are greatly appreciated.
I consider myself an advanced user of Excel but even this formula has me a little stumped. I have a data sheet with various lines of contracts with varying contract term dates (start and end). Three different scenarios - start in 2016 and end in 2017, start in 2017 and end in 2017, start in 2017 and end in 2018.
I need to calculate the number of 2017 months in all scenarios. I think my formula needs to be like this:
If start date year does not equal 2017 and end date year equals 2017 then calculate # of months end date has in 2017.
Otherwise, if start date year does equal 2017 and end date equals 2018 then calculate # of months start date has left in 2017.
Using the formula, I realized that I missed a scenario as it worked for every other date scenario provided. However, when the dates start in the middle of the month like 8/15/17 - 11/15/17 the formula is resulting in 4 months instead of 3 months.
=DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+1
Removing the +1 will correct the formula for this scenario, however, make it incorrect for other scenarios like start date 11/16/16, end date 1/31/17 only counting 2017 months, resulting in 0 months.
O2 - start date
P2 - end date
Any ideas are greatly appreciated.