Excel Formula and date calculations

[javiUSAF]

Formula help!
Two columns: D and E may or may not be populated with dates. COlumn F is supposed have the difference between the dates in columns D and E.
The task is as follows:
If date in (E2) is LESS than 10 days of (D2), (G2) should be marked "COMPLETE"
If date in (E2) is MORE than 10 days of (D2), (G2) should be marked "cOMPLETED LATE"
If there is no date in (E2) and Date in (D2) is within 10 days of today, (G2) should be marked "OPEN"
If there is no date in (E2) and Date in (D2) is PAST 10 days from today, (G2) should be marked "overdue"
If there are no dates in both (E2) and (D2), (G2) should be marked "not processed"
My 1st problem is Column F with the difference in days. I did a simple E2-D2 function but this presents a problem when calculating the difference when
there is no date in column E or in D (sometimes there is a Finish date in E but no start date in D). I get a negative number of days and of course a
zero result for when there are no dates on both E and D cells.
My 2nd problem (and this is dependent on the results of the G column), is the formula on the (F) "Days" column. I've started with the following but need help.
=IF(ISBLANK(D2:F2),"Null",IF(F2<-10,"overdue",IF(F2<=10,"complete",IF(F2>10,"completed Late"))))
Below is a sample of the data I have to work with (columns D (start date) and E (finish date) are from raw data) Columns F (days) and G(status) I created.
|Start date|--|Fin Date| --- |Days| --- |Status|
|1/8/2008| -- |1/31/2008| --|23 |-----|completed Late| <---shows correctly
|1/28/2008| - |2/7/2008| --- |10 |---- |complete| <--shows correctly
|2/8/2008| -- |no fin date|-- |-39486| --|overdue| <--it is correct but if the date was <10 shows overdue
|6/20/2011| --|no fin date|--|-40683| --|overdue| <--within 10 days, should be marked "Open"
|no strt dat|--|no fin date|---|0| ----- |complete| <--no dates, need it to be marked "not processed"
|6/16/2008| --|6/16/2008| --|0|------ |complete| <---shows correctly
|no strt dat|--|3/10/2011| --|40612| -|completed Late| <---no date on start date should be marked "complete"

Hope you can help. Thank you!

 

[jeffreybrown]

Try...

=LOOKUP(SIGN(E2-D2),{-1,0,1},{"Overdue","Complete","Complete Late"})

Edit: Sorry just read again about your other requirements so I'll re-attack.

 

[jeffreybrown]

Here is the best I could come up with.

G2=IF(TODAY()-D2<=10,"Open",IF(AND(F2>0,F2<=10),"Complete",IF(COUNT(D2:E2)=0,"Not Processed",LOOKUP(SIGN(F2),{-1,0,1},{"Overdue","Complete","Complete Late"}))))

<TABLE style="WIDTH: 274pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=364><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2880" width=90><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2528" width=79><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 1952" width=61><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4288" width=134><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=90>Start Date

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=79>Fin Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=61>Days</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 101pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=134>Status</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1/8/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1/31/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Complete Late</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1/28/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2/7/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Complete</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>2/8/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>-39486</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Overdue</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>6/20/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>-40714</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Open</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Not Processed</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>6/16/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>6/16/2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Complete</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>3/10/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>40612</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Complete Late</TD></TR></TBODY></TABLE>

 

[javiUSAF]

Thanks so much for the quick reply, i'll take a stab at it again when I get back to work tomorrow.

 

[javiUSAF]

Jeff, that was genius! It worked perfect, thank you thank you thank you.

 

[jeffreybrown]

You're welcome...glad it worked for you and thanks for the feedback

 

[nimesh29]

Here is the best I could come up with.

G2=IF(TODAY()-D2<=10,"Open",IF(AND(F2>0,F2<=10),"Complete",IF(COUNT(D2:E2)=0,"Not Processed",LOOKUP(SIGN(F2),{-1,0,1},{"Overdue","Complete","Complete Late"}))))

[TABLE="width: 274"]
<colgroup><col style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2880" width="90"><col style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2528" width="79"><col style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 1952" width="61"><col style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4288" width="134"></colgroup><tbody>[TR]
[TD="class: xl63, width: 90, bgcolor: transparent"]Start Date

[/TD]
[TD="class: xl64, width: 79, bgcolor: transparent"]Fin Date[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent"]Days[/TD]
[TD="class: xl64, width: 134, bgcolor: transparent"]Status[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1/8/2008[/TD]
[TD="class: xl66, bgcolor: transparent"]1/31/2008[/TD]
[TD="class: xl64, bgcolor: transparent"]23[/TD]
[TD="class: xl67, bgcolor: transparent"]Complete Late[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1/28/2008[/TD]
[TD="class: xl66, bgcolor: transparent"]2/7/2008[/TD]
[TD="class: xl64, bgcolor: transparent"]10[/TD]
[TD="class: xl67, bgcolor: transparent"]Complete[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2/8/2008[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]-39486[/TD]
[TD="class: xl67, bgcolor: transparent"]Overdue[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]6/20/2011[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]-40714[/TD]
[TD="class: xl67, bgcolor: transparent"]Open[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]Not Processed[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]6/16/2008[/TD]
[TD="class: xl66, bgcolor: transparent"]6/16/2008[/TD]
[TD="class: xl64, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]Complete[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]3/10/2011[/TD]
[TD="class: xl64, bgcolor: transparent"]40612[/TD]
[TD="class: xl67, bgcolor: transparent"]Complete Late[/TD]
[/TR]
</tbody>[/TABLE]

Jeff, I am trying to work out similar formula. I have to maintain a log for when we receive/ due/ days/ return date/ status; formula is very similar to one above that you created but, i am trying to get my status to reflect based on 'Return Date' (complete, complete late, overdue or open (based on due date)).
Thank you advance for you help.