change 12/5/2007 to 12/1/2005
 

I need a formula to add 2000 to the day, or the middle number between
the forward slashes, and make it the year and then make the day 1 or
15 or any number.

Can anyone help with that. One problem is sometimes it's one digit
and other times it's two. Ultimately, I'd like to graph the worksheet
with the date (month, year) as the X axis.

Alan

change 12/5/2007 to 12/1/2005
 

=DATE(2000+DAY(A1),MONTH(A1),1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan Calan" wrote in message
...
I need a formula to add 2000 to the day, or the middle number between
the forward slashes, and make it the year and then make the day 1 or
15 or any number.

Can anyone help with that. One problem is sometimes it's one digit
and other times it's two. Ultimately, I'd like to graph the worksheet
with the date (month, year) as the X axis.

Alan

change 12/5/2007 to 12/1/2005
 

Bob, thanks so much, it worked perfectly.

However, some of the dates in column E were Ok, like those for the
2000 and below. I have no idea why that is true. Those I don't want
to change. And for most of the others there were no sales in column
F, so there should be no date.

=DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is
there any way we can add those tests? I tried myself and got FALSE in
cells where I wanted blanks.

On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips"
wrote:

=DATE(2000+DAY(A1),MONTH(A1),1)

change 12/5/2007 to 12/1/2005
 

Sticking to Bob's original cell references, try this:

=IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1), MONTH(A1),1)))

You appear to have your dates in column E, so make the necessary
changes to A1 (4 times).

Hope this helps.

Pete

On Oct 30, 11:14 pm, Alan Calan wrote:
Bob, thanks so much, it worked perfectly.

However, some of the dates in column E were Ok, like those for the
2000 and below. I have no idea why that is true. Those I don't want
to change. And for most of the others there were no sales in column
F, so there should be no date.

=DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is
there any way we can add those tests? I tried myself and got FALSE in
cells where I wanted blanks.

On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips"



wrote:
=DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text -

- Show quoted text -

change 12/5/2007 to 12/1/2005
 

Pete and Bob,

Thanks so much and I learned a few things too. I had the logic almost
correct after Bob's formula, I didn't know you could pull days and
months and years out of the date format, but also I don't think I knew
"" puts nothing in a cell. I thought I could just skip it or comma
passed it. That, plus some of the cells had garbage in them made it
impossible for me to see the forest through the trees.

I don't even know how it happened but some cells on the text to
columns looked like this:

01 $240,000

I guess it just sucks in everything it sees sequentially.

So, thanks again for the help.

Alan


On Tue, 30 Oct 2007 16:39:52 -0700, Pete_UK
wrote:

Sticking to Bob's original cell references, try this:

=IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1) ,MONTH(A1),1)))

You appear to have your dates in column E, so make the necessary
changes to A1 (4 times).

Hope this helps.

Pete

On Oct 30, 11:14 pm, Alan Calan wrote:
Bob, thanks so much, it worked perfectly.

However, some of the dates in column E were Ok, like those for the
2000 and below. I have no idea why that is true. Those I don't want
to change. And for most of the others there were no sales in column
F, so there should be no date.

=DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is
there any way we can add those tests? I tried myself and got FALSE in
cells where I wanted blanks.

On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips"



wrote:
=DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text -

- Show quoted text -

change 12/5/2007 to 12/1/2005
 

You're welcome, Alan - thanks for feeding back.

Pete

On Nov 3, 2:30 pm, Alan Calan wrote:
Pete and Bob,

Thanks so much and I learned a few things too. I had the logic almost
correct after Bob's formula, I didn't know you could pull days and
months and years out of the date format, but also I don't think I knew
"" puts nothing in a cell. I thought I could just skip it or comma
passed it. That, plus some of the cells had garbage in them made it
impossible for me to see the forest through the trees.

I don't even know how it happened but some cells on the text to
columns looked like this:

01 $240,000

I guess it just sucks in everything it sees sequentially.

So, thanks again for the help.

Alan

On Tue, 30 Oct 2007 16:39:52 -0700, Pete_UK
wrote:



Sticking to Bob's original cell references, try this:

=IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1) ,MONTH(A1),1)))

You appear to have your dates in column E, so make the necessary
changes to A1 (4 times).

Hope this helps.

Pete

On Oct 30, 11:14 pm, Alan Calan wrote:
Bob, thanks so much, it worked perfectly.

However, some of the dates in column E were Ok, like those for the
2000 and below. I have no idea why that is true. Those I don't want
to change. And for most of the others there were no sales in column
F, so there should be no date.

=DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is
there any way we can add those tests? I tried myself and got FALSE in
cells where I wanted blanks.

On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips"

wrote:
=DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -