Excel help - time date extraction

[LordGrover]

I have several columns of data in the form:

2022-09-20T00:30:00+01:00

What's the recommended way to get useful columns of date and net time without going through multiple hidden columns using left, right, etc., or is that the only way?

[simon_harris]

you cas use MID to extract the data from the position you want, text to colums and use position to cut it where you want it, power query to splt them, loads of ways to cut it that aren't complex

[Zetec-S]

Apologies if I've misunderstood, but do any of these help?



(I'm sure someone will be along with a better solution shortly )

[Doofus]

simon_harris said:

you cas use MID to extract the data from the position you want, text to colums and use position to cut it where you want it, power query to splt them, loads of ways to cut it that aren't complex

That would only work if you convert to a textstring first. The time and date are stored as numbers. What you're seeing there is just a formatted version, but using MID on that won't return what you need.

You can extract DAY, MONTH, HOUR, MINUTE and so on, but DAY will give you 1 thru' 7 and MONTH will return 1 thru' 12, so if you want day and month names, you'll need lookup tables too.

ETA. What he said

[LordGrover]

Zetec-S said:

Apologies if I've misunderstood, but do any of these help?



(I'm sure someone will be along with a better solution shortly )

13 not 12, but yep - that does the trick.

Thank you.

[Zetec-S]

LordGrover said:

13 not 12, but yep - that does the trick.

Thank you.

[gamefreaks]

Your date times are in ISO8601 format.

There isn't a nice way to convert these in Excel thats aby better then the string parsing the posters above have suggested.

https://stackoverflow.com/questions/4896116/parsin...

[paulrockliffe]

gamefreaks said:

Your date times are in ISO8601 format.

There isn't a nice way to convert these in Excel thats aby better then the string parsing the posters above have suggested.

https://stackoverflow.com/questions/4896116/parsin...

Yes, the problem with not being able to parse that format is that converting the Time+TimeZone Offset element into Time will cause the Date values to change where for example, 23:00 + 2 hours = 01:00. Hopefully the OP's data all plays nicely with that!

[Brother D]

I had a friend who had a big invoice sheet they had been entering data in a US format on an UK version excel sheet (I have no idea why).

I sort of know my way around excel but legit took 30 minutes to find a way to convert the column into UK date format for his accountant. (And no it wasn't just a case of format cells US->UK).