how can i convert Saturday, January 3, 1970 into 03/01/1970 into the same or separate cell?

arniebun

New Member
Joined
Jun 11, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi I am trying to convert Thursday, January 3, 1970 from one cell to the next showing 03/01/1970, or be able to replace the original cell with this date formatting. I am trying to extract data off a website but its going to be loads so will take me hours of flicking keys that would drive me insane. their are a number of different dates and months so it would have to recognised the month year day (3) and discard Saturday or put it into a different cell because would be good to see relationships on the days of the week. would really appropriate your help with this.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,928
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
N.B.
- my Regional Settings for date are dd-mm-yy USA centric systems reverse coercion of Month
- format the date for your preference. I formatted the date to include the day of the week.
- the formula is much more concise if you use the helper cell
- if you use the helper cell, ignore C2.

T202006b.xlsm
ABC
2Thursday, January 3, 1970Sat 03-Jan-1970
3Thursday, January 3, 1970January 3 1970Sat 03-Jan-1970
4Monday, June 29, 2020June 29 2020Mon 29-Jun-2020
5
3c
Cell Formulas
RangeFormula
C2C2=DATE(RIGHT(MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99),4),MONTH(1&LEFT(MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99),3)),MID(B3,FIND(" ",MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99))+1,2))
B3:B4B3=MID(SUBSTITUTE(A3,",",""),FIND(" ",SUBSTITUTE(A3,",",""))+1,99)
C3:C4C3=DATE(RIGHT(B3,4),MONTH(1&LEFT(B3,3)),MID(B3,FIND(" ",B3)+1,2))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Another option
=DATE(RIGHT(A2,4),MONTH(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),100,100))),SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,100)),",",""))
 

arniebun

New Member
Joined
Jun 11, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
brilliant thanks a mill, really has saved me some time sifting though 50 years of data
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top