Sorting date and non "date"

[keyser_Soze]

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

 

[keyser_Soze]

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.

 

[Peo Sjoblom]

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

 

[keyser_Soze]

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.

 

[Peo Sjoblom]

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, insert>name>define
that will shorten your formula quite a bit

example, assume using the above formula and name the substitute part "subst"
f, do insert>name>define 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




news:[email protected]...

 

[keyser_Soze]

example, assume using the above formula and name the substitute part "subst"

f, do insert>name>define 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.