Extract Date and Time from a string

[reyemarr]

Hi,

I have a column with dates in the following format:

2016-11-10T05:18:47+00:00

2016-11-10T09:59:38+00:00

2016-11-10T11:00:58+00:00

2016-11-10T11:25:00+00:00

<tbody>
</tbody>

I want to extract both the date and time from each string and save them on a separate column each. What is a good formula to accomplish this?

Thanks

MR

 

[CyrusTheVirus]

Welcome to the forums. Try out the below, which assumes the month is 11 (Nov) and the day is 10 (Oct).

B2 (format cell as Short Date):

=DATEVALUE(LEFT(A1,10))

C2 (format cell as Time):

=TIMEVALUE(MID(A1,12,8))

2016-11-10T05:18:47+00:00	11/10/2016	5:18:47 AM
2016-11-10T09:59:38+00:00	11/10/2016	9:59:38 AM
2016-11-10T11:00:58+00:00	11/10/2016	11:00:58 AM
2016-11-10T11:25:00+00:00	11/10/2016	11:25:00 AM

<tbody>
</tbody>

 

[tusharm]

Assuming you will always have leading zeros, use
=LEFT(B2,10)
for the date and
=MID(B2,12,8)
for the time.
To force Excel to convert the strings into date and time as it understands them, add a -- in front of the function as in =--MID(B2,12,8) and format the cell as desired.

Hi,

I have a column with dates in the following format:

2016-11-10T05:18:47+00:00

2016-11-10T09:59:38+00:00

2016-11-10T11:00:58+00:00

2016-11-10T11:25:00+00:00

<tbody>
</tbody>

I want to extract both the date and time from each string and save them on a separate column each. What is a good formula to accomplish this?

Thanks

MR

 

[bosco_yip]

In B1, formula copy across to C1

=--SUBSTITUTE(LEFT($A1,19),"T"," ")

format B1 as Date, C1 as Time, and all copy down.

Regards

 

[AlKey]

Enter formula in B1 and copy across to C1 and then down
Format Col B as Date and Col C as Time

=--TRIM(MID(SUBSTITUTE(LEFT($A1,FIND("+",$A1)-1),"T",REPT(" ",99)),99*(COLUMNS($A:A)-1)+1,99))

Book1
	A	B	C
1	2016-11-10T05:18:47+00:00	11/10/2016	5:18:47 AM
2	2016-11-10T09:59:38+00:00	11/10/2016	9:59:38 AM
3	2016-11-10T11:00:58+00:00	11/10/2016	11:00:58 AM
4	2016-11-10T11:25:00+00:00	11/10/2016	11:25:00 AM
Sheet1

 

[reyemarr]

Thanks all for the different options.

Best regards,

MR

 

[AlKey]

Here is another one
Enter formula in B1 and copy across to C1 and then down
Format Col B as Date and Col C as Time

=CHOOSE(COLUMNS($A:A),--LEFT($A1,10),--MID($A1,12,8))