Converting date formats

[Pinonze]

Hello Dear Members,

Could you please help. I am trying to convert the format of date in excel. See below

Tuesday, August 29, 2017 = 29/08/2017.

So in cell A1 is Tuesday, August 29, 2017. We need cell A2 = 29/08/2017

Any suggestion would be much appreciated.

[Special-K]

There's probably a shorter way but this works on your example

=SUBSTITUTE(MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))+1,3),","," ")&LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),3)&" "&RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),4)+0

[shg]

Or

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

... and format as you like.

[Pinonze]

Hello Special K

Nice one. Just tested it and the result is

Tuesday, August 29,2017 using your formula above it gives me 29 Aug 2017. Which is great however do you think there is a formula to actually change it to 29/08/2017 or do I need to change the format to achieve this? Also rather than "Aug" may we have August. Thanks Special K really appreciate this.

[Pinonze]

Thanks, SHG,

The only thing is that it did not work. I tried it and got a value error

See Image
Capture.JPG
Capture.JPG

Kind REgards

Andrew Nze

[shg]

Works here:

	A	B	C
2	Tuesday, August 29, 2017	29/08/2017	B2: =--MID(A2, FIND(",", A2) + 2, 99)
3			Format of B2: dd/mm/yyyy

[Glenn Kennedy]

Instead of a non-editable picture, Pinonze, please post an Excel sheet.

[Pinonze]

Hello SHG

Still doesn't work! Weird

[Pinonze]

QA Summary Report.xls

Please find attached

[Special-K]

With slashes

=(SUBSTITUTE(MID(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(" ",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1,3),",","")&LEFT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),3)&"-"&RIGHT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),4))+0

result is a number

[Glenn Kennedy]

Shg's probably doesn't work 'cos of USA date conventions.

try this:

=DATE(RIGHT(A3,4),MONTH(1&MID(A3,FIND(" ",A3)+1,3)),LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",50)),50*2,50)),2))

[Pinonze]

@ Special K - You are a LEGEND!!!!! - Works like a treat.

Thanks All - SHG I am ok now thanks for your help!!!! Appreciate it

[Pinonze]

This works a treat as well. Appreciate it.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.