split date and time values out to separate cells

[nightracer]

Hi all, I have some data that shows date and times in a single cell as:

Cell A1
2023-05-02 18:00 - 21:30

Other than text to columns, is there a formula I could use to split this into:
Cell B1 (date) 02.05.23
Cell C1 (time 1) 18:00
Cell D1 (time 2) 21:30

Any help appreciated
N

 

[Alex Blakenburg]

You didn't action Fluff's comment in your last thread.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have MS 365 or Office 2021 you will have more options but this should work.
Note: You will need to format the cells as date and time x 2

Book1
	A	B	C	D
1	2023-05-02 18:00 - 21:30	2/05/2023	18:00	21:30
Sheet1

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(LEFT($A1,10))
C1	C1	=TIMEVALUE(MID($A1,12,5))
D1	D1	=TIMEVALUE(RIGHT($A1,5))

 

[nightracer]

Fantastic, thank you very much Alex, really appreciate it.
Many thanks
N

 

[Alex Blakenburg]

You're welcome. Glad I could help.

 

[Peter_SSs]

Given that you have just updated your profile, even though this thread is quite old, you may be interested in another option (as alluded to by Alex) where you can get all 3 values with a single formula.

23 12 04.xlsm
	A	B	C	D
1	2023-05-02 18:00 - 21:30	02.05.2023	18:00	21:30
Date Time

Cell Formulas
Range	Formula
B1:D1	B1	=--TEXTSPLIT(A1,{" "," - "})
Dynamic array formulas.