If conditions to Add Mulitply dates

blossomthe2nd

Active Member
Joined
Oct 11, 2010
Messages
450
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Your rules seem a bit confusing. Can you please provide some examples to illustrate each rule and their result?
 
Upvote 0
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
 
Last edited:
Upvote 0
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 />
 
Upvote 0
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
 
Upvote 0
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! ;)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top