Thursday, January 24, 2008

Convert From date to Text in excel

Use the =text function
For example
=text(a1, "mm-dd-yyyy-hh-mm-ss")
Mix and replace the format as needed see here for all of them

Reposted 'cause it is so good.


Number Code Description
General General number format.
0 (zero) Digit placeholder. This code pads the value with zeros to fill the format.
# Digit placeholder. This code does not display extra zeros.
? Digit placeholder. This code leaves a space for insignificant zeros but does not display them.
. (period) Decimal number.
% Percentage. Microsoft Excel multiplies by 100 and adds the % character.
, (comma) Thousands separator. A comma followed by a placeholder scales the number by a thousand.
E+ E- e+ e- Scientific notation.
Text Code Description
$ - + / ( ) : space These characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash.
\character This code displays the character you specify.

Note Typing !, ^, &, ', ~, {, }, =, <, or > automatically places a backslash in front of the character.
"text" This code displays text.
* This code repeats the next character in the format to fill the column width.

Note Only one asterisk per section of a format is allowed.
_ (underscore) This code skips the width of the next character. This code is commonly used as "_)" (without the quotation marks) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses. This allows the values to line up at the decimal point.
@ Text placeholder.
Date Code Description
m Month as a number without leading zeros (1-12)
mm Month as a number with leading zeros (01-12)
mmm Month as an abbreviation (Jan - Dec)
mmmm Unabbreviated Month (January - December)
d Day without leading zeros (1-31)
dd Day with leading zeros (01-31)
ddd Week day as an abbreviation (Sun - Sat)
dddd Unabbreviated week day (Sunday - Saturday)
yy Year as a two-digit number (for example, 96)
yyyy Year as a four-digit number (for example, 1996)
Time Code Description
h Hours as a number without leading zeros (0-23)
hh Hours as a number with leading zeros (00-23)
m Minutes as a number without leading zeros (0-59)
mm Minutes as a number with leading zeros (00-59)
s Seconds as a number without leading zeros (0-59)
ss Seconds as a number with leading zeros (00-59)
AM/PM am/pm Time based on the twelve-hour clock
Miscellaneous Code Description
[BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n] These codes display the characters in the specified colors.

Note n is a value from 1 to 56 and refers to the nth color in the color palette.
[Condition value] Condition may be <, >, =, >=, <=, <> and value may be any number.

Note A number format may contain up to two conditions.

3 comments:

Anonymous said...

Nice crib sheet :)

Jennifer said...

How do you convert date format of 5/15/1989 to text format of 05151989?

Yellow Blade said...

Công ty vận chuyển hàng hóa chúng tôi xin giới thiệu các dịch vụ vận chuyển, ship hàng uy tín để phục vụ nhu cầu Tết của quý khách hàng. Cụ thể chúng tôi sẽ cung cấp dịch vụ chuyển quà tết. Chúng tôi sẽ giúp bạn vận chuyển hàng hóa đến tay người thân, bạn bè ở xa một cách nhanh chóng nhất. Đảm bảo giá cả hợp lý chất lượng dịch vụ tuyệt vời. Ngoài ra chúng tôi còn cung cấp nhiều dịch vụ khác như ship hàng cod, giao hàng cho shop, chuyển phát nhanh,... Nếu cần chuyển hàng hãy nhớ liên hệ với chúng tôi nhé.