Sorting Column of Dates and Quarters

**DonkeyOte** · 07-27-2010, 01:49 PM

How do you want them to be handled ?

If you want them to appear last in your list then

=IF(OR(ISNUMBER(A1),A1="undetermined"),A1,DATE(LEFT(A1,4),RIGHT(A1)*3+1,0))
copied down

sort based on output.

**succel** · 07-27-2010, 01:55 PM

that will work :D

thanks, donkeyote!

**succel** · 07-27-2010, 02:43 PM

now on to figuring out the rest of the macro!

**succel** · 07-29-2010, 12:43 PM

Actually, if I'd like the "undetermined" cells to be converted to the date 12/31/2100, what would I modify in =IF(OR(ISNUMBER(A1),A1="undetermined"),A1,DATE(LEFT(A1,4),RIGHT(A1)*3+1,0))?

or could i insert another column and do a separate if statement to uncomplicate things? in this case, if it's a number copy it over and if it's not (undetermined) convert to 12/31/2100?