There is an Excel 2016 file with data about some events, the main sheet contains an ID and a date for each event, like this:

ID  Date
1   2017-10-02
2   2017
3   2017-09
4   2017-09-25
…   etc.

Most of the dates include year, month and day, but for some ones the exact day is not known, and some ones are accurate to a year only.

On another sheet I want this range to be sorted by date (newest first) following this algorithm:

  1. First, all events with full dates are sorted as usually.
  2. Second, for any event having only month, it is inserted into the middle of this month: not on the 15th day, but between first and last half of already sorted events.
  3. Third, for any event having only year, it is inserted into the middle of this year, similar to item 2.

Is it possible to achieve this with Excel formulas without VBA and (very important!) without assigning fake months and days to the events where they are absent?

  • Very easy with VBA – Gary's Student Dec 1 '17 at 13:57
  • "without assigning fake months and days to the events where they are absent?" - if you mean you can't add a helper column with those fake dates, then answer is "no, it's not possible". Please clarify what you can do and what can't. – Máté Juhász Dec 1 '17 at 13:57
  • @MátéJuhász, it is important to stick only to the data which is given and not to fabricate new data. If there’s no info about exact day or month of the event, this info should not emerge from nowhere. – Hydrochoerus Hydrochaeris Dec 1 '17 at 14:04
  • You don't need to justify your rules. I was asking in order to understand it better. So the answer is: No, there is no way to achieve it without VBA. – Máté Juhász Dec 1 '17 at 14:10
  • I think I would do this with fake dates in possibly two helper columns - sort the original data based on the helper columns and then you can delete the helpers, just leaving the correctly sorted original data - would that work for you? – barry houdini Dec 1 '17 at 18:48

Since you are in need of the Non-VBA Solution so, I would like to suggest you the simplest one, and to achieve it you need to follow these steps.

Part One

  1. Select the entire Data Range.

  2. Sort them in Ascending order on Date.

  3. Finally apply Filter.

Check the Screen Shot,

enter image description here

Note, last row has only Year (2017), for this Format the Cell(s) as Text, since Excel doesn't allows to write ONLY YEAR in Cell.

Part Two,

  1. First Filter Data has FULL DATE value.

  2. Copy & Paste Filtered Data in other place.

  3. Next Filter Data has Month & Year, Copy and Paste those also, elsewhere.

  4. Finally Filter Data with Year only, Copy & Paste them also.

Your filtered Data looks like the Screen Shot below.

enter image description here

Please Note, if you don't need to separate the Data in this case PART TWO is not required, Because after PART ONE, you are getting Data in desire order.

I do believe that, this Solution help you, if not just drop a Comment.

  • This NOT a correct answer to the problem OP stated. Try to read and fully understand what the question is before you post an incorrect answer. It just wastes everyone's time. – Bandersnatch Dec 2 '17 at 20:26
  • Okay, now I'm eagerly waiting for the perfect answer,, help me too. – Rajesh S Dec 3 '17 at 6:41
  • If VBA is ruled out, then Barry Houdini's suggestion would be the simplest approach. But the OP didn't answer his question about whether (temporary) extra columns would be OK. – Bandersnatch Dec 3 '17 at 16:01
  • OP's algorithm is, 1. Event with FULL Date. 2. Event with Month and 3. Event with Year. But technically Excel doesn't allows to enter only month or year. For this Cell must be formate as Text or General, or Date should written then apply Custom Format for Only Month & Year. Literally no user do so in day to day practice!! And the main issue is that OP holder hardly bothers about the Solution. Person must come and say how far Solutions are correct. – Rajesh S Dec 4 '17 at 10:43
  • 1
    @RajeshS, if I saw any signs of something I could find even resembling a step in a right direction to a solution, I’d come and say. – Hydrochoerus Hydrochaeris Dec 7 '17 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.