1

How: Convert separate Date & Time text strings together into a single unified Date Time field?

Taking the following text pasted into an Excel file from a web page:

Column A

enter image description here

What formula would be needed to pull both of those together into a Cell as a Combined: Date-Time Data

Column B (I typed the following manually, but how could I automate this with a formula)?

enter image description here

Been unable to use the VALUE() and DATEVALUE() on the above string in A1

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/

1 Answer 1

2

remove the Day name and add the time:

=MID(A1,FIND(",",A1)+2,99)+A2

Then format the result cell: mm/dd/yyyy hh:mm

enter image description here

4
  • Thanks. I am copy pasting the above from Amazon and other Tracking for packages websites hence wondering if it can be done without having to format the cells? No way for it to default appear such?
    – Alex S
    Jun 16, 2020 at 16:17
  • 2
    Sure but it will be a text field: =TEXT(MID(A1,FIND(",",A1)+2,99)+A2,"mm/dd/yyyy hh:mm") which may slow down and error if referred to in other formula. Jun 16, 2020 at 16:23
  • Scott - It seems to be working great, even when pulled in by another date time math formula using INT(X-Y)
    – Alex S
    Jun 17, 2020 at 4:41
  • It was working great in Office 2010, but when I opened the same file in Excel 2016/19 - it borks. Any way to fix that?
    – Alex S
    Jun 14, 2021 at 13:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .