Unable to get desired result from formula

[naiku]

I am sure this will turn out to be one of those moments where something is blindingly obvious, but it is late on Friday afternoon, and I can't get this to work.

This is what I want to achieve. I have a spreadsheet with 3 columns, one of these is a job, the next is a user entered date, the next is a calculated date. I want Excel to look at the 2nd date, if this is listed as TBD, I want it to perform a vlookup to some date on another worksheet, then compare the found date to the 1st date, if the 1st date is after the 2nd (looked up) date, I want the cell to show the 1st date. If the 2nd date is after the 1st date, I want the cell to show the 2nd date.

At the moment I am getting no dates, when I know I should be getting some. This is the formula I have entered:

=IF(F4="TBD",(IF(DATEDIF(E4,(VLOOKUP(A4,Sheet4!$A$2:$B$519,2,FALSE)),"D")<0,E4,(IF(F4="TBD",(VLOOKUP(A4,Sheet4!$A$2:$B$519,2,FALSE)),F4)))))

I am sure it is just something like a ( or a comma in the wrong place.

Thanks.

 

[xenou]

There is no need for DateDif() to tell if one date is greater than another. Just ordinary comparison operators will work ( >, >=, =, <=, < )

Perhaps:
=IF(F4="TBD",IF(VLOOKUP(A4,Sheet4!$A$2:$B$519,2,FALSE)>E4,VLOOKUP(A4,Sheet4!$A$2:$B$519,2,FALSE),E4),F4)

But you haven't made it clear what to do if the dates are equal.


If this doesn't work and you are still stuck, give example data to work with, and a sample of the desired result, so someone can more easily help.

ξ

 

[mahesh786]

Thanks for the information!
vashikaran specialist