Change date format

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I want change date formula to "sep 10 2013" in 10-9-2013 plz give me detail

2 replies

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Dipak,

Since "sep 10 2013" is saved as text, it's tricky to change it's format.

It basicly means you need to reserve an extra cell to translate "3 letter month" into number.

Let's say your date "sep 10 2013" is located in A5

B5 could be result formula:
=MID(A5,5,2)&"-"&IF(C5=FALSE,IF(LEFT(A5,3)="aug",8,IF(LEFT(A5,3)="sep",9,IF(LEFT(A5,3)="okt",10,IF(LEFT(A5,3)="nov",11,12)))),C5)&"-"&RIGHT(A5,4)

C5 could be calculate formula:
=IF(LEFT(A5,3)="jan",1,IF(LEFT(A5,3)="feb",2,IF(LEFT(A5,3)="mar",3,IF(LEFT(A5,3)="apr",4,IF(LEFT(A5,3)="may",5,IF(LEFT(A5,3)="jun",6,IF(LEFT(A5,3)="jul",7,FALSE)))))))

Please check the month references (Jan, feb, mar, ... etc..) as they might differ from the ones you use.

Best regards,
Trowa
Posts
1
Registration date
Thursday April 18, 2013
Status
Member
Last seen
April 19, 2013

Dear Sir, I am not understand your formula "sep 10 2013" only calculation one month plz calcualte all month plz I understand me................................
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
@Dipak "I want change date formula to "sep 10 2013" in 10-9-2013 plz give me detail"

What are you saying ? what is the date now. what is the date you want to see. Why can you not use format property to change the date format ?

I would go with Trowa understanding of issue that you have a text that looks like a date.
If the date is in A5
then you can convert it to excel date as
= DATEVALUE(MID(A5,5,2) & "-" & UPPER(LEFT(A5,3)) & "-" & RIGHT(A5,4))

then format the cell to what ever format you want
The formula is based on assumption that you have
three characters for month
followed by a single space
followed by two characters for date (so 4 appear as 04)
followed by space
followed by 4 characters for year