0
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:
- First, all events with full dates are sorted as usually.
- 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.
- 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?
Hydrochoerus Hydrochaeris
Posted 2017-12-01T13:54:57.497
Reputation: 43
Very easy with VBA – Gary's Student – 2017-12-01T13:57:34.633
"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 – 2017-12-01T13:57:51.467
@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 – 2017-12-01T14:04:46.547
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 – 2017-12-01T14:10:32.077
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 – 2017-12-01T18:48:46.900
Capy, a couple of questions. 1. You stipulate that a date with month-only should be inserted "between first and last half" of other dates for that month. So suppose you had only these date for August: 2017-08-25, 2017-08-30 and 2017-08. Then the month-only date should be listed between the other two? 2. Would @barry 's suggestion be acceptable? Finally I would suggest that although you say "info should not emerge from nowhere", that's effectively what you're requiring in points 2 and 3 of your algorithm. – Bandersnatch – 2017-12-03T16:14:57.367
@Bandersnatch, 1) Yes, the third date should be inserted between the other two. 2) No. 3) No, by the data “emerging from nowhere” I meant assigning arbitrary months and days to the events that have no such info initially. If all we know is that the event was in Aug 2017 (and the exact day is not known), it is absloutely prohibited to assign the event to any particular day in August, even temporarily, even formally, even for technical purposes, etc. – Hydrochoerus Hydrochaeris – 2017-12-07T16:16:00.850