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

  • Thread starter Thread starter Alan Calan
  • Start date Start date
A

Alan Calan

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
 
=DATE(2000+DAY(A1),MONTH(A1),1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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.
 
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
 
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
 
Back
Top