convert 2026-05-26T23:00:00.000Z to two columns

JohnSwift10

How do I convert 2026-05-26T23:00:00.000Z to date and time in 2 or 3 columns in excel 2019 where the data is in C column from C2 downwards?

wongataa

Text to columns function on the Data tab in the Ribbon.

Vitor

Try asking Gemini or ChatGPT

GDB2222

https://forums.moneysavingexpert.com/discussion/comment/82013704#Comment_82013704

AI is not all that reliable, but on this sort of thing it's usually spot on.

Cornucopia

If the data is in date/time format, you can select parts of it using functions like YEAR/MONTH/DAY/HOUR/MINUTE.

MyRealNameToo

=DATEVALUE(LEFT(C2,10))
=TIMEVALUE(MID(C2,12,8))

Then format the relevant columns as Date and Time

Excel doesnt really work with dates, it works with numbers that represent date/times, hence any date type function won't work with a date before 1/1/1900 (it also treats 1900 as a leap year even though it wasnt but thats an intentional error introduced to maintain compatibility with Lotus Notes).

I use Excel heavily every day but its an archaic programme and really needs killing off, it does things in stupid ways and a laptop that can deal with massive data sets in a basic DB will struggle to even scroll through the same data set present in XLS