If conditions to Add Mulitply dates

[blossomthe2nd]

Hi guys

Hope someone can help,
I have a column of dates (A) formatted as dd/mm/yyyy.

I am wishing to write a formula to amend these dates based on the following rules,

If A2 is > than 19/05/2011 return the Value in A2, if betweeen 18/05/2010 to 17/05/2011 add a year, if month is > than May change year to 2011, everything else change year 2012 .

I have been messing with of and or formulas and getting all sorts of Value# errors and i'm not sure if its because Im not writing the formula using the correct formating ( not used to using dates !!!!)

Can someone please help ?

Thanks
A

 

[MrKowz]

Your rules seem a bit confusing. Can you please provide some examples to illustrate each rule and their result?

 

[blossomthe2nd]

Hi there Mr Kowz,

Yeah they are pretty confusing , they are basically expiry dates that we wish to push forward , I have a list of them with dates ranging from 2008 - 2012, I wish to write a rule that will review these dates and push them out into the future ( so no longer expired!!)

However retain the Day Month portion, as we are only in May of the year now I am trying to cover all circumstances so if I had a date 12/06/08, I would want the formula to come back with 12/6/11 - if I had 12/5/08 I would want the formula to return 12/5/12

I have put a few samples and disired outcomes below, hopefully that clarifies ( Maybe Im over complicating this !!!! - I just wish the value to be in the future (within a 12 month range of today but as close to its origination date as possible)but keeping the day month part of date)

Thanks so much

<TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=187 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 id=td_post_2720439 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=87 height=17>Column A</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=100>Return</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40179">01/01/2010</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40909">01/01/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39600">01/06/2008</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40695">01/06/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40720">26/06/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40720">26/06/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40330">01/06/2010</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40695">01/06/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40426">05/09/2010</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40791">05/09/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40638">05/04/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="41004">05/04/2012</TD></TR></TBODY></TABLE>
01/01/2011 01/01/2012

 

[MrKowz]

Try the following formula, and copy down (note in my example, it is in mm/dd/yyyy format due to my system locale)

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;">1/1/2012</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">6/1/2008</td><td style="text-align: right;;">6/1/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">6/26/2011</td><td style="text-align: right;;">6/26/2011</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">6/1/2010</td><td style="text-align: right;;">6/1/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">9/5/2010</td><td style="text-align: right;;">9/5/2011</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4/5/2011</td><td style="text-align: right;;">4/5/2012</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">1/1/2012</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">A1>DATE(<font color="Red">2011,5,19</font>),A1,IF(<font color="Red">AND(<font color="Green">A1>=DATE(<font color="Purple">2010,5,18</font>),A1<=DATE(<font color="Purple">2011,5,17</font>)</font>),DATE(<font color="Green">YEAR(<font color="Purple">A1</font>)+1,MONTH(<font color="Purple">A1</font>),DAY(<font color="Purple">A1</font>)</font>),IF(<font color="Green">MONTH(<font color="Purple">A1</font>)>5,DATE(<font color="Purple">2011,MONTH(<font color="Teal">A1</font>),DAY(<font color="Teal">A1</font>)</font>),DATE(<font color="Purple">2012,MONTH(<font color="Teal">A1</font>),DAY(<font color="Teal">A1</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[blossomthe2nd]

MR Kowz

That look pretty good ran it off the samples and it does exactly what I wish

Thanks so much that would have taken me hours

Kind regards
Anne

 

[MrKowz]

MR Kowz

That look pretty good ran it off the samples and it does exactly what I wish

Thanks so much that would have taken me hours

Kind regards
Anne

Glad it works, thanks for the feedback!