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.

Wednesday, January 09, 2008

Extracting data before and after the comma in excel


Tuesday, January 01, 2008

Nearly Hoodwinked by my own Chase Credit Card

I had just completed a purchase with Newegg, and I was redirected to a page to signup for "Verified by Visa" Not sure what it was and does. But I just closed the window. The purchase went through fine. I do not mind signing up for it but not like that, thrown in my face without full information on what it is for.