Excel date function - unknown day or month

Question

Not sure if what I want is possible (I'd imagine it isn't as Excel likes things to be correct!), but, I have a list of items, and the date to/from they were in use. In some cases, the day and/or month of that item is unknown. At present, I show this on my Excel as, for example, dd/mm/2018.

Obviously, I am then unable to filter on just the year as it isn't in correct date/time format.

So, is there anyway of storing dates with unknown days/months, but where Excel will still allowing filtering, as if it were a proper date?

Think that makes sense!

Example:

Answer 1

You can format a cell to show only the year.

Assuming that you have some date in the cell and only want to display the year from that date.

• Select the cell or cells
• Right-click a selected cell and select "Format Cells"
• Click "Custom" on left side of the dialog
• Enter aaaa in the Type field
• Click OK.

With this visual difference in the display of the cells, you will be able to see which cells don't have a full date. Once you update the full date in the field, you may return the cell's format to be the same as the ones that have full dates.

Answer 2

Suggestion: To allow the use of the simplest method of sorting; Set dates to be 01/01/year where month and date is unknown and similarly 01 for day in dates where it is unknown (alt: possibly 28:th for end dates).

If you need to TELL which dates has unknown info, add a column indicating it, e.g. "DM" for day and month, or just "D" for day.