1

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.

Improve this question
6
  • I don't have time to writeup a full solution, but if you switch it to 05/21/2018 12:00 you'll see that it starts working. It's not expecting dots and it's not expecting the day first.
    – Ezekiel
    Apr 28, 2019 at 17:15
  • @EzekielElin Thanks - so I need to manually change the format in order to be able to automatically change the format? That ... seems crazy? In any case, I have about ~1K rows, that isn't going to work.
    – Don't Panic
    Apr 28, 2019 at 17:17
  • @EzekielElin BTW I am not in the US; the date format I am starting from is correct. My Mac region settings are set correctly, so both macOS and therefore I assume Numbers know the local date format (which is what I have).
    – Don't Panic
    Apr 28, 2019 at 17:22
  • There is one correct worldwide date format, 2019-04-28 (that is, yyyy-MM-dd). It is universally understood. It sorts well. It does not have odd or technology-breaking symbols. The standard also includes accommodations for time zones and times. See: ISO 8601 intro, Wikipedia, xkcd.
    – Joel Reid
    Apr 28, 2019 at 17:28
  • 1
    If, as seems possible, Numbers thinks the values are strings rather than dates, you could try doing a search and replace to change the '.' characters to '/' and see if that fixes it. You might need to do this in the CSV file (if you have access to it) before opening it in Numbers.
    – James Random
    Apr 28, 2019 at 17:30

2 Answers 2

Reset to default
2

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 MID to extract the year, month, and date fields by their position in the string, and hand them off to the DATE function.

=DATE(MID($A2,7,4),MID($A2,4,2),MID($A2,1,2))

screenshot of the above formula in action

Improve this answer
12
  • Thanks for the answer. The date format is correct for the country I am in, and macOS region settings are set for this country. I can't believe Numbers understands only US date formats!?
    – Don't Panic
    Apr 28, 2019 at 17:26
  • Bizarre. Yikes, Apple.
    – Joel Reid
    Apr 28, 2019 at 17:31
  • @Don'tPanic It could be a Numbers bug that it doesn't recognise all local date formats when reading CSV files. Or maybe the date column is quoted in the CSV and so Numbers interpreted it as strings?
    – James Random
    Apr 28, 2019 at 17:32
  • @JamesRandom yes it is quoted in the source CSV, but then it has to be bcs it has the time there: "25.04.2019 12:00". I suppose I could parse the CSV to get rid of it ... ugh.
    – Don't Panic
    Apr 28, 2019 at 17:38
  • I don't use Numbers much, but it doesn't seem to have options for specifying/overriding the types when you import CSV (unlike Excel, for example) which would be what you need here, I guess.
    – James Random
    Apr 28, 2019 at 17:41
0

Add a formula replacing $A2 with the cell you wish to use.

DATEVALUE(MID($A2,1,10)) + TIMEVALUE(MID(A2,12,5))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .