Date Format in Excel

[DWB82]

Hi Everyone

I have a file with a date/Time format that I am having some issues with. I have tried correcting this using both of the formulas that work with dates.

The only way that I have found to work with it is to run text to columns and then build the date up from each of the columns that this splits in to.

I have also attempted a mixture of Left, Mid & Right however, this has not worked either.

I would like either a formula or a Macro that is able to reference the data and put it in to a format that I am able to work with. The date information that I am working with looks like this "6/27/2019 7:53:16 AM".

I have tried to attache a file however, have been unable to.

If anything is unclear drop me a line.

Thanks in advance

 

[Special-K99]

=LEFT(A1,FIND(" ",A1)-1)+0

+0 forces the result into a number which is an Excel date, without the +0 the result would just be a string of characters.

You can't attach files on this site.

 

[SpillerBD]

Your sample Date/Time is good and does not demonstrate a problem.

"...and put it in to a format that I am able to work with." How are you trying to work with the date/time serial number?

 

[Norie]

You can convert the date/time into 2 columns, one date and one time using Data>Text to columns.

Use Fixed width, put a break between the date and time, use Date for the column data format for both columns and specify the MDY format for the date column.