Excel Text And Date Formulas
One of the things we've been hearing about Office 2007 is how many new formulas there are!
There are some new formulas in there, but most have been around forever. The
new interface for Excel does do a much better job of displaying what's
available though, and it makes it look like there are new functions.
From the ribbon under Formulas,
you can see all of the classes of formulas available to you, and by clicking
one (like Logical above), you can see individual formulas. We like IFERROR,
which is new, instead of IF(ISERROR(),TRUE, FALSE).
In the following
example of logical, text, and date functions we won't use anything exclusive
to Office 2007 - most of our clients are still using earlier versions. Let's
have a look at a typical kind of "Notes" column we need to extract a date
from using only functions that have been around for a while.
In the Excel
file visible below (you can download it
for better viewing) you can see a Notes column that contains a date in a
variety of formats from m/d to mm/dd/yyyy. We need to extract that date so
that we can sort the records by it.
Unfortunately, there's no built-in function to extract a date value from a
bunch of text. We'll have to figure it out by ourselves. The process is
something like:
- Find a / in the text and go back two spaces - that's the start of
the date
- Find the first space after the slash - that's the end of the date
- If there is no space, you stop where the text stops
- Pull out the text from the start of the date until the end of the
date
- Convert that text into a real date
- Keep Your Fingers Crossed!
This kind of horizontal programming is very common in Excel and it
has its uses. But you've probably already spotted a number of problems!
What if there are no slashes because the date is entered like mm-dd-yyyy?
What if there are no spaces?
These types of errors are better addressed using
Excel VBA to create custom
formulas, but we'll show how to get as far as we can using Excel
formulas.
1. In Cell D2, find a slash in the text and go
back two spaces : =SEARCH("/",C2)-2
2. In Cell E2, find the first space
after the slash:
=SEARCH(" ",C2,D2+1)
3a. In Cell F2,
find out the total length of the text in C2:
=LEN(C2)
3b. In Cell G2, use whichever number works! :
=IF(ISERROR(E2),F2,E2)
4. In Cell H2, get the text out of the
cell:
=MID(C2,D2-2,G2-(D2-3))
5. In Cell
I2, convert the text into a real date:
=DATEVALUE(H2)
And we're done!That's a lot of columns to come up
with a simple date. If you're adventurous, you might like to try what John
Walkenback calls a megaformula and combine all of those steps into one
formula:
=DATEVALUE(MID(C2,SEARCH("/",C2)-2,(IF(ISERROR(SEARCH("
",C2,SEARCH("/",C2)+1)),LEN(C2),SEARCH("
",C2,SEARCH("/",C2)))-(SEARCH("/",C2))+3)))
That should really all
be on one line - in one cell, in fact! Good luck sorting it out! And if you
move cells around on your worksheet, you're done for.
We don't use megaformulas much at I Get It! Development because they're
impossible to maintain. Nonetheless, if you are trying to preserve columns
on your worksheet, you may need to combine some of them.