Adjusting Date & Time Format

[Sundance_Kid]

Hi, I am downloading a file with data and I would like to add extra columns to show the details in a format I want.

It is the date & time.

I have attached an XL2BB file, columns B & C are showing what I currently get and columns E to H are what I want to get to, using the data from column B to get the data in columns E & F and from columns C to get the data in column G & H.

TimeIssueQuery.xlsx
	A	B	C	D	E	F	G	H	I
1
2		Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
3		6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05		18/06/2023	10:41	18/06/2023	14:05
4
5
6
7
8
9
Sheet1

 

[Dave Patton]

E3 Data TexttoColumns Date M/D/Y do not take the time
Format to your preference

T202306a.xlsm
	A	B	C	D	E	F	G	H
1
2		Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
3		6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05		18-Jun-23	10:41:05 AM	18-Jun-23	02:05:00 PM
4
5a

Cell Formulas
Range	Formula
F3	F3	=RIGHT(B3,11)+0
H3	H3	=RIGHT(C3,5)+0

 

[Sundance_Kid]

E3 Data TexttoColumns Date M/D/Y do not take the time
Format to your preference

T202306a.xlsm
	A	B	C	D	E	F	G	H
1
2		Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
3		6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05		18-Jun-23	10:41:05 AM	18-Jun-23	02:05:00 PM
4
5a

Cell Formulas
Range	Formula
F3	F3	=RIGHT(B3,11)+0
H3	H3	=RIGHT(C3,5)+0

Thanks for your reply, I nearly have it solved. Two issues:
1) For the Date, I have selected Text to Columns and Fixed Width but cannot figure out how you exclude the time portion when converting the date, when I tried to exclude it, it put the time into another column.
2) I have added a second time, see cell B4 but this does not convert with the formula. Ideally it should be showing 13:01 in cell F4.

Thanks

TimeIssueQuery.xlsx
	B	C	D	E	F	G	H
2	Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
3	6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05			10:41		14:05
4	6/19/2023 1:01:06 PM	Wolverhampton 19.06.2023 15:40			#VALUE!		15:40
Sheet1 (2)

Cell Formulas
Range	Formula
F3:F4	F3	=RIGHT(B3,11)+0
H3:H4	H3	=RIGHT(C3,5)+0

 

[Dave Patton]

1. Use TextToColumns with selection B3
step 3
Date MDY
Location E4
click on the General heading select do not import
click on the General heading select do not import the am
Finish
2. Use the suggested formulas for the times

 

[Dave Patton]

format to your preference

T202306a.xlsm
	A	B	C	D	E	F	G	H
6		Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
7		6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05		18-Jun-23	10:41:05 AM		02:05:00 PM
8		6/19/2023 1:01:06 PM	Wolverhampton 19.06.2023 15:40		19-Jun-23	01:01:06 PM		03:40:00 PM
9
5a

Cell Formulas
Range	Formula
F7	F7	=RIGHT(B7,11)+0
F8	F8	=RIGHT(B8,10)+0
H7:H8	H7	=RIGHT(C7,5)+0

 

[Dave Patton]

More that you can experiment with

T202306a.xlsm
	A	B	C	D	E	F	G	H
6		Bet Placed Date/Time	Race Date/Time		Bet Placed Date	Bet Placed Time	Race Date	Race Time
7		6/18/2023 10:41:05 AM	Doncaster 18.06.2023 14:05		18-Jun-23	10:41 AM	18-Jun-23	02:05 PM
8		6/19/2023 1:01:06 PM	Wolverhampton 19.06.2023 15:40		19-Jun-23	01:01 PM	19-Jun-23	03:40 PM
5a

Cell Formulas
Range	Formula
F7:F8	F7	=--TEXTAFTER(B7," ")
G7:G8	G7	=--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(C7," ")," "),".","-")
H7:H8	H7	=--TEXTAFTER(C7," ",2)