CODEWITHSUNDEEP
vbaexcel
Ryflex
Ryflex

Reputation: 5779

Excel formula to convert date from dddd mmmm dd, yyyy to dd/mm/yyyy

I'm trying to convert a long date in excel from the format of dddd mmmm dd, yyyy to dd/mm/yyyy using a formula

Example:

Convert the following:

Tuesday August 23, 2016

To:

23/08/2016

I've tried using substitute and search without any success and as far as I am aware there is no function where I can input as one format and withdraw it as another format, I tried using =Text(A1, "dd/mm/yyyy") too but that doesn't work either.

Any ideas?

Upvotes: 3

Views: 4652

Answers (3)

Slai
Slai

Reputation: 22896

then your dates might need a bit of cleaning :] 

=Text( Mid( Trim(Clean(A1)), Find(" ", Trim(Clean(A1)) ), 99) * 1, "dd/mm/yyyy")

Update

I just noticed the vba tag

[a1].NumberFormat = "dd/mm/yyyy"
[a1] = Split([a1], , 2)(1)        ' "Tuesday August 23, 2016" to "August 23, 2016"

Upvotes: 1

Ross
Ross

Reputation: 2417

So, using @Bathsheba suggestion, I went around this the long way but it works.

Try:

=DATEVALUE(CONCATENATE(MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2),MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3),RIGHT(A1,4)))

Explaination:

  • MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2)-This finds the day in dd format from the string in A1

  • MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3)-Takes the first three letters of the month from the string in A1

  • RIGHT(A1,4)-Takes the year from the string in A1

It then takes these values and concatenates them into the format of dd mmm yyyy. The DATEVALUE then changes this into the correct format of dd/mm/yyyy. This works for all dates in you format.

Note: The cell, which this formula lies, should be formatted for short date.

Upvotes: 1

Brian
Brian

Reputation: 2108

Custom format the cells as dd/mm/yyyy and use this formula:

=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

or without any additional formatting:

=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"dd/mm/yyyy")

Upvotes: 1

Related Questions