Help needed with IF formula

[TomP1988]

Hi all

I've been struggling with how to structure this formula for a while now and cannot quite figure it out and get it working correctly.

I have a 'Start Date' field - M6
I have a 'Initial Finish Date' field - O6
I have 'Current Planned Finish' - P6 and
I have 'Forecast Finish' - Q6

Column R is the Duration of each Project. In Cell R6, I would like to return the number of months between the Start Date (M6) and the Forecast Finish (Q6), but...

If Q6 is blank, return the number of months between the Start Date (M6) and the Current Planned Finish (P6) and..

If P6 is blank, return the number of months between the Start Date (M6) and the Initial Finish Date (O6)...

The formula I'm using to work out the duration between whichever dates is...
=(YEAR(EndDate)-YEAR(StartDate))*12+MONTH(EndDate)-MONTH(StartDate)+1 (I've used the +1 because all the start dates tend to be the 1st of the month and all the end dates tend to be the end of the month, so without it 01/01/14 to 31/01/14 would pull back a value of zero).

Really appreciate anyone's help.

Thanks
Tom

[Jonmo1]

Try

=DATEDIF(M6,LOOKUP(3000000,O6:Q6),"M")+1

[Sam Capricci]

here is what I came up with =IF(Q6<>"",DATEDIF(M6,Q6,"m"),IF(P6<>"",DATEDIF(M6,P6,"m"),DATEDIF(M6,O6,"m")))
limited testing but it appears to work.

[TomP1988]

Haha wow! That is impressive! Thank you!

How is it working? What does the 3000000 do?

Cheers
Tom

[pjwhitfield]

Ive gone with a different approach, involving a hidden column (S) containing:

=IF(ISNUMBER(Q6),Q6,IF(ISNUMBER(P6),P6,R6))

then in R6 I have:
= ((YEAR(S6) - YEAR(M6)) * 12) + MONTH(S6) - MONTH(M6)

[Jonmo1]

You're welcome..

This part
LOOKUP(3000000,O6:Q6)
Finds the cell furthest to the right with a Date in it.
The 3000000 is just used as number garanteed larger than any date in the range O6:Q6

The largest date allowed in excel is 12/31/9999
In the numerical value for that date is 2958465 (I just rounded up to a nice even 3 million)

[pjwhitfield]

You're welcome..

This part
LOOKUP(3000000,O6:Q6)
Finds the cell furthest to the right with a Date in it.
The 3000000 is just used as number garanteed larger than any date in the range O6:Q6

The largest date allowed in excel is 12/31/9999
In the numerical value for that date is 2958465 (I just rounded up to a nice even 3 million)

Loving that idea, if I may say so its genius.