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

=TRIM(RIGHT(C1,LEN(C1)-SEARCH(",",C1)))
=TRIM(LEFT(C1,SEARCH(",",C1)-1))

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.