Trying to format a cell to Date/Time.

[Bcornish]

Hello,
I'm trying to set up Cell "B1" to pull the date and time from cell "A1" and then Format it to the Date/Time format. Cell "B1" pulls the information from "A1" using the formula below. When I try to change the format to Date/Time Cell "B1" is not effected.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][TABLE="width: 397"]
<colgroup><col width="397"></colgroup><tbody>[TR]
[TD="class: xl66, width: 397"]To OAKLAND, CA Delivered 11/01/17 at 12:07PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 107"]
<colgroup><col width="107"></colgroup><tbody>[TR]
[TD="width: 107"]11/01/17 12:07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

The formula for cell "B1" is =LEFT(RIGHT(A1,19),8) & " " & LEFT(RIGHT(A1,7),5)

I appreciate any input.
Thank you

 

[gallen]

When you say Date/Time format, how exactly do you want it to display in cell B1?

 

[gallen]

You can format it however you like using the TEXT function. So for example if I wanted 11/01/17 to display as Wed 11 Jan 2017 I would change the formula to:


=TEXT(LEFT(RIGHT(A1,19),8),"ddd dd mmm yyyy") & " " & LEFT(RIGHT(A1,7),5)


Which would mean cell B1 reads


Wed 11 Jan 2017 12:07