Change date format
Closed
Dipak Sonwane
-
18 Apr 2013 à 07:31
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 20 Apr 2013 à 10:24
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 20 Apr 2013 à 10:24
Related:
- Change date format
- How to change date format in excel - Guide
- Kingston format utility - Download - Storage
- Hdd low level format tool - Download - Storage
- Change lg tv name - Guide
- Facebook date of birth change - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
18 Apr 2013 à 11:00
18 Apr 2013 à 11:00
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 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
20 Apr 2013 à 10:24
20 Apr 2013 à 10:24
@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
19 Apr 2013 à 09:31