Change date format
Closed
Dipak Sonwane
-
Apr 18, 2013 at 07:31 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 20, 2013 at 10:24 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 20, 2013 at 10:24 AM
Related:
- Change date format
- Excel date format dd.mm.yyyy - Guide
- How to change whatsapp time format - Guide
- Format factory - Download - Other
- Change computer name cmd - Guide
- Facebook date of birth change - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 18, 2013 at 11:00 AM
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 20, 2013 at 10:24 AM
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
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
Apr 19, 2013 at 09:31 AM