Saturday, January 8, 2011

Convert month in text to number

When we pull data from different sources i.e. SQL, txt file, CSV files...., The date columns are imported in text format because of their source data type or could be due to any other reason.

For example - You have data of month in text format with no fixed length(at least three characters) like Jan, Febr, March, Apr, May etc. & you want to convert them into number as 1,2,3,4,5.

In this case, the best formula you can use to convert month in text to number is =Month(A1&1) or =Month("Apr"&1)

I am not sure you are going to use this function directly but you can use it as sub function in Date function as well like
=DATE(2011, MONTH(A1&1),2)

***This formula is submitted by Manoj Kumar in the Group Puzzle. Thanks Manoj.***

If you have any feedback or better solution , Feel free the write the comment.

Wednesday, January 5, 2011

Custom Number and Date Format

Click here to read full article on 'Custom' Format.

What is 'Custom Format' ?:
Microsoft Office Excel provides many built-in number formats, but in some cases they do not meet our needs, we can customize a built-in number format to create our own.

Why 'Custom Format' and not 'Conditional Formatting'?: because to cure 'Common Cold' we go to doctors not surgeons. Exactly, when there is a simple and robust way then why to go otherwise. There are many benefits of using custom format including these:
1. Less overhead than Conditional Formatting.
2. Values can be used easily in formulas (Less manipulation required).
3. Charts looks better with custom colored labels/axis.
4. Works on all versions of Excel.
and many more which you can figure out using your innovative mind after reading this article.

Examples:
Let's move with some examples of various data. Essentially, examples makes this subject simple.
I - BASIC EXAMPLES
COMMENTS TO DISPLAY AS USE THIS
Leading Zeros 26 0026 0000
Phone Number 9999404843 999-940-4843 000-000-0000
Day of the Date 26/05/1984 Saturday dddd
Month of the Date 26/05/1984 May mmmm
Comma Place holder 23456789 23,456,789 #, ###
Currency 2605.5 € 2,605.50 € #,###.00

Click here to read full article on 'Custom' Format including intermediate and advanced examples with theory and logic.