Date

[Tirivashe]

Hi there,

I am trying to use excel to automatically show a balance of zero once the expiry date for something arrives.

Background:

I offer people points based on a certain percentage. These point expire after 3 years i.e. if these points are not used within the 3 years, I want excel to make the balance zero.

My spreadsheet currently shows the date when the points become active and the date when they expire. So I am looking for a formula that allows me to say : if "today" is equal to the expiry date then the balance = zero otherwise the balance will be the original point less the points used.

I hope this makes sense and you guys can help me out. Thank you

 

[shift-del]

Hi

<table valign="middle" colspan="7" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="7" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">Person</td><td align="left">Points total</td><td align="left">Points used</td><td align="left">Points left</td><td align="left">Date</td><td align="left">Expire</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="left">John</td><td align="right">10</td><td align="right">3</td><td align="right">0</td><td align="right">
</td><td align="right">01.04.2011</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td>=(1-(TODAY()>=F2))*(B2-C2)</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[Tirivashe]

Thank you

 

[Scott Huish]

You could drop the 1- if you reversed the comparison:

=(TODAY()<F2)*(B2-C2)