ISO 8601 timestamps and Numbers

[Ian McCall]

Seems Numbers doesn’t understand them. I have a column full of entries like
“2021-05-01T05:30:00+01:00”, I set the Cell Data Type to "Date&
Time”...nothing. It doesn’t recognise these values as dates at all.

Any way around this? Must say does feel like quite an oversight.

Thanks,
Ian

[Graeme Wall]

replacing the T with a space helps but I can't get it to recognise the
+01:00

NB you did set the Date and Time formats?
--
Graeme Wall
This account not read.

[Chris Ridd]

On 03/05/2022 19:11, Ian McCall wrote:

Set a custom date and time format inside Numbers. Works here, except
there's no option for timezone offsets.

I suspect the latter means that internally all dates and times are
considered local. It looks a bit like Excel has the same limitation.

Arguably Numbers should understand more standard date and time formats,
but there'll always be one more standard out there it does not...

--
Chris

[Ian McCall]

On 3 May 2022, Graeme Wall wrote
(in article <t4s4kr$e0l$3...@dont-email.me>):

> replacing the T with a space helps but I can't get it to recognise the
> +01:00
>
> NB you did set the Date and Time formats?

Not sure how to do that to be honest. I have found “custom format”, but I
can’t make one match ISO8601.

Cheers,
Ian

[Ian McCall]

On 4 May 2022, Chris Ridd wrote
(in article <t4sv14$mp9$1...@dont-email.me>):

> On 03/05/2022 19:11, Ian McCall wrote:
> > Seems Numbers doesn’t understand them. I have a column full of entries
> > like
> > “2021-05-01T05:30:00+01:00”, I set the Cell Data Type to "Date&
> > Time”...nothing. It doesn’t recognise these values as dates at all.
> >
> > Any way around this? Must say does feel like quite an oversight.
>
> Set a custom date and time format inside Numbers. Works here, except
> there's no option for timezone offsets.

Yep - exactly. I can’t get it working (including the T bit, as well as the
offset)

> Arguably Numbers should understand more standard date and time formats,
> but there'll always be one more standard out there it does not...

Agreed, but ISO 8601 of all things. You would really, -really- expect that
one to be recognised.

Cheers,
Ian

[Richard Tobin]

In article <0001HW.2821A7B505...@news.individual.net>,

I know nothing about Numbers, but wouldn't a spreadsheety way to
handle it be to have another column with a formula that rearranges the
bits of the original into a date format it understands?

Presumably there's something that lets you add some hours to a date
to handle the timezone.

-- Richard

[Ian McCall]

On 4 May 2022, Richard Tobin wrote
(in article <t4u16v$a8n$1...@macpro.inf.ed.ac.uk>):

> I know nothing about Numbers, but wouldn't a spreadsheety way to
> handle it be to have another column with a formula that rearranges the
> bits of the original into a date format it understands?

That would work - some playing around with substrings I guess. Thanks for
that.

Cheers,
Ian

[Chris Ridd]

On 04/05/2022 14:05, Ian McCall wrote:
> On 4 May 2022, Chris Ridd wrote
> (in article <t4sv14$mp9$1...@dont-email.me>):
>
>> On 03/05/2022 19:11, Ian McCall wrote:
>>> Seems Numbers doesn’t understand them. I have a column full of entries
>>> like
>>> “2021-05-01T05:30:00+01:00”, I set the Cell Data Type to "Date&
>>> Time”...nothing. It doesn’t recognise these values as dates at all.
>>>
>>> Any way around this? Must say does feel like quite an oversight.
>>
>> Set a custom date and time format inside Numbers. Works here, except
>> there's no option for timezone offsets.
>
> Yep - exactly. I can’t get it working (including the T bit, as well as the
> offset)

Well ignoring the offset part because apparently spreadsheets don't do
timezones without something like Richard's suggestion, you'd do this:

Inspector Cell tab
Data Format: Create Custom Format
(new dialog appears)
Name: ISO 8601 there or thereabouts
Type: Date & Time
Custom Format: [Year]-[Month]-[Day of Month]T[Hour]:[Minute]:[Second]

Each [label] has a popup letting you choose, say, 00-23 for the hour.

>
>> Arguably Numbers should understand more standard date and time formats,
>> but there'll always be one more standard out there it does not...
>
> Agreed, but ISO 8601 of all things. You would really, -really- expect that
> one to be recognised.

Yes, as a European I'd agree with you. But there are other genuine
standards they also don't do.

--
Chris

[Graeme Wall]

On 04/05/2022 17:41, Chris Ridd wrote:
> On 04/05/2022 14:05, Ian McCall wrote:
>> On 4 May 2022, Chris Ridd wrote
>> (in article <t4sv14$mp9$1...@dont-email.me>):
>>
>>> On 03/05/2022 19:11, Ian McCall wrote:
>>>> Seems Numbers doesn’t understand them. I have a column full of entries
>>>> like
>>>> “2021-05-01T05:30:00+01:00”, I set the Cell Data Type to "Date&
>>>> Time”...nothing. It doesn’t recognise these values as dates at all.
>>>>
>>>> Any way around this? Must say does feel like quite an oversight.
>>>
>>> Set a custom date and time format inside Numbers. Works here, except
>>> there's no option for timezone offsets.
>>
>> Yep - exactly. I can’t get it working (including the T bit, as well as
>> the
>> offset)
>
> Well ignoring the offset part because apparently spreadsheets don't do
> timezones without something like Richard's suggestion, you'd do this:
>
> Inspector Cell tab
> Data Format: Create Custom Format
> (new dialog appears)
> Name: ISO 8601 there or thereabouts
> Type: Date & Time
> Custom Format: [Year]-[Month]-[Day of Month]T[Hour]:[Minute]:[Second]
>
> Each [label] has a popup letting you choose, say, 00-23 for the hour.
>

Neat

[Chris Ridd]

On 04/05/2022 15:05, Richard Tobin wrote:
> Presumably there's something that lets you add some hours to a date
> to handle the timezone.

Yes, plain old arithmetic. Add (1/24) to a date/time value to increase
it by one hour.

--
Chris

[Graeme Wall]

Assuming you don't want the time displayed with the offset.

[Chris Ridd]

On 04/05/2022 21:46, Graeme Wall wrote:
> On 04/05/2022 19:32, Chris Ridd wrote:
>> On 04/05/2022 15:05, Richard Tobin wrote:
>>> Presumably there's something that lets you add some hours to a date
>>> to handle the timezone.
>>
>> Yes, plain old arithmetic. Add (1/24) to a date/time value to increase
>> it by one hour.
>>
>
> Assuming you don't want the time displayed with the offset.

Right. But that seems impossible to do while keeping the cell as a
date/time. As a string, sure..

Numbers' function help says quite a few things about what timezone it
assumes in multiple cases. "Good luck" seems like a good summary.

--
Chris

[Graeme Wall]

If you want to keep the offset you could display it in the adjacent cell:

|Date T Time|offset|