Forum Discussion
Nested IF Function
I can't figure out why this function is not working.
=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))))))
I get a false response with this and am not sure why. FYI $Q$1 is =EDATE(P1,12) which is today's date plus one year. Can anyone tell me where it went wrong. Based on these data in the reference cells, the outcome should be what is entered in the I-9 Retention Date field (with the exception of Smith, E which should be 1/28/16).
| Employee Name | Hire Date | Hire Date Retention | I-9 Section 1 Complete | I-9 Section 2 Complete | Document Expiration Date | Certification Complete | Document Copies Attached | Termination Date | Termination Date Retention | Rehire Date | Rehire Retention Date | Retermination Date | Retermination Retention Date | I-9 Retention Date |
| Smith, B | 10/7/05 | 10/7/08 | 7/29/17 | 7/29/18 | 3/27/18 | 3/27/21 | 3/28/18 | 3/28/19 | 3/27/2021 | |||||
| Smith, C | 5/22/17 | 5/22/20 | 5/24/17 | 5/24/17 | na | 5/24/17 | 2 | 5/22/2020 | ||||||
| Smith, D | 3/22/06 | 3/22/09 | 1/16/09 | 1/16/10 | 2/9/13 | 2/9/16 | 3/29/2019 | |||||||
| Smith, E | 7/14/03 | 7/14/06 | 11/19/11 | 11/19/12 | 1/28/13 | 1/28/16 | 9/21/13 | 9/21/14 | 3/29/2019 |
3 Replies
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?
