Best Microsoft Excel Bloggers

Tuesday, September 29, 2009

Using Functions Together

5 TExt functions and Date Function Combined......Whew

I am putting together an 8 hour Functions seminar for the Indianapolis CPA Society and I think I am going to restruture it a bit. Usually when function are taught, they are taught as isolated examples. However, a lot of times you really need to use multiple functions together. So, I am going to show some useful examples that people can actually use. I am jumping the gun a bit here but I just put together a useful example and thought I would share it with you.

Frequently dates don't import the way you wish and you need to do a little 'surgery' on them. In the example below, I needed to use 4 Text Functions and 1 Date function. Now, obviously the first time or two you do this, it'll take awhile but then it will be second nature......... well maybe :)

In this example, dates that were imported into Column A are not in a date format familiar to Excel.
Using 5 different functions: 4 Text functions and 1 Date function we can extract, combine and then display Column A values in a date format.

I have broken it apart so that you can identify all the different functions.
The Left function retrieves the 4 leftmost characters in the cell
The Find function searches for the period in the cell and determines that there are 4 characters to get to the period plus 1 = 5 characters
The Mid function goes to the cell and uses the 5 characters + 1 character as the starting point and then grabs the 2 next characters
The Right function grabs the last 2 characters to the rightAll the functions are housed within the Date function which then provides joins the characters together in a familiar date format.
Stayed tuned... let's see what else I can come up with.
If you use any useful combination of functions please let me know. I love to use practical examples.

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek