I have a column of dates, some of which are valid dates in excel
(6/1/1900) and others which are not whole dates or valid (1/31/ (always
has a trailing '/') or 1/31/1899)

I would like to create a new column with the text of these dates so
that I can sort based on this new mixed date column.

If I have a serialized date, I will just use =TEXT(<cell,"yyyy/mm/dd")

If all my non serialzed dates where fixed length, I could use:
=IF(LEN(J4)6,RIGHT(J4,4)," ")&"/"&MID(J4,4,2)&"/"&LEFT(J4,2)

But, some of my months and days are entered as 1,2,3 while others are
entered as 01,02,03.

So, how can I tell if I have a serializable date vs general, and how
can I make the above concatenation work for variable length
months/days?

Thanks

I was not able to determine if the cell was a date, but I was able to
see if it was text so...

=IF(ISTEXT(<cell),IF(LEN(<cell)6,RIGHT(<cell,4 ),"
")&"/"&MID(<cell,4,2)&"/"&LEFT(<cell,2),TEXT(<cell,"yyyy/mm/dd"))

works for fixed length invald dates. Now to get the non fixed
lengths...

Thanks for any input.

If a date is cut off like in for instance 1/31/
how do you want the result of that?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




wrote in message
oups.com...
I was not able to determine if the cell was a date, but I was able to
see if it was text so...

=IF(ISTEXT(<cell),IF(LEN(<cell)6,RIGHT(<cell,4 ),"
")&"/"&MID(<cell,4,2)&"/"&LEFT(<cell,2),TEXT(<cell,"yyyy/mm/dd"))

works for fixed length invald dates. Now to get the non fixed
lengths...

Thanks for any input.

For dates that do not have a year, I will either use 4 blanks or 4
dashed.

here is what I came up with: ( I did not original include the case of
a blank cell)
=IF(<Cell="","",IF(ISTEXT(<Cell),IF(LEN(<Cell) 6,RIGHT(<Cell,4),"----")&"/"&LEFT(<Cell,SEARCH("/",<Cell,1)-1)&"/"&MID(<Cell,SEARCH("/",<Cell,1)+1,SEARCH("/",<Cell,SEARCH("/",<Cell,1)+1)-SEARCH("/",<Cell,1)-1),TEXT(<Cell,"yyyy/mm/dd")))

It is rather long but does work. In situations like this where a
sub-value is calculated often, is there a way to reference it rather
then calculate it each time? ie SEARCH("/",<Cell,1) in the above.

Thanks.

If it is always 2 / and you always want to find the position of the last
then you can substitute it with something that can't be in the formula like

=FIND("^^",SUBSTITUTE(J4,"/","^^",2))

will always find the position of the last / if there are 2 of them

with regards to your question yes you can name a part of a formula and use
that name, insertnamedefine
that will shorten your formula quite a bit

example, assume using the above formula and name the substitute part "subst"
f, do insertnamedefine and
put subst in the name and in the reference box put

=SUBSTITUTE(Sheet1!$J$4,"/","^^",2)

now you can use

=FIND("^^",subst)


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




wrote in message
ups.com...
For dates that do not have a year, I will either use 4 blanks or 4
dashed.

here is what I came up with: ( I did not original include the case of
a blank cell)
=IF(<Cell="","",IF(ISTEXT(<Cell),IF(LEN(<Cell) 6,RIGHT(<Cell,4),"----")&"/"&LEFT(<Cell,SEARCH("/",<Cell,1)-1)&"/"&MID(<Cell,SEARCH("/",<Cell,1)+1,SEARCH("/",<Cell,SEARCH("/",<Cell,1)+1)-SEARCH("/",<Cell,1)-1),TEXT(<Cell,"yyyy/mm/dd")))

It is rather long but does work. In situations like this where a
sub-value is calculated often, is there a way to reference it rather
then calculate it each time? ie SEARCH("/",<Cell,1) in the above.

Thanks.

example, assume using the above formula and name the substitute part "subst"
f, do insertnamedefine and
put subst in the name and in the reference box put

=SUBSTITUTE(Sheet1!$J$4,"/","^^",2)

That would only work (I think) for the current column and Row. Since
there are many rows and several columns, how could I make the define
less static.

ie:
=SUBSTITUTE(Sheet1!<column offset from current<current
row,"/","^^",2)

Thanks.