Datetime in Excel

[JonathanRyan]

It's 2022 and still Excel doesn't natively support datetimes created by other MS applications.

What's the easiest way to deal with them?

I have a column of data that looks like this

2022-07-05T01:00:00+01:00

which is pretty standard for "2am BST (UTC + 1) on 5th July".

How do I make Excel realise this is a datetime so I can work out days of the week, avg per day etc?

I can obviously brute force it with a bunch of mid functions and a date() with another column for time and then stuff them back together but that all seems a bit 19th century. What's the cool way?

 

[Deleted member 49549]

It's 2022 and still Excel doesn't natively support datetimes created by other MS applications.

What's the easiest way to deal with them?

I have a column of data that looks like this

2022-07-05T01:00:00+01:00

which is pretty standard for "2am BST (UTC + 1) on 5th July".

How do I make Excel realise this is a datetime so I can work out days of the week, avg per day etc?

I can obviously brute force it with a bunch of mid functions and a date() with another column for time and then stuff them back together but that all seems a bit 19th century. What's the cool way?

Ive come across this before and found no elegant way. I used this stackoverflow to help.
Excel gets even more annoying when it parses text as a date with no way to get the original data back. A big issues in genetics where they have genomes named SEPT4 and MARCH1.
And dont get me started if you have a dataset that goes before 1900 !

 

[JonathanRyan]

Ive come across this before and found no elegant way. I used this stackoverflow to help.
Excel gets even more annoying when it parses text as a date with no way to get the original data back. A big issues in genetics where they have genomes named SEPT4 and MARCH1.
And dont get me started if you have a dataset that goes before 1900 !

Thanks It's amazing how M$'s flagship products don't play nicely. I suspect it's all a plot to get me to buy PowerBI.

 

[RichardC27]

It's 2022 and still Excel doesn't natively support datetimes created by other MS applications.

What's the easiest way to deal with them?

I have a column of data that looks like this

2022-07-05T01:00:00+01:00

which is pretty standard for "2am BST (UTC + 1) on 5th July".

How do I make Excel realise this is a datetime so I can work out days of the week, avg per day etc?

I can obviously brute force it with a bunch of mid functions and a date() with another column for time and then stuff them back together but that all seems a bit 19th century. What's the cool way?

Split it into two columns to separate the date and the time.

Then use the DATEVALUE function on the date part to convert a text date into an actual date
Use TIMEVALUE to do the same to the time part.
Recombine the two columns.

It's the T in front of the time that's causing the issue there. Excel natively stores all dates as the number of days since 01/01/1900, and times as decimals of the whole number. That's why it can't handle dates pre-1900, but it's also what allows you to perform calculations on dates and times, as they're actually just numbers in the background. Excel actually sees 05/07/2022 as 44,747 as that's the number of days since 01/01/1900

 

[JonathanRyan]

Split it into two columns to separate the date and the time.

Then use the DATEVALUE function on the date part to convert a text date into an actual date
Use TIMEVALUE to do the same to the time part.
Recombine the two columns.

It's the T in front of the time that's causing the issue there. Excel natively stores all dates as the number of days since 01/01/1900, and times as decimals of the whole number. That's why it can't handle dates pre-1900, but it's also what allows you to perform calculations on dates and times, as they're actually just numbers in the background. Excel actually sees 05/07/2022 as 44,747 as that's the number of days since 01/01/1900

Thanks, yes. I can brute force it.

I'm still mystified that Excel hasn't caught up with the rest of the Microsoft estate in supporting their UTCtimestamp format. It's a really good way for representing it until you reach Excel.

BTW I just checked and PowerBI has exactly the right tool. I might need to see if I can get PowerQuery to do the heavy lifting in Excel and that's Dax based.

 Result = #table(
        type table
        [
            #"UTC timestamp" = datetime,
            #"UTC date" = date,
            #"Local timestamp with offset" = datetimezone,
            #"Local timestamp without offset" = datetime
        ],
        {
            {
            UtcTimestamp,
            DateTime.Date(UtcTimestamp),
            LocalTimestamp,
            DateTimeZone.RemoveZone(LocalTimestamp)
            }