Trying to use date format on an imported txt file in numbers on iPhone, but while the format option shows me the date and time options, the date does not change. Any ideas what the problem might be?
iPhone 11, iOS 26
Ahh, ISO8601 dates... yes.. Numbers does not recognize them as Date & Time values, only strings.
The simplest path for you right now is a helper column. Fortunately ISO8601 is pretty clear on the format, so a simple text parser can handle it. Here's one... Assuming the dates are in column A, insert a new column, B. Set the formula in B1 to:
=DATE(LEFT(A,4),TEXTBETWEEN(A,"-","-"),TEXTBETWEEN(A,"-","T",2))
(note: there are many ways of parsing this string, using combinations of LEFT(), RIGHT(), TEXTBETWEEN(), TEXTBEFORE(), TEXTAFTER(), and more.. this is just one example)
This will fill column B with the dates extracted from the ISO8601 . You can then either use Column B as your data column, ignoring Column A, or copy this column using Edit -> Copy Snapshot to copy the values (without the formulas) and paste it back into column A to replace the ISO8601 dates with values Numbers can work with, then delete the temporary column.
Ahh, ISO8601 dates... yes.. Numbers does not recognize them as Date & Time values, only strings.
The simplest path for you right now is a helper column. Fortunately ISO8601 is pretty clear on the format, so a simple text parser can handle it. Here's one... Assuming the dates are in column A, insert a new column, B. Set the formula in B1 to:
=DATE(LEFT(A,4),TEXTBETWEEN(A,"-","-"),TEXTBETWEEN(A,"-","T",2))
(note: there are many ways of parsing this string, using combinations of LEFT(), RIGHT(), TEXTBETWEEN(), TEXTBEFORE(), TEXTAFTER(), and more.. this is just one example)
This will fill column B with the dates extracted from the ISO8601 . You can then either use Column B as your data column, ignoring Column A, or copy this column using Edit -> Copy Snapshot to copy the values (without the formulas) and paste it back into column A to replace the ISO8601 dates with values Numbers can work with, then delete the temporary column.
You can tell Numbers that any given cell is a 'Date & Time' value, but it will only work if the cell contents are something that Numbers can reasonably convert into a date.
So, you have to look at the format of the dates you're importing and work out how to convince Numbers that it's an actual date. This could be caused by the order of the elements (for example, if the cell contains "2026.2.25', you might see that as a date, but it doesn't match Numbers' expectations).
Date formats also vary by region (MDY vs DMY), some regions use different delimiters (. vs /), and a myriad of other variables.
Conceptually, it's no different from telling Numbers that a cell with the value "Hello, world" is a Number cell... there's no reasonable way to convert "Hello, world" into a number, so your request to make it a Number is ignored.
Depending on the format of the date, and where the data is coming from, you. may need to change the exported file before you import into Numbers, or you can use additional post-processing in Numbers to break out the field into something that Numbers can interpret. Without seeing the data in question it's hard to be more specific.
Thanks for the response. This would be so simple in Excel.This is the cell format. It was comma delimited. 2026-01-09T19:48:36.247357Z. It’s not a huge file, but I’m trying to avoid manually typing in the dates. Don’t really want to jump through hoops to fix this, but if there is a simple solution and you know what it is, please let me know. Thanks.
Thank you Camelot - you are a true knight! Formulas worked perfectly.
Numbers iPhone: Imported TXT date format not changing