Change date format

Closed
Dipak Sonwane - Apr 18, 2013 at 07:31 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 20, 2013 at 10:24 AM
Hello,

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

2 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Apr 18, 2013 at 11:00 AM
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
0
Dipak Sonwane Posts 1 Registration date Thursday April 18, 2013 Status Member Last seen April 19, 2013
Apr 19, 2013 at 09:31 AM
Dear Sir, I am not understand your formula "sep 10 2013" only calculation one month plz calcualte all month plz I understand me................................
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 20, 2013 at 10:24 AM
@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
0