I can't believe I have to ask this, but here I am.
I have a Numbers (Numbers version 6.0 (6194)) sheet with data from an imported CSV file. One column is date/time info, formatted like so:
21.05.2018 12:00
This is the correct date format in the country I am in. My Mac knows this, so I assume Numbers ought to.
I need to get rid of the time from this col. You'd think that would be pretty simple. I select the cells in that col (not including the header, in case that single plain text cell confuses things), then click Format, then Cell, then under Data Format I choose Date & Time. No matter what Date or Time option I choose … absolutely nothing happens. My selected column of dates does not change – the displayed text is in exactly the same format as it started as, as shown above.
I know there isn't, but it is as if there is an "Apply" button I am missing, so my chosen settings aren't actually applied. If I click another cell in the sheet, then back to one of the ones I just tried to format, the Data Format has gone back to Automatic, not whatever I just chose.
Searching for help turns up the official Apple guide which, AFAICT, tells me to do exactly what I am doing.
I also found this old question and answer right here on Stackexchange which seems again to be exactly what I am doing – though the answer suggests that typing in new dates will result in them being formatted as required. Maybe I can't reformat existing dates in the sheet? That makes no sense!
What am I missing?
Edit
I tried adding a new col and using a formula to generate a date without the time:
YEAR($A2)−MONTH($A2)−DAY($A2)
But it just shows an error:
The function “YEAR” expects a date, but cell A2 contains a string.
Best Answer
I suspect your date is simply not in a format Numbers natively recognizes. To test this failure-mode and see the same symptom, try typing in something else it may not recognize, like 999999999999, and set it to a date format. Again, it doesn't change, because Numbers doesn't want to assume how to parse it, and risk outputting incorrect values.
A solution is to pre-parse the input by field. Here's a demo using
MIDto extract the year, month, and date fields by their position in the string, and hand them off to theDATEfunction.