Challenge

[sameeracma]

Hi

this is urgent(part of a assignment)

I need an Excel formula which will take the month/day from one column plus the year from another column and combine these to

make a date in a third column. Then the formula will need to check if the new date in the third column is an odd year. If yes, then if the odd year date is less than the current date, change the date to the nearest odd year which has not occurred

yet. Also, the formula will need to do the same thing for dates in an even year.

I created a formula which combines the month/day into a new date in the third column but am missing the logic for the comparison of odd/even years.

i need the month/day from column B and the year from column F. The new combined date should appear in column I. I wrote a formula which does some of the work in column I but it's incomplete.

pls try to fix this up

<table style="border-collapse: collapse; width: 827pt;" width="1101" border="0" cellpadding="0" cellspacing="0"><col style="width: 158pt;" width="210"> <col style="width: 75pt;" width="100"> <col style="width: 79pt;" width="105"> <col style="width: 64pt;" width="85"> <col style="width: 62pt;" width="83"> <col style="width: 77pt;" width="102"> <col style="width: 75pt;" width="100"> <col style="width: 66pt;" width="88"> <col style="width: 88pt;" width="117"> <col style="width: 83pt;" width="111"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl65" style="height: 30pt; width: 158pt;" width="210" height="40">Attorney Name</td> <td class="xl66" style="border-left: medium none; width: 75pt;" width="100">Birth
Date</td> <td class="xl67" style="border-left: medium none; width: 79pt;" width="105">DOB
Month</td> <td class="xl68" style="border-left: medium none; width: 64pt;" width="85">MCLE
Group</td> <td class="xl68" style="border-left: medium none; width: 62pt;" width="83">Bar
Number</td> <td class="xl69" style="border-left: medium none; width: 77pt;" width="102">Admit Year</td> <td class="xl70" style="border-left: medium none; width: 75pt;" width="100">Admit
Month</td> <td class="xl71" style="border-left: medium none; width: 66pt;" width="88">Odd/Even
Admit Year</td> <td class="xl71" style="border-left: medium none; width: 88pt;" width="117"> Compliance
Date</td> <td class="xl72" style="border-left: medium none; width: 83pt;" width="111">Compliance
Month</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ademola, Yomi (LO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">03/12/1980</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4401832</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04/18/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">3/12/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> </tr> </tbody></table>

Formula=MONTH(B1)&"/"&DAY(B1)&"/"&IF(MOD(YEAR(F1),2)=0,IF(YEAR(F1)<TODAY(),(YEAR(TODAY())-YEAR(F1))+YEAR(F1)+1,),IF(YEAR(F169)<TODAY(),(YEAR(TODAY())-YEAR(F1))+YEAR(F1)+2,))<today(),(year(today())-year(f1))+year(f1)+1,),if(year><today(),(year(today())-year(f1))+year(f1)+2,))></today(),(year(today())-year(f1))+year(f1)+2,))></today(),(year(today())-year(f1))+year(f1)+1,),if(year>

 

[konew1]

Re: Chalenge

Hi
Try this
=IF(YEAR(F4) >YEAR(TODAY()),DATE(YEAR(F4),MONTH(B4),DAY(B4)),DATE(YEAR(NOW())+MOD(YEAR(NOW()),2)-MOD(YEAR(F4),2),MONTH(B4),DAY(B4)))
I'm not sure if you want to retur 2009 or 2011 for the odd that hasn't yet happened. Or maybe you need look at specific dates to see if the date hasn't happened. This will return 2009 but it will be easy to adjust if required.

 

[sameeracma]

Re: Chalenge

thanx for your work but the formula is not correct. Column I "Compliance Date" represents a date that I will need to send out a letter so the date must always be in the future. As an example, Row 40 for the name "Benedict, Ryan", using your formula Column I indicates "1/27/2009" but that date is in the past, therefore it needs to read "1/27/2011". That is the date the letter will need to be sent to Ryan Benedict.

<table style="border-collapse: collapse; width: 827pt;" width="1101" border="0" cellpadding="0" cellspacing="0"><col style="width: 158pt;" width="210"> <col style="width: 75pt;" width="100"> <col style="width: 79pt;" width="105"> <col style="width: 64pt;" width="85"> <col style="width: 62pt;" width="83"> <col style="width: 77pt;" width="102"> <col style="width: 75pt;" width="100"> <col style="width: 66pt;" width="88"> <col style="width: 88pt;" width="117"> <col style="width: 83pt;" width="111"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl65" style="height: 30pt; width: 158pt;" width="210" height="40">Attorney Name</td> <td class="xl66" style="border-left: medium none; width: 75pt;" width="100">Birth
Date</td> <td class="xl67" style="border-left: medium none; width: 79pt;" width="105">DOB
Month</td> <td class="xl68" style="border-left: medium none; width: 64pt;" width="85">MCLE
Group</td> <td class="xl68" style="border-left: medium none; width: 62pt;" width="83">Bar
Number</td> <td class="xl69" style="border-left: medium none; width: 77pt;" width="102">Admit Year</td> <td class="xl70" style="border-left: medium none; width: 75pt;" width="100">Admit
Month</td> <td class="xl71" style="border-left: medium none; width: 66pt;" width="88">Odd/Even
Admit Year</td> <td class="xl71" style="border-left: medium none; width: 88pt;" width="117"> Compliance
Date</td> <td class="xl72" style="border-left: medium none; width: 83pt;" width="111">Compliance
Month</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ademola, Yomi (LO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">03/12/1980</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4401832</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04/18/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">3/12/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Adkins, Frances (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/10/1978</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4222584</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">03/22/2004</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">8/10/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Agrati, Paola (NY)</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">07/9/1979</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl87">4509808</td> <td class="xl76" style="border-top: medium none;">05/21/2007</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">7/9/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ahrens, Matthew (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">09/01/1970</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4055950</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">05/07/2002</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">9/1/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ajiashvili, Nathan (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/30/1981</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4426268</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">06/26/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">1/30/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Allinson, David (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/13/1969</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2797579</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/07/1997</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/13/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Al-Sudairi, Selman (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/06/1978</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4259925</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">12/01/2004</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">1/6/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Alvarez, Carlos (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/12/1961</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2115889</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/27/1987</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">11/12/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Alvino McGill, Lori (DC)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/27/1977</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4275855</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">02/16/2005</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8/27/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Alwang, Melissa (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">04/24/1964</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2863660</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04/29/1998</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">4/24/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Amdur, Stephen (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">10/17/1979</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">10-October</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4332169</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">06/20/2005</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">10/17/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">10-October</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Angelovska-Wilson,
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">10/02/1973</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">10-October</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4086740</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">11/26/2002</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">10/2/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">10-October</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Anzel, Christina (SF)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">12/01/1974</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4261640</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">09/28/2004</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">12/1/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Aravind, Santosh (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/14/1977</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4429148</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">07/11/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8/14/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Arce, Vilma (SF)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/24/1976</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4432134</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">11/15/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">11/24/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Archer, Kendra (SD)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">07/18/1977</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4126439</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">03/02/2003</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">7/18/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ashworth, Julie (NY)</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">02/22/1981</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4635538</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">09/08/2008</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/22/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl89" style="border-top: medium none; height: 12.75pt;" height="17">Aslani-Far, M. Adel (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">12/20/1968</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2799542</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/01/1997</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">12/20/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Atrokhov, Wendy (MO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/13/1973</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">3024130</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/24/2000</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8/13/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Bacon, J. Douglas (CH)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">05/05/1959</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4436077</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">09/20/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">5/5/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Balaban, Witold (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">04/12/1964</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2584936</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">02/09/1994</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">4/12/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Banovich, Mark M (MO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">06/05/1970</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2767135</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">07/15/1996</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">6/5/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Barbadoro, Theresa (LO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">09/11/1979</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4404257</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04/18/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">9/11/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl89" style="border-top: medium none; height: 12.75pt;" height="17">Barbier de La Serre, Eric
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/10/1973</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4062022</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/01/2002</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">1/10/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Barrowes, Spencer (HK)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">06/03/1976</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4416350</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">05/31/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">6/3/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Bay, Matteo (BR)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">07/08/1963</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2835742</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">10/21/1997</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">10-October</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">7/8/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Beck, Ramsey (DC)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">12/09/1970</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">3065554</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">09/20/2000</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">12/9/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Beckett, Mark (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">06/16/1957</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2215226</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">07/26/1988</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">6/16/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Benedict, Ryan (MI)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/27/1976</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4298915</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">02/07/2005</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">1/27/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> </tbody></table>


i tried this one but i couldn't get the result that i want

= IF( DATE( B2, MONTH(A2), DAY(A2) ) < TODAY(), DATE( B2+2, MONTH(A2), DAY(A2) ), DATE( B2, MONTH(A2), DAY(A2) ) )<!--HR-->


Also, I assume the same issue with the odd years will happen with the even years since we're in an odd year. What I mean is we would probably have the same problem with even years if we were in an even year now. So the formula would need to do the same thing with respect to even years as it does with odd years. Hope this helps. Thanks.<!--HR-->

 

[konew1]

Re: Chalenge

Hi
I'll try to look at it later, but very busy just now at work today so someone else might help

 

[sameeracma]

thanx
ok, but i need it tomorrow. can you do it tomorrow?. now it's 11 pm in ma country.

 

[sameeracma]

its better if you can use this data in excel. pls try to fix this


<table style="border-collapse: collapse; width: 827pt;" width="1101" border="0" cellpadding="0" cellspacing="0"><col style="width: 158pt;" width="210"> <col style="width: 75pt;" width="100"> <col style="width: 79pt;" width="105"> <col style="width: 64pt;" width="85"> <col style="width: 62pt;" width="83"> <col style="width: 77pt;" width="102"> <col style="width: 75pt;" width="100"> <col style="width: 66pt;" width="88"> <col style="width: 88pt;" width="117"> <col style="width: 83pt;" width="111"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl65" style="height: 30pt; width: 158pt;" width="210" height="40">Attorney Name</td> <td class="xl66" style="border-left: medium none; width: 75pt;" width="100">Birth
Date</td> <td class="xl67" style="border-left: medium none; width: 79pt;" width="105">DOB
Month</td> <td class="xl68" style="border-left: medium none; width: 64pt;" width="85">MCLE
Group</td> <td class="xl68" style="border-left: medium none; width: 62pt;" width="83">Bar
Number</td> <td class="xl69" style="border-left: medium none; width: 77pt;" width="102">Admit Year</td> <td class="xl70" style="border-left: medium none; width: 75pt;" width="100">Admit
Month</td> <td class="xl71" style="border-left: medium none; width: 66pt;" width="88">Odd/Even
Admit Year</td> <td class="xl71" style="border-left: medium none; width: 88pt;" width="117"> Compliance
Date</td> <td class="xl72" style="border-left: medium none; width: 83pt;" width="111">Compliance
Month</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ademola, Yomi (LO)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">03/12/1980</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4401832</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04/18/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4-April</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">3/12/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Adkins, Frances (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/10/1978</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4222584</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">03/22/2004</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3-March</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">8/10/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Agrati, Paola (NY)</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">07/9/1979</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl87">4509808</td> <td class="xl76" style="border-top: medium none;">05/21/2007</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">7/9/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">7-July</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ahrens, Matthew (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">09/01/1970</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4055950</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">05/07/2002</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">5-May</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">9/1/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9-September</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Ajiashvili, Nathan (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/30/1981</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4426268</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">06/26/2006</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">6-June</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">1/30/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Allinson, David (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/13/1969</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2797579</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/07/1997</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/13/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Al-Sudairi, Selman (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">01/06/1978</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4259925</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">12/01/2004</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">12-December</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Even</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">1/6/2010</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Alvarez, Carlos (NY)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">11/12/1961</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">2115889</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">01/27/1987</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1-January</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">11/12/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">11-November</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">Alvino McGill, Lori (DC)</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">08/27/1977</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">NY1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">4275855</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">02/16/2005</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2-February</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Odd</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8/27/2011</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8-August</td> </tr> </tbody></table>

 

[konew1]

Hi
I think you should go to your postings and edit to delete the names and dates. If these are real people then they have reason to be concerned with this on a public notice board.
Try this
=IF(DATE(YEAR(F3),MONTH(B3),DAY(B3)) >TODAY(),DATE(YEAR(F3),MONTH(B3),DAY(B3)),DATE(YEAR(NOW())-ABS(MOD(YEAR(NOW()),2)-MOD(YEAR(F3),2))+2,MONTH(B3),DAY(B3)))
It provides the compliance dates in your posting #6