Turn a string date into Date and Time values

[mvrht]

I am looking to extract the date into DD-MM-YYYY format and append the start date

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>May 2, 20181:00 PM - 1:45 PM PT
to become
02/05/2018 13:00:00

I have split the string with the following
=REGEXREPLACE(C2,"2018","2018 ")

But struggling with the conversion part

 

[Rick Rothstein]

I am looking to extract the date into DD-MM-YYYY format and append the start date

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>May 2, 20181:00 PM - 1:45 PM PT
to become
02/05/2018 13:00:00

Some questions for clarification...

1) May was a bad month to use for your example as we cannot tell whether your months are spelled out in full or abbreviated to the first 3 letters. Which is it?

2) Is there really no space or other character between the last digit of the year and the first digit of the time?

3) Why do you want the time to be 13:00:00 when the actual time would be 13:45:00... is the time supposed to be rounded down to the nearest hour?

4) Is May 2, 2018 a proper representation for a date in your locale?

 

[mvrht]

1) August etc. The full month
2) No space, that is how it comes in via ImportXML
3) I need the start time, hence the first part of the time block. (It's a webinar list)
4) Not sure what you mean, but another date exmaple is April 26, 20181:00 PM - 2:00 PM PT

Thanks

 

[jtakw]

Hi,

May be this will help:

Excel 2010
	A	B	C
1	May 2, 20181:00 PM - 1:45 PM PT		02/05/2018 13:00:00
2
3			C1 formatted as dd/mm/yyyy hh:mm:ss
Sheet31

Cell Formulas
Range	Formula
C1	=VALUE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(A1,FIND(",",A1)+2,4),MID(A1,FIND(",",A1)+2,4)&" "),"-",REPT(" ",100)),100))

 

[Rick Rothstein]

Does this formula work for you...

=0+REPLACE(LEFT(A1,FIND("M",A1,FIND(":",A1))),FIND(",",A1)+6,0," ")

Custom Format the cell you put it in as dd/mm/yyyy hh:mm:ss

 

[mvrht]

Hi,

May be this will help:

Excel 2010

	A	B	C
May 2, 20181:00 PM - 1:45 PM PT
C1 formatted as dd/mm/yyyy hh:mm:ss

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]02/05/2018 13:00:00[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet31​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=VALUE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(A1,FIND(",",A1)+2,4),MID(A1,FIND(",",A1)+2,4)&" "),"-",REPT(" ",100)),100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is good but outputs the date only in Google Sheets

This is the an example of the required return of a certain date
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

27/04/2018 14:46:58

​

 

[mvrht]

Does this formula work for you...

=0+REPLACE(LEFT(A1,FIND("M",A1,FIND(":",A1))),FIND(",",A1)+6,0," ")

Custom Format the cell you put it in as dd/mm/yyyy hh:mm:ss

Again, this is good. But only returning the date in Google Sheets and not start time. Such as
02/05/2018 13:00:00

 

[Rick Rothstein]

Again, this is good. But only returning the date in Google Sheets and not start time. Such as
02/05/2018 13:00:00

Google Sheets? This is an Excel forum and, in Excel, the formula I posted gives the date and time that you want when you format the cell you put that formula in using the following format pattern...

dd/mm/yyyy hh:mm:ss

 

[mvrht]

Google Sheets? This is an Excel forum and, in Excel, the formula I posted gives the date and time that you want when you format the cell you put that formula in using the following format pattern...

dd/mm/yyyy hh:mm:ss

My mistake, it comes out perfect from both yourself and jtakw- I has the cell format set incorrectly. I always found the formulas were pretty much compatible across the board Excel & Sheets etc and it works perfect in Sheets too. Thanks for solving