Convert Date and Time stored as text

Copper Contributor

Good day, I have date and time columns extracted from a source and cannot set the correct excel formats from the source. So, the extract comes through as text e.g. "6/22/2022 12:01 PM" in one cell. I need to perform a calculation on this. So I would have another column indicating the date and time of the next event in the same format as above. I need to determine the time difference between the two.

 

Is it possible to convert text (6/22/2022 12:01 PM) to date and time excel formats?

 

Thank you

3 Replies

@Warren_Porteous 

Try the following:

  • Select the date and time values in one column.
  • On the Data tab of the ribbon, select Text to Columns.
  • Without changing any settings, click Finish.

Does that work? If so, repeat for other columns.

@Hans Vogelaar thank you, that splits the columns well. the first column (the date) appears in the correct format. The second column (the time) needs further conversion, thank you!

@Warren_Porteous 

If you select Delimited and turn off all delimiters, the date and time should end up in a single cell.