Challenge

sameeracma

New Member
Joined
Jul 2, 2009
Messages
7
Hi :unsure::unsure:

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>
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

New Member
Joined
Jul 2, 2009
Messages
7
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

New Member
Joined
Jul 2, 2009
Messages
7

ADVERTISEMENT

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

sameeracma

New Member
Joined
Jul 2, 2009
Messages
7
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,665
Members
418,147
Latest member
dorkas

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