Tricks To Use Excel Functions With Dates

Excel function tutorials

If you have tried using dates in excel, you will know exactly what type of problem that I’ve encountered too many times. A large portion of the emails that we receive with excel related questions actually are related to using dates. Microsoft Excel is robus in many ways but I would say that manipulating dates is not one of the strenghts. Why? I’m not sure. It could be because there so many different formats depending on where you are and your computer settings. I’m not sure. But what I do know is that it creates many headaches. There are no bulletproof solutions but I will show you how I deal with dates in excel.

An Example

First off, as is always the case, I will try to start by using an example. In this case, we received a file from a reader that is trying to group, for a large excel file, the dates into one of 4 groups:

-Over 10 years old
-5 to 10
-3 to 5
-0 to 3

So basically, I have some dates and need to set them in those ranges, here are the instructions:

If Cell A2 (Last Receipt Date) > 12/2/2009 but less than 12/31/2011 return 0-3 years
If Cell A2 (Last Receipt Date) > 12/2/2006 but less than 12/2/2009 return 3-5 years
If Cell A2 (Last Receipt Date) > 12/2/2001 but less than 12/2/2006 return 5-10 tears
If Cell A2 (Last Receipt Date) < 12/2/2001 return over 10 years Basically, I will transform the statements into the following: =If date is older than 12/2/2001, +10 years, if date is older than 12/2/2006, if date is older than 12/2/2009, if date is older than 12/31/2011 I could try finding out the range but that is not necessary in this case. What I started by doing is writing the dates down as you can see here:

Then, next to it, I copied the same numbers but then changed the format from date to “numbers” or “general”, by clicking here:

If when you change the format, you do not get numbers like this, that means that excel does not correctly identify the dates as “dates”.

Once that is done, I used the following formular in B2

=IF(A2<$E$14,"+10",IF(A2<=$E$13,"5-10",IF(A2<=$E$12,"3-5",IF(A2<=$E$11,"0-3",""))))

As always, you can download the excel file here

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************