Date & Time Extraction in seperate columns Excel formula required

[Yadavgiri]

Hi All,

Attached is the file which i have got a interval time which comes in the format as given in Column A.

I need excel formula which captures date & time from this cell into a separate column which i have shown in Columns highlighted in yellow.

Please can someone help me with a formula which does this action?

Regards,
Yadavagiri

[sandy666]

with Power Query

StartInterval	Date	Time
2022-09-13T13:30:00.000+02:00	13/09/2022	13:30:00
2022-09-13T10:00:00.000+02:00	13/09/2022	10:00:00
2022-09-13T11:00:00.000+02:00	13/09/2022	11:00:00
2022-09-13T14:30:00.000+02:00	13/09/2022	14:30:00
2022-09-13T16:30:00.000+02:00	13/09/2022	16:30:00
2022-09-13T11:30:00.000+02:00	13/09/2022	11:30:00
2022-09-13T09:30:00.000+02:00	13/09/2022	09:30:00
2022-09-13T09:30:00.000+02:00	13/09/2022	09:30:00
2022-09-13T08:30:00.000+02:00	13/09/2022	08:30:00
2022-09-13T08:30:00.000+02:00	13/09/2022	08:30:00
2022-09-13T08:30:00.000+02:00	13/09/2022	08:30:00

Please Login or Register  to view this content.

[Pete_UK]

You can use this formula for the date in C2:

=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))

and this one for the time in D2:

=--MID(A2,12,8)

Format both cells in the style you wish to see, then copy down as required.

Hope this helps.

Pete

[Yadavgiri]

Thank You Sandy and Pete for the update.
both worked for me. Very much appreciated.
i also tried some work from my end and was able to fix from my end too but will use all the ways
attached the file highlighted in green my working

[Pete_UK]

Glad to have helped, and thanks for the rep.

Pete