Formula to Make Overdue date record highlighted in field

ferranti

New Member
Joined
Aug 8, 2010
Messages
3
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Excel Workbook
ABCD
1Loan DateReturn Due DateOverdue by x daysDate Sample Returned
201/08/201007/09/2010-30
301/07/201007/08/20101
403/08/201010/08/2010-2
503/08/201011/08/2010-309/08/2010
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21. / Formula is =AND(C1<0,D1="")Abc
D21. / Formula is =AND(C1<0,D1="")Abc
 

Fowmy

Well-known Member
Joined
Jul 10, 2008
Messages
512
Office Version
  1. 365
Platform
  1. Windows
May this one:

Mark the whole Data area and give this as the Conditional formating "=$C3<>0"
Excel Workbook
ABCD
2Loan DateReturn Due DateOverdue by x daysDate Sample Returned
301-08-1007-09-10006-09-10
401-07-1007-08-101
503-08-1010-08-10012-09-10
Sheet1
Excel 2010
Cell Formulas
RangeFormula
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

New Member
Joined
Aug 8, 2010
Messages
3
Thank you very much for your help. May have some further requests down the line.
Many thanks, A
 

JennGraham

New Member
Joined
Feb 2, 2017
Messages
1
<colgroup><col width="63" style="width: 47pt;">
<tbody>
</tbody>
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.
Loan Date
YYY/MM/DD
Return Due Date
YYY/MM/DD
Days OverdueDate Returned
YYYY/MM/DD
Extended Date
YYY/MM/DD
2017/02/022017/03/020
2016/11/022016/12/02622016/12/05
2016/11/022016/12/02302017/01/03
2016/11/022016/12/02-292017/03/03
#VALUE!
#VALUE!
<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> </tbody>
 

Forum statistics

Threads
1,143,677
Messages
5,720,260
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top