complicated Excel formula

[Lenna]

Please help adjust this formula in N =IF(ISBLANK(L4),IF(TODAY()-I4+1>21,"Overdue","Not Reported"),IF(M4>21,"Overdue",IF(M4=21,"On Time","Early")))
Formula in M = L-I

[TABLE]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Patient Id[/TD]
[TD]MR Number[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Category[/TD]
[TD]Included In Mailing[/TD]
[TD]Log Time[/TD]
[TD]Sample Date[/TD]
[TD]Assigned Date[/TD]
[TD]Test Type[/TD]
[TD]Test Id[/TD]
[TD]ReportDate[/TD]
[TD]Days2SignOff[/TD]
[TD]TAT[/TD]
[TD]Corrected[/TD]
[/TR]
[TR]
[TD]8056[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIDNEY REC[/TD]
[TD]FALSE[/TD]
[TD]1/17/2014[/TD]
[TD]1/16/2014[/TD]
[TD]9/5/2014[/TD]
[TD]SABII[/TD]
[TD]570059[/TD]
[TD]9/6/2014[/TD]
[TD]1[/TD]
[TD]Early[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]17627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIDNEY REC[/TD]
[TD]FALSE[/TD]
[TD]2/18/2014[/TD]
[TD]2/3/2014[/TD]
[TD]6/1/2014[/TD]
[TD]FL__PRAII[/TD]
[TD]575071[/TD]
[TD]9/7/2014[/TD]
[TD]98[/TD]
[TD]Overdue[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]11209[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIDNEY REC[/TD]
[TD]FALSE[/TD]
[TD]3/13/2014[/TD]
[TD]3/13/2014[/TD]
[TD]9/5/2014[/TD]
[TD]LSMII[/TD]
[TD]579540[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]11209[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIDNEY REC[/TD]
[TD]FALSE[/TD]
[TD]3/13/2014[/TD]
[TD]3/13/2014[/TD]
[TD]6/1/2014[/TD]
[TD]LSMI[/TD]
[TD]579539[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

I need help with the third and the fourth row scenario… (third row) Result was not reported(L is blank) and test is not overdue yet(L-I<21 days)..should return(Not Reported & Not OverDue)

And (forth row) result was not reported (L is Blank) and test is overdue(L-I >21 days)... should return(Not Reported & OverDue)

If result was not reported today's date is used to calculate Days2SignOff.


Thanks

 

[Matei]

Hi Lenna, Not sure if I followed the description correctly as I see you had all the conditions set up correctly, you just needed to update the text results.
This is what I changed it to...

=IF(ISBLANK(L4),IF(TODAY()-I4+1>21,"Not Reported & Overdue","Not Reported & Not Overdue"),IF(M4>21,"Overdue",IF(M4=21,"On Time","Early")))


Let me know if this is what you need.
Matei.

 

[Lenna]

Thanks