Formatting time

thompsonk12 

If you load the data into Power Query and type a few example outputs, PQ will generate and apply the edits to the entire data field for you.  I had to set a US locale in order to convert from US to rest of world date formatting. 

thompsonk12 

Assuming your posted example data is representative of all your data, then I think this formula should work...

=0+REPLACE(A1,12,0,":")

Format the cell with the Custom Format of your choosing.

thompsonk12 

Try this formula, formatted as "mm/dd/yyyy hh:mm": 

=DATEVALUE(LEFT(A1,8))+
TIME(MID(A1,10,2),
RIGHT(A1,2),0)

thompsonk12 

You may use formula like

=DATEVALUE(LEFT(A1,8))+TIMEVALUE(MID(A1,10,2)&":"&RIGHT(A1,2))

for the first cell in next column, select this column starting from this cell till end of the range, Ctrl+D. On the range Ctrl+1 and apply custom mm/dd/yy hh:mm format to the range.

That is assuming mm/dd/yy is your default format for the date.

thompsonk12 

Hi,

Assuming that your Date and Time where entered correctly, they should be right aligned.

And because a Date is an incremental number (Day 1 is the 1st. of January 1900), and Time is a Decimal fraction of a Day.

With this assumption in mind:

Select a certain number of Cells in Column B (just to the right of your Date values) same size as the range in Column A: If you have Dates in the range A2:A1000 then Select B2:B1000

Then

Hit CTRL + R 

To copy the same values to column B

Now let's format

Select Column A and Hit: CTRL + SHIFT + 3  (date Formatting)

Select Column B and Hit  CTRL + SHIFT + 2 (Time Formatting)

However, I see in the screenshot your Date & Time look like text which requires a different process according to the situation (may be type 1 in a random cell >> copy this cell >> select Column A >> Hit ALT E S M consecutively) then follow the technique above. 

By the way, there is a function to convert a Date entered as Text into a number:

So say in A1 you have the Text: 28 Nov 2018, if you type in B1 

=DateValue(A1) it will return 43432 which is the corresponding date as an incremental number from 1 Jan 1900

here is a link to a complete Guide on Date Math in Excel 

https://www.youtube.com/watch?v=qIZxeOq-QDk

Hope that helps

Nabil Mourad