Forum Discussion
Nested IF Function
I do not know how this whole logic works but breifly looking at your formula. your MAX is missing a closing bracket. besides your logical tests in formulas all pass to FALSE to trigger the last part of the formula which is MAX(N3,L3) and that you need to check. if your values are not number MAX formula will return error or 0
=IF(A3="","",IF(AND(ISBLANK(N3),ISBLANK(L3),ISBLANK(J3)),MAX(C3,$Q$1),IF(AND(ISBLANK(N3),ISBLANK(L3)),MAX(C3,J3),IF(ISBLANK(N3),MAX(L3,$Q$1),MAX(N3,L3)))))
Yes!!! That helped but now some of the dates it is giving are not right. Basically I am trying to get the greatest date. If an employee was hired but not termed that date should be the greatest of hire retention date or today plus one year ($Q$1). If the employee was termed, it should be the greatest of hire retention date or term retention date. If he was rehired, greatest of rehire retention and $Q$1. If retermed, the greatest of rehire retention and reterm retention. It is still not giving me that on all rows.
It is giving me the correct dates if there has been at least one termination date and one rehire date. If there is no termination or rehire date the formula for enter the greatest of hire date and today plus one year is not coming out right. It is only giving me 1/0/1900. I didn't change anything more than adding the parenthesis you suggested.
- Can you upload the sample table you have pasted as screenshot?
