Prev: IF Statement troubles
Next: Help with Text and Dates
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" <Terry Willard(a)discussions.microsoft.com> 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 troubles
Next: Help with Text and Dates