Hello. I've been trying to figure out a formula to compare three dates, when at times all three are not always present.
#1 - initial
#2 - secondary
#3 - final
There is always an initial and a final and the number of days between needs to be 30 or less. If there is a secondary date present, then the calculation needs to work from the final to the secondary to determine if the number of days is 30 or less. If there isn't a secondary date present that field will contain "N/A".
I've added extra columns and did summary calculations
final - initial = D3
final - secondary = E3
and then wrote the below, but that doesn't include situations when the secondary doesn't exist. It also adds extra columns to the spreadsheet which is undesirable.
=IF(D3>E3,IF(E3>30,"Late","ok"))
Last edited by llamb; 02-02-2012 at 11:51 AM. Reason: (I was just told the secondary date field doesn't contain a date it will contain text "N/A")
Hi
Try this
=IF(AND(LEN(B3)=0,C3-A3>30),"NoInterim, >30",IF(C3-B3>30,"Interim,>30","OK"))
Note that in cells B7 and B9 you have a " ". Make sure you remove that space so that it is completely blank.
rylo
Hi
Try this in cell F3, copied down:
This can handle the N/A in Column B, unless this is the Excel, #N/A error. If it is an error code, not just text, you will need to modify the formula to:=IF(C3-MAX(A3,B3)>30,"Late","ok")
Cheers, Rob.=IF(C3-MAX(A3,IF(ISERROR(B3),0,B3))>30,"Late","ok")
Last edited by rscsmith; 02-02-2012 at 05:13 PM.
Rob! This works like a CHARM!!! Thank you so very much. You've completely saved the day!!Originally Posted by rscsmith
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks