?
Solved

Sort dates that have double zeroes in the month or day section

Posted on 2015-02-11
7
Medium Priority
?
135 Views
Last Modified: 2015-02-12
In the attached XL sheet, I have a Date column that I want to sort by year. The problem is, for some of the dates, I don't know the exact day or monty, only the year, so I've put a double zero (00) placeholder in there for the days/months I'm not sure of. It needs to be exact so I can't put 01 or anything like that in there. When I do this, it does not sort correctly. Is there some way to maintain the double zeroes and have the sheet column sort correctly by year?

Thanks,
Sort-Date.xlsx
0
Comment
Question by:contrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40604547
Short answer: NO.

day of month = 00 is not a valid date and the cell becomes a text entry.

I'd suggest you use a default of 01 (first of the month) and add a column next to it with a flag to indicate uncertainty over the exact date.

>It needs to be exact
It just can't be exact all the time you have unknowns in there.

I suppose the other thing you could do would be to use a =Year(yourdatecolumn) formula to get the year on its own, though you'd manually have to enter the year for the non-date 00 entries that you added. You could then sort on that Year column.
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 1000 total points
ID: 40604765
Here's a round about way. This formula is used to determine the year:

=VALUE(IF(ISTEXT(A2)=TRUE,RIGHT(A2,4),YEAR(A2)))

It can be done without the Value function as it did not sort like I thought. Basically what it does is look at your data to determine if it text or not. If it is, it uses the Right Function. If not then it uses Year. Do the same for Month and Day, using the appropriate functions:

=IF(ISTEXT(A2)=TRUE,LEFT(A2,2),MONTH(A2)) for month and
=VALUE(IF(ISTEXT(A2)=TRUE,MID(A2,4,2),DAY(A2))) for day.

Once that's done, copy all the data and then paste it special as values on another sheet. (See Sheet2 of attached.) You can now sort on that data.

Flyster
Sort-Date.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40605855
Flyster - you don't need the =TRUE part of those formulas. The IF statement will deal with the TRUE and FALSE conditions.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40606068
I have found that some of the text entries have a spare space character at the end.

I have tried incorporating the TRIM function with Flysters suggestions and it all goes horribly wrong.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 40606095
Year - =IF(ISTEXT($A2),RIGHT(TRIM($A2),4),YEAR($A2))
Month - =IF(ISTEXT($A2),MID(TRIM($A2),4,2),MONTH(TRIM($A2)))
Day - =IF(ISTEXT($A2),LEFT(TRIM($A2),2),DAY(TRIM($A2)))

Don't need to copy and paste, can sort in place by year then month then day.

I think it was going wrong because I was putting a TRIM around the first A2 as well "ISTEXT(TRIM($A2))" and by forcing a function on what looks like a date, Excel was trying to convert to a date, incorrectly in places.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40606115
Just noticed, Flyster's suggestions have assumed date format mm/dd/yyyy whereas I have assumed date format dd/mm/yyyy.

The cell format of sample data would suggest mm/dd/yyyy to be correct so my formulas should be:

Year - =IF(ISTEXT($A2),RIGHT(TRIM($A2),4),YEAR($A2))
Month - =IF(ISTEXT($A2),LEFT(TRIM($A2),2),MONTH(TRIM($A2)))
Day - =IF(ISTEXT($A2),MID(TRIM($A2),4,2),DAY(TRIM($A2)))

Thanks
Rob H
0
 

Author Closing Comment

by:contrain
ID: 40606152
I am accepting and giving points to 2 answers because each gave me what I wanted even though there were slight differences on the formulas used. Great answers both to a very difficult situation!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question