can I change the date format as dd-mm-yy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In excel the date format is as mm-dd-yy. In my imported data(Btree) the
format is dd-mm-yy. So I want to change the format as dd-mm-yyyy
 
Try this:
(1) Select dates
(2) Right mouse click
(3) Choose command 'Format Cells'
(4) In tab 'Number' choose category 'Custom' and then choose your needful
date type.
 
But if the problem is with the format of data being imported, is it the
display format of the cells which he needs to look at, or is it Windows
regional settings?
 
There could be problems.

Do you want change date format from dd-mm-yy (imported data) to dd-mm-yyyy?
If yes, then you can use following formula:
=DATE(RIGHT(C2;2);MID(C2;4;2);LEFT(C2;2))

C D
2 31-12-96 31-12-1996
*Column C is the imported date.

There would be problems if year 2000 and so on is used in column C.
If all dates starts with year 2000, then formula would be:

=DATE(20&RIGHT(C2;2);MID(C2;4;2);LEFT(C2;2))

C D
2 31-12-05 31-12-2005
*Column C is the imported date.


The order of functions RIGHT, MID and LEFT in function DATE depends on date
format.
If date format is mm-dd-yy, than formula would be:

B C D
4 mm-dd-yy 12-31-87 31-12-1987
=DATE(RIGHT(C4;2);LEFT(C4;2);MID(C4;4;2))

5 mm-dd-yy 12-31-05 31-12-2005
=DATE(20&RIGHT(C5;2);LEFT(C5;2);MID(C5;4;2))
 
No need for that if you get dates with another regional date setting, if you
get a workbook all dates will be converted automatically, if you import text
just use step 3 in the text import wizard under column data format, select
date and from the dropdown select DMY
 
Aivis,

This is close to my problem. I'm importing text and one of the columns is a
date/time inthe format "Fri Aug 22 13:49:20 EDT 2003" and I'm trying to get
this into an excel format that I can then use for sorting. Would this
method, or something like it, work for my problem? And how, setting the
column format to a date format hasn't worked and I can find documentation on
how to specify custom formats to solve this.

Thanks,
~Dan
 
You have several responses to your other post.

Dan said:
Aivis,

This is close to my problem. I'm importing text and one of the columns is a
date/time inthe format "Fri Aug 22 13:49:20 EDT 2003" and I'm trying to get
this into an excel format that I can then use for sorting. Would this
method, or something like it, work for my problem? And how, setting the
column format to a date format hasn't worked and I can find documentation on
how to specify custom formats to solve this.

Thanks,
~Dan
Click to expand...
 
If your date/time "Fri Aug 22 13:49:20 EDT 2003" is a text (without " ") and
nothing helps to change this text format to date/time format, then use this
formula:

=DATE(RIGHT(TRIM(E27);4);VLOOKUP(MID(TRIM(E27);5;3);{"Jan"\1;"Feb"\2;"Mar"\3;"Apr"\4;"May"\5;"Jun"\6;"Jul"\7;"Aug"\8;"Sep"\9;"Oct"\10;"Nov"\11;"Dec"\12};2;0);MID(TRIM(E27);9;2))+TIME(MID(TRIM(E27);12;2);MID(TRIM(E27);15;2);MID(TRIM(E27);18;2))

If time format in text is 3:49:20 not 03:49:20 then use this formula:

=DATE(RIGHT(TRIM(E27);4);VLOOKUP(MID(TRIM(E27);5;3);{"Jan"\1;"Feb"\2;"Mar"\3;"Apr"\4;"May"\5;"Jun"\6;"Jul"\7;"Aug"\8;"Sep"\9;"Oct"\10;"Nov"\11;"Dec"\12};2;0);MID(TRIM(E27);9;2))+TIME(MID(TRIM(E27);FIND(":";TRIM(E27))-2;2);MID(TRIM(E27);FIND(":";TRIM(E27))+1;2);MID(TRIM(E27);FIND(":";TRIM(E27);FIND(":";TRIM(E27))+1)+1;2))

D E F
27 Fri Aug 22 03:49:50 EDT 2003 text
28 Fri Aug 22 03:49:50 EDT 2003 date/time (if time format is 03:49:50)
29 Fri Aug 22 03:49:50 EDT 2003 date/time (if time format is 3:49:50)

The formula would be different if time zone would be ET not EDT or EST
(symbols in time zone more or less then 3).
In cells E28 and E29, I used following date/time format:
ddd mmm dd hh:mm:ss "EDT" yyyy
 
Back
Top