CODEWITHSUNDEEP
excelexcel-formulavba
Anon Ymouse
Anon Ymouse

Reputation: 101

Excel Convert date type: 22nd july 2015 into 22/07/2015

Hi as the title says is there a piece of VBA or a formula that converts dates like 22nd January 2015 into 22/01/2015?

Did not agree with people down voting, or the other question "answering" this as it did not.

Upvotes: 0

Views: 475

Answers (2)

Rick Hitchcock
Rick Hitchcock

Reputation: 35670

This formula should work with any two-letter ordinal number suffixes:

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,4),"nd",),"rd",),"st",),"th",)
 &
 MID(A1,5,99),"dd/mm/yyyy")

Example

enter image description here

(If not for August, this formula could be simplified a bit.)

Upvotes: 1

FreeMan
FreeMan

Reputation: 5687

Dim d As Date

d = CDate("22 July 2015")
Range("A1") = d
Range("B1") = Format(d, "dd/mm/yyyy")

Upvotes: 1

Related Questions