Does Apple Numbers handle Timezones and common text formatted date/time like Mar 01, 2023 1:58:33 PM EST ?

As none of the format settings listed for Date And Time values includes the time zone value, I suspect that is the source of the issue you are seeing.

Here's the result I got from copying the Date and Time as expressed in your post, then pasting it directly into a cell whose Data format had been set to Date & Time with the format shown in the Format Inspector, to the right of the table.

Depending how many different time zones you normally use, you could build a pop-up menu containing the list, plus a 'start with blank' item to insert the zone tag in column C (to the right of the D&T value).

Placed in the column B cell, with the D&T value, the whole string would not match an available format, and would be interpreted as text.

Regards,

Barry

BillCerniuk wrote:

I have logs that have:

Mar 01, 2023 1:58:33 PM EST

It comes into Numbers OK but Numbers cannot seem to understand the format as date and or time.

How is your log "coming into" Numbers? Is "it" a CSV (character separated values) file or perhaps CSV data you are copy-pasting.

If so, and your data separator is a comma, then Numbers will get confused because there is a comma within the data-time format you are importing. It won't see the whole date-time string and think the parts before the comma are both text and split them into different columns.

If before importing you can change the separator within the data to something else, say a tab, then you will get better results. Then when you change the delimiter in response to the prompt after 'Table data was imported and can be adjusted' to tab you will get your log time in one column.

The value will still be formatted as text. Simply select that column and change the Data Format to Date & Time if you want (not mandatory).

Then to show the time in your local time zone simply add or subtract the offset for your time zone using Numbers shorthand for Duration input. For EST just subtract -"5h":

Select the column with that formula, command-c to copy, and Edit > Paste Formula Results and delete the original imported formula if you want.

SG

It understands them but converts them to GMT and we have no access to the time zone format (we cannot set or change the time zone on a date&time formatted cell). But text-formatted "date & time" with time zones can be used.

I copy/pasted the exact string from your post to a non-formatted cell. It was interpreted as date&time, converted to GMT (the time changed accordingly), and the EST became GMT.

I copy/pasted the string from your post to a cell pre-formatted as text. It pasted in exactly (EST and everything) and I was able to do math on it, but the result was in GMT. I do not know of a way to make the math result display as EST.

If they are in text cells, I'm pretty sure that sorting will be alphanumerically, not by date&time. Just something to be aware of.

You can convert the text-based "date&time" to actual date & time (as GMT) using DATEVALUE() + TIMEVALUE() or by simply adding "0d".

Oops! Paying too much attention to the missing "EST", and hadn't noticed the change from

     "Mar 01, 2023 1:58:33 PM"

to "Mar 01, 2023 6:58:33 PM"

Fortunately Badunit's observation skills were running smoothly!!

Regards,

Barry

BillCerniuk wrote:

yesterday it read them and converted to GMT automatically

can’t get it to do it again… yet…

You weren't dreaming. But I'll pose the question again. How is your log "coming into Numbers"? How are you doing the "it read them"?

If you are opening a CSV file or copy-pasting CSV data then see my post above.

SG

I find it odd that you have seen different results from importing the same Excel file. Different results from different Excel files would not be unbearably odd, but I expect the same results each time from the same Excel file.

Is the problem "solved" now that you have reimported that one file?

One other oddity I noticed just now is a difference when I paste your date & time into a text-formatted cell using Paste vs Paste & Match Style. I used Paste&Match Style in my first reply, not realizing the results would be different (other than the font). If I Paste then convert to date&time, it converts to GMT but drops the time zone info (Mar 1, 2023 6:58:33 PM). If I Paste&Match Style then convert, the result has GMT (Mar 1, 2023 6:58:33 PM GMT) and the GMT format is added to the list of time formats (as you noticed).

BillCerniuk wrote:

just an import using a machine generated Excel file.

Not sure what you mean by "machine generated" Excel file. Is it being produced is some special way, as opposed to just being saved from Excel? Your reference to BBEdit in your original post suggests data in a text format rather than Excel.

Keep in mind that Excel and Numbers store dates and times in very different ways. Excel uses serial numbers. Numbers uses "date-time strings."

The behavior you describe (the inability to change the Data Format from Text to Date & Time) suggests that Numbers fails to recognize the "string" in the cell(s) as valid formats for Date & Time. Numbers is quite good at guessing, but your particular format has it stumped. So you should consider changing the format on the source end if possible.

One way may be to save as CSV rather than as Excel, and follow the suggestions in my post above.

SG

BillCerniuk wrote:

machine generated (not MS Excel with human generated) spreadsheets.

Are those "spreadsheets" .xslx files or are they text files containing spreadsheet data? I'm guessing they're not .xslx files.

If they are not .xlsx files, then follow suggestions in my post above to change the dates to a format that Numbers can recognize.

If they are .xlsx files and you are still having trouble opening them in Numbers then you may want to open them in Excel and Save As ... csv. Then change the dates to a format that Numbers can recognize.

SG

BillCerniuk wrote:

The are ‘.xlsx’. (XML). They open nicely in BBEdit or Xcode

'.xlsx'. (XML) is a VERY rare file type! Maybe an old format from a decade or more ago?

.xlsx files (common usual Excel format) do not open "nicely: in BBEdit, at least not on my machine.

SG

meant to write "delete the original imported column" (not formula)