From: Terry Willard Terry on 31 Mar 2010 09:29 I am trying to replace only the first number in a date. example: 6/6/10 with 7/6/10 I am doing a find 6/ and a replace 7/ but it changes the 7/6 to 7/7 and I only want to look at the first number in the date. Can anyone help me???? From: Luke M on 31 Mar 2010 09:42 Use the 4th arguement to state that you only want to change the first instance. =SUBSTITUTE(A2,"6","7",1) This premise does assume that you have dates stored as text. If you have them stored as numbers (preferred), you can do: =DATE(YEAR(A2),7,DAY(A2)) -- Best Regards, Luke M "Terry Willard" wrote in message news:CA8C06FB-F1A4-472E-A738-860171BA5222(a)microsoft.com...>I am trying to replace only the first number in a date. > example: 6/6/10 with 7/6/10 > I am doing a find 6/ and a replace 7/ but it changes the 7/6 to 7/7 and I > only want to look at the first number in the date. Can anyone help me???? From: tompl on 31 Mar 2010 09:55 Assuming that your date format is Month, Day, Year you could use the following formula to compute the change from June to July. Other months would not be affected. First, assuming your original date is in cell A2, put this formula in Cell B2: =IF(MONTH(A2)=6,DATE(YEAR(A2),7,DAY(A2)),A2) Next, Paste-Special-Values from Cell B2 to Cell A2. Then you can clear the contents of Cell B2. Tom From: Terry Willard on 31 Mar 2010 10:21 Thank you so much! This was VERY helpful!!!!! "tompl" wrote: > Assuming that your date format is Month, Day, Year you could use the > following formula to compute the change from June to July. Other months > would not be affected. > > First, assuming your original date is in cell A2, put this formula in Cell B2: > =IF(MONTH(A2)=6,DATE(YEAR(A2),7,DAY(A2)),A2) > > Next, Paste-Special-Values from Cell B2 to Cell A2. Then you can clear the > contents of Cell B2. > > Tom >  |  Pages: 1 Prev: IF Statement troublesNext: Help with Text and Dates