Formula to Make Overdue date record highlighted in field

[ferranti]

I have created a spreadsheet for samples In and Out of our shop. I have the following fields:-

<table x:str="" style="border-collapse: collapse; width: 342pt;" width="455" border="0" cellpadding="0" cellspacing="0"><col style="width: 67pt;" width="89"> <col style="width: 81pt;" width="108"> <col style="width: 87pt;" width="116"> <col style="width: 107pt;" width="142"> <tbody><tr style="height: 24.75pt;" height="33"> <td class="xl24" style="height: 24.75pt; width: 67pt;" width="89" height="33">Loan Date</td> <td class="xl24" style="width: 81pt;" width="108">Return Due Date</td> <td class="xl24" style="width: 87pt;" width="116">Overdue by x days</td> <td class="xl24" style="width: 107pt;" width="142">Date Sample Returned</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" x:num="40391" align="right" height="17">01/08/2010</td> <td class="xl23" x:num="40428" align="right">07/09/2010</td> <td class="xl23">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" x:num="40360" align="right" height="17">01/07/2010</td> <td class="xl23" x:num="40397" align="right">07/08/2010</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" x:num="40393" align="right" height="17">03/08/2010</td> <td class="xl23" x:num="40400" align="right">10/08/2010</td> <td>
</td> <td>
</td> </tr> </tbody></table>
I am hoping someone can suggest the best way to work this in excel.

I basically input manually the Loan Date and then eg. put in the Return Due Date eg. 7 days later (also manually). I would like the whole line to read red and in bold when the return date is passed (showing sample return is overdue).

I would also like the Overdue by X days field to actually put in the number of days the item is overdue. Of course, once the Date Sample Returned is filled in, it will no longer be highlighted in Red.

I know this seems long-winded.. so would be grateful for suggestions.
Many thanks, Al

 

[VoG]

Perhaps

Excel Workbook
	A	B	C	D
1	Loan Date	Return Due Date	Overdue by x days	Date Sample Returned
2	01/08/2010	07/09/2010	-30
3	01/07/2010	07/08/2010	1
4	03/08/2010	10/08/2010	-2
5	03/08/2010	11/08/2010	-3	09/08/2010
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(C1<0,D1="")	Abc
D2	1. / Formula is =AND(C1<0,D1="")	Abc

 

[Fowmy]

May this one:

Mark the whole Data area and give this as the Conditional formating "=$C3<>0"

Excel Workbook
	A	B	C	D
2	Loan Date	Return Due Date	Overdue by x days	Date Sample Returned
3	01-08-10	07-09-10	0	06-09-10
4	01-07-10	07-08-10	1
5	03-08-10	10-08-10	0	12-09-10
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C3	=IF(D3="",MAX(TODAY()-B3,0),0)
C4	=IF(D4="",MAX(TODAY()-B4,0),0)
C5	=IF(D5="",MAX(TODAY()-B5,0),0)

 

[ferranti]

Thank you very much for your help. May have some further requests down the line.
Many thanks, A

 

[ferranti]

Thank you very much for your help.
Many thanks, A

 

[JennGraham]

[TABLE="width: 63"]
<colgroup><col width="63" style="width: 47pt;">
<tbody>
</tbody>[/TABLE]
How about taking Fowmy's worksheet formula one step further, to add another field "Extended to" date. In this sample, the loan date, Date Returned and Extended Date are entered manually, the Return Due Date is calculated (=IF(A2="",,"",Edate(A2,1))). I'm stuck on getting the Days Overdue to consistently return a valid result on each test. Days Overdue should be the number of days from TODAY since either the due date or the extended date, if there is one entered.

In the sample below, I tried the formula =IF(E2="",MAX(TODAY()-B2,TODAY()-E2) as a start. TODAY() is February 2, 2017 in reality.

Of course, this overly simple formula fails on C3 since Date Return was not tested. I'm quickly lost when it comes to nesting IF's, and after hours of chugging away at this I'm lost. Any suggestions? If possible, I'd prefer to keep this clean, with cells filled only if overdue.
[TABLE="width: 391"]
<colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="114" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4070;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3046;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 3942;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3993;"> <tbody>[TR]
[TD="class: xl67, width: 100"]Loan Date
YYY/MM/DD[/TD]
[TD="class: xl68, width: 114"]Return Due Date
YYY/MM/DD[/TD]
[TD="class: xl69, width: 85"]Days Overdue[/TD]
[TD="class: xl70, width: 110"]Date Returned
YYYY/MM/DD[/TD]
[TD="class: xl71, width: 112"]Extended Date
YYY/MM/DD[/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]2017/02/02[/TD]
[TD="class: xl65, align: right"]2017/03/02[/TD]
[TD="class: xl73, width: 85, align: right"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl74"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]2016/11/02[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2016/12/02[/TD]
[TD="class: xl75, align: right"]62[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2016/12/05[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl72, align: right"]2016/11/02[/TD]
[TD="class: xl65, align: right"]2016/12/02[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl74, align: right"]2017/01/03[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]2016/11/02[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2016/12/02[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]-29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]2017/03/03[/TD]
[/TR]
[TR]
[TD="class: xl72"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: center"]#VALUE![/TD]
[TD][/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent, align: center"]#VALUE![/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]