LinkBack URL
About LinkBacks
How can I sort a column with dates in a standard date format and quarter format?
How can I sort a column with dates in a standard date format and quarter format?
Last edited by succel; 07-27-2010 at 01:07 PM.
never mind. some simple if statements solve my problem!
Yes, I was going to say you should convert the strings to dates in adjacent column and sort based on those values, ie:
=IF(ISNUMBER(A1),A1,DATE(LEFT(A1,4),RIGHT(A1)*3+1,0))
copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
exactly the solution. i appreciate your response. i guess explaining the problem helped me realize how easy the solution actually is!
No problem - if you do come up with a solution yourself it's still good to post it for the benefit of others who may come across the thread at a later date (whilst trying to do something similar themselves).
uh oh..now i have a new problem: some of the values in my list are "undetermined," which causes the if function to yield the "wrong data type" error message. how can i avoid this?
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.
that will work :D
thanks, donkeyote!
now on to figuring out the rest of the macro!
Last edited by succel; 07-27-2010 at 03:50 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?
Last edited by succel; 07-29-2010 at 12:58 PM.
Never mind, again. All I need to do is insert another column, and if the contents of the prior column contain the string "undetermined," then insert 12/31/2100.
Thanks for all your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks