If Date Earlier than another Date THEN Count Number of Months

[sophia1313]

I am waving the white flag. I need help with an issue in Excel 2007. I have a date in Column S. It is formatted as MM/DD/YYYY (e.g., 7/1/2013). It is being pulled into Column S from another tab using a VLOOKUP. There is another date in Column AE. It is formatted as YYYY-MM (e.g., 2013-03). It too is being pulled in from yet a different tab. Then finally there is a number in Column AF for number of months. I need to determine if the date in AE3 occurs earlier than date in S3. If it does occur earlier then I need to count the number of months between the date in AE3 and the fixed date of 12/31/2013 in (AI3), which is the project end date. I need to do this all in one cell. Thank you!

 

[alfranco17]

There is an article about this topic in:

Calculate the difference between two dates - Excel - Office.com

You could use the following formula (adapted from that site):
=IF(AE3 < S3,12*(2013-YEAR(ae3))+12-MONTH(ae3),"")

I struggled a lot with that < symbol, I learned from Weazel how to post it.

 

[Weazel]

<s3,datedif(ae3,ai3,"m"),"")"< html=""></s3,datedif(ae3,ai3,"m"),"")"<>maybe

=IF(AE3 < S3,DATEDIF(AE3,AI3,"m"),"")

please note that there are spaces around the < sign so it shows up<s3,datedif(ae3,ai3,"m"),"")<s3,datedif(ae3,ai3,"m"),"")[ code]<="" html=""></s3,datedif(ae3,ai3,"m"),"")<s3,datedif(ae3,ai3,"m"),"")[>

 

[sophia1313]

Thank you for the article the solution. I still struggled in understanding it, in the end I used the formula as provided without overthinking it and it worked! Maybe I will give it a few hours and try to figure out exaclty why it worked some other time. Right now I am just happy it did work. Thank you.

 

[sophia1313]

Okay Weazel, this worked and I understand it! Simple. Like me. If not for you guys and the < explanation I would still be trying to figure this out. Thank you!