Determine a value from Conditional Formatting

VLMacctg

New Member
Joined
Jul 22, 2015
Messages
2
Do you think you can help with the following excel problem?<o:p></o:p>
<o:p></o:p>
I need help with a formula for the number of occurrences at the bottom of the Deposit date column.<o:p></o:p>
Based upon the entered data the number should equal the number of highlighted cells which is “3”.<o:p></o:p>
The highlights occur through conditional formatting when the date of deposit is 2 or 3 days greater than the batch date. How can I automate this value so that it will automatically filter for the value of highlighted cells? This data will change monthly so I need the formula to read the highlighted cells only whenever they may occur which could be in any cell. Thanks in advance.<o:p></o:p>

<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></v:path></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 282.6pt; height: 446.4pt;" type="#_x0000_t75" alt="" o:spid="_x0000_i1025"> <v:imagedata o:href="cid:image004.png@01D2BF41.FDE812F0" src="file:///C:\Users\vmoore\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o:p>[TABLE="width: 283"]
<tbody>[TR]
[TD="class: xl114, width: 118, bgcolor: transparent"]Day
[/TD]
[TD="class: xl115, width: 82, bgcolor: transparent"]Batch Date
[/TD]
[TD="class: xl117, width: 85, bgcolor: #F2DCDB"]Deposit Date
[/TD]
[TD="class: xl118, width: 90, bgcolor: #F2DCDB"] Amount
[/TD]
[/TR]
[TR]
[TD="class: xl142, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl143, bgcolor: transparent"]1-Apr
[/TD]
[TD="class: xl144, bgcolor: transparent"][/TD]
[TD="class: xl145, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]2-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]3-Apr
[/TD]
[TD="class: xl125"][highlight]6-Apr
[/TD]
[TD="class: xl126"] 256.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]4-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 134.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]5-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 50.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]6-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 230.35
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]7-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 173.50
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]8-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 455.00
[/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]9-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]10-Apr
[/TD]
[TD="class: xl125"][highlight]13-Apr
[/TD]
[TD="class: xl126"] 195.45[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]11-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 180.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]12-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 229.65
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]13-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 45.62
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]14-Apr
[/TD]
[TD="class: xl125"][highlight]18-Apr
[/TD]
[TD="class: xl126"] 40.00[/highlight]
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]15-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl146, bgcolor: #BFBFBF"]Sunday
[/TD]
[TD="class: xl147, bgcolor: #BFBFBF"]16-Apr
[/TD]
[TD="class: xl148, bgcolor: #BFBFBF"][/TD]
[TD="class: xl149, bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl124, bgcolor: transparent"]17-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 57.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Tuesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]18-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 314.20
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Wednesday
[/TD]
[TD="class: xl124, bgcolor: transparent"]19-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 13.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Thursday
[/TD]
[TD="class: xl124, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 40.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Friday
[/TD]
[TD="class: xl124, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"]21-Apr
[/TD]
[TD="class: xl126, bgcolor: transparent"] 15.00
[/TD]
[/TR]
[TR]
[TD="class: xl128, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl124, bgcolor: transparent"]22-Apr
[/TD]
[TD="class: xl125, bgcolor: transparent"][/TD]
[TD="class: xl126, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl122, bgcolor: transparent"][/TD]
[TD="class: xl123, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] Number of Deposit Days
[/TD]
[TD="class: xl138, bgcolor: transparent"] 16
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl140, bgcolor: transparent, colspan: 2"] [highlight]Number of Occurences [/highlight][highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"] 3[/highlight]
[/TD]
[TD="class: xl138, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: transparent"]Percentage
[/TD]
[TD="class: xl121, bgcolor: transparent"][/TD]
[TD="class: xl141, bgcolor: transparent, align: right"]19%
[/TD]
[TD="class: xl139, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
</o:p>
 
How come Friday is not highlighted?
Also I don't know what you mean by 2 or 3 days greater? So which one is it?

Here is an example of greater than 2 days. This will return 4.

<table cellpadding="4px" border="1" cellspacing="1">
<tr><td bgcolor="#DFE3E8"></td><td align="center" width="192" bgcolor="#DFE3E8">A</td><td width="87.6" align="center" bgcolor="#DFE3E8">B</td><td width="298.8" align="center" bgcolor="#DFE3E8">C</td><td align="center" width="76.8" bgcolor="#DFE3E8">D</td></tr>
<tr><td bgcolor="#DFE3E8">5</td><td bgcolor="#FFFFFF"><b>Day</b></td><td bgcolor="#FFFFFF"><b>Batch Date</b></td><td bgcolor="#FFFFFF"><b>Deposit Date</b></td><td bgcolor="#FFFFFF"><b>Amount</b></td></tr>
<tr><td bgcolor="#DFE3E8">6</td><td bgcolor="#FFFFFF">Saturday</td><td bgcolor="#FFFFFF">1-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">7</td><td bgcolor="#FFFFFF">Sunday</td><td bgcolor="#FFFFFF">2-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">8</td><td bgcolor="#FFFFFF">Monday</td><td bgcolor="#FFFFFF">3-Apr</td><td bgcolor="#FFFFFF">6-Apr</td><td bgcolor="#FFFFFF">256</td></tr>
<tr><td bgcolor="#DFE3E8">9</td><td bgcolor="#FFFFFF">Tuesday</td><td bgcolor="#FFFFFF">4-Apr</td><td bgcolor="#FFFFFF">6-Apr</td><td bgcolor="#FFFFFF">134.35</td></tr>
<tr><td bgcolor="#DFE3E8">10</td><td bgcolor="#FFFFFF">Wednesday</td><td bgcolor="#FFFFFF">5-Apr</td><td bgcolor="#FFFFFF">6-Apr</td><td bgcolor="#FFFFFF">50</td></tr>
<tr><td bgcolor="#DFE3E8">11</td><td bgcolor="#FFFFFF">Thursday</td><td bgcolor="#FFFFFF">6-Apr</td><td bgcolor="#FFFFFF">7-Apr</td><td bgcolor="#FFFFFF">230.35</td></tr>
<tr><td bgcolor="#DFE3E8">12</td><td bgcolor="#FFFFFF">Friday</td><td bgcolor="#FFFFFF">7-Apr</td><td bgcolor="#FFFFFF">10-Apr</td><td bgcolor="#FFFFFF">173.5</td></tr>
<tr><td bgcolor="#DFE3E8">13</td><td bgcolor="#FFFFFF">Saturday</td><td bgcolor="#FFFFFF">8-Apr</td><td bgcolor="#FFFFFF">10-Apr</td><td bgcolor="#FFFFFF">455</td></tr>
<tr><td bgcolor="#DFE3E8">14</td><td bgcolor="#FFFFFF">Sunday</td><td bgcolor="#FFFFFF">9-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">15</td><td bgcolor="#FFFFFF">Monday</td><td bgcolor="#FFFFFF">10-Apr</td><td bgcolor="#FFFFFF">13-Apr</td><td bgcolor="#FFFFFF">195.45</td></tr>
<tr><td bgcolor="#DFE3E8">16</td><td bgcolor="#FFFFFF">Tuesday</td><td bgcolor="#FFFFFF">11-Apr</td><td bgcolor="#FFFFFF">13-Apr</td><td bgcolor="#FFFFFF">180</td></tr>
<tr><td bgcolor="#DFE3E8">17</td><td bgcolor="#FFFFFF">Wednesday</td><td bgcolor="#FFFFFF">12-Apr</td><td bgcolor="#FFFFFF">13-Apr</td><td bgcolor="#FFFFFF">229.65</td></tr>
<tr><td bgcolor="#DFE3E8">18</td><td bgcolor="#FFFFFF">Thursday</td><td bgcolor="#FFFFFF">13-Apr</td><td bgcolor="#FFFFFF">14-Apr</td><td bgcolor="#FFFFFF">45.62</td></tr>
<tr><td bgcolor="#DFE3E8">19</td><td bgcolor="#FFFFFF">Friday</td><td bgcolor="#FFFFFF">14-Apr</td><td bgcolor="#FFFFFF">18-Apr</td><td bgcolor="#FFFFFF">40</td></tr>
<tr><td bgcolor="#DFE3E8">20</td><td bgcolor="#FFFFFF">Saturday</td><td bgcolor="#FFFFFF">15-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">21</td><td bgcolor="#FFFFFF">Sunday</td><td bgcolor="#FFFFFF">16-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">22</td><td bgcolor="#FFFFFF">Monday</td><td bgcolor="#FFFFFF">17-Apr</td><td bgcolor="#FFFFFF">18-Apr</td><td bgcolor="#FFFFFF">57</td></tr>
<tr><td bgcolor="#DFE3E8">23</td><td bgcolor="#FFFFFF">Tuesday</td><td bgcolor="#FFFFFF">18-Apr</td><td bgcolor="#FFFFFF">20-Apr</td><td bgcolor="#FFFFFF">314.2</td></tr>
<tr><td bgcolor="#DFE3E8">24</td><td bgcolor="#FFFFFF">Wednesday</td><td bgcolor="#FFFFFF">19-Apr</td><td bgcolor="#FFFFFF">20-Apr</td><td bgcolor="#FFFFFF">13</td></tr>
<tr><td bgcolor="#DFE3E8">25</td><td bgcolor="#FFFFFF">Thursday</td><td bgcolor="#FFFFFF">20-Apr</td><td bgcolor="#FFFFFF">21-Apr</td><td bgcolor="#FFFFFF">40</td></tr>
<tr><td bgcolor="#DFE3E8">26</td><td bgcolor="#FFFFFF">Friday</td><td bgcolor="#FFFFFF">21-Apr</td><td bgcolor="#FFFFFF">21-Apr</td><td bgcolor="#FFFFFF">15</td></tr>
<tr><td bgcolor="#DFE3E8">27</td><td bgcolor="#FFFFFF">Saturday</td><td bgcolor="#FFFFFF">22-Apr</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">28</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">29</td><td bgcolor="#FFFFFF">Number of Deposit Days</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF">16</td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">30</td><td bgcolor="#FFFFFF">Number of Occurences</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF">=SUMPRODUCT(--((C6:C27-B6:B27)>2))</td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">31</td><td bgcolor="#FFFFFF">Percentage</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF">19%</td><td bgcolor="#FFFFFF"></td></tr>
<tr><td colspan="5"></td></tr>
<tr><td colspan="5" bgcolor="#D7E7F9">Sheet2</td></tr>
</table>
 
Upvote 0
Highlights occur when on the "batch date" the actual "deposit date" is greater than 2 days for all days except Friday's (which is 3 days because it's before a two day weekend)
 
Upvote 0

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
Back
Top