Question
How do you use date and time formulas in Excel?
Answer
In this example we have a list of birthdays. Our objective with this data is to find out how many birthdays occurred more than six months ago, and how many occurred five years ago. To start we want to know today's date.
- Click in cell A2. Type in =TODAY() and press ENTER. The computer will automatically enter in today’s date.
- Click in cell B2. In this example, we want this value to be five years before today’s date. To do this, the formula must be =EDATE(A2,-60) (60 months in five years) and press enter.
|
|
IMPORTANT: If you simply get a numerical value instead of a date, that means you have to format the cell to be a date. Right click on the cell and click Format Cells. In the Number tab of the format dialog box, choose Date. Choose whatever date format you wish and then click Ok.
|
- Select your current data (A4:B10) and go to the Home Tab. Click on the Sort & Filter dropdown arrow and select Filter. Now in the column you should see a dropdown arrow. Click on the arrow under and select Date Filters and Between.
-
You will be prompted with an AutoFilter box which we will use to help sort your data.
- In the is after or equal to field, put the date that occurred five years ago
- In the is before or equal to field, put today’s date Click Ok
- Any dates ranging between the date five years ago and today’s date will show, but all others will disappear from view.
- We can do basically the same thing we just did by using a formula. We will use the AND formula because we have two things to check:
- First, click the down arrow by Birthday and click Clear Filter from Birthday
- Second, add a new column heading in C4 reading Today to 5y Ago
- Click your mouse in C5 and enter =AND( click on cell B5 is greater than or equal to only B2, and comma B5 is less than or equal to only A2.) =AND(B5>=$B$2,B5<=$A$2)
|
|
TIP: Note that we are using an absolute reference for A1 and A2 because we don’t want our formula to reference cells lower than those if/when we drag/copy the formula.
|
- Click enter, it will show us the statement is TRUE or FALSE, depending on your data.
- Now we want to have this formula we just created to be applied to the entire list of birthdays. To do this roll your mouse over C5 until you get the + symbol from the mouse. Once the mouse pointer looks like the +, drag all the way to the bottom of the column and release. Each column will now have a TRUE or FALSE in the box indicating that the birthday was between six months to five years ago, and will indicate FALSE if the birthday was within the last five years.
For more information about Formulas and Functions in Excel, please view the full tutorial from the CTL here: Excel Part 3
If you need further assistance, please click the CTL Support Request button on this page