Excel Formulas Halfwit to Hero

The Tutorials
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 
Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10

Chapter 5

Calculations with Dates and Time

In this chapter you learn how to work with dates and times. Great news--most of the formula writing skills you developed in the last two chapters still apply!

Exercise #1: The new hire

In this exercise you get to try your hand and some basic date calculations.

  1. Begin by opening the spreadsheet The new hire.xlsx. This worksheet calculates some important dates for a newly hired employee. The date the employee was hired is already entered (in cell B4), although you can change that if you'd like. Your job is to fill in the formulas for other bits of missing information in column B.

  2. Start with cell B3. It needs the current date. However, you can't just type in the date (like 3/23/2019). Instead, you need a way to make sure this cell always has the current date, even if you open the workbook tomorrow or next week. Excel has a function that does the job. If you remember it, write the formula.

    The function is TODAY. When you use TODAY, Excel grabs the current date, and refreshes the cell to make sure it never goes out of date. Here's the formula you need:

    =TODAY()

    Don't forget the brackets after the word "TODAY"

  3. Next, you need to fill in cell B6 with the number of days since the employee was hired. To calculate this, you can use simple math (no functions needed) and the information in cell B4 and B3. Write the formula now.

    Simple subtraction does the trick. You need the difference between today and the hire date:

    =B3-B4

    This works because every Excel date is actually a number of days (measured from the long ago year 1900).

  4. Now check out cell B7. You need to find the date that's 90 days after the hire date in cell B4. Once again, simple math does the trick, no functions required.

    Just add the extra days to the hire date:

    =B4+90

    The result is the new date 90 days in the future.

  5. Cell B8 is more interesting. Now you need to find a date that's exactly 6 months in the future. But here's the problem: you don't know how many days are in each of these months. Excel has a way for you to add months instead of days, but it requires a little more work. First you need to deconstruct the date into its components, then you need to add your months, and finally you need to stick it back together. Can you figure out what functions you'll use to take it apart, and what function you'll use to reassemble it?

    You need YEAR, MONTH, and DAY to pull the date apart, and DATE to reassemble it.

  6. Take a crack at writing the formula in cell B8. The trick is to use nested functions. The DATE function creates the date using three parameters. Inside the DATE function, you set each parameter using corresponding YEAR, MONTH, or DAY function.

    Remember to add 6 to the month number to move the date forward:

    =DATE(YEAR(B4), MONTH(B4)+6, DAY(B4))

  7. Cell B9 is an easy one. Don't overthink it--you should be able to calculate the number of days to the performance review using date math and no functions.

    Just subtract the current date from the review date:

    =B8-B4

  8. The formula you need to cell B11 is similar to the one you used in cell B8. Once again, you're deconstructing a date and putting it back together. There are two differences: you're only adding one month, and the pay date is on the 15th of the month, no matter what.

    Here's the formula that adds one month and locks in the day on the 15th:

    =DATE(YEAR(B4), MONTH(B4)+1, 15)

  9. Cell B12 is similar to cell B8 and B9. Don't be lazy and add 365 days, because this won't get the right anniversary if new employee is hired in a leap year. Instead, use the date breakdown and reassembly trick. By now, you should be getting pretty familiar with it.

    This time you're changing only the year information:

    =DATE(YEAR(B4)+1, MONTH(B4), DAY(B4))

Exercise #2: Clock the commute

Now it's time to practice your formula skills with time values.

  1. Begin by opening the spreadsheet Clock the commute.xlsx. This worksheet has a list of departure and arrival times for a series of trips to work.

  2. Move to cell D4. Here you need to write a formula that calculates the time taken on October 3. The information is in cell B4 and cell C4. Can you write the formula you need?

    Basic subtraction does the trick to find the amount of time between B4 and C4:

    =C4-B4

  3. Right now the formatting in cell B4 is wrong. Excel thinks you're trying to refer to a time of day, but you really want a time interval. To fix the problem, move to the cell, find the format list in the Home►Number section of the ribbon, and choose More Number Formats. Excel shows the Format Cells window.

  4. In the Category list, click Custom. Scroll down a little until you find the time format with square brackets [h]:mm:ss. Click it and click the OK button.

    This is the format you want:

    Now you should see the commute time in cell D4 (it's 27 minutes).

  5. Using the magic of Excel formula copying, you should be able to fill in cells D5 to D14.

    The absolute fastest way to do it is to move to press Ctrl+C in cell D4. Now select all the cells underneath, from D5 to D14 (just click on D5 with the mouse and drag down until you get to D14). Finally, press Ctrl+V to paste a copy of the formula into all these selected cells. Excel adjusts each copy to use the commuting times in the same row.

  6. Your last challenge is to fill in a formula in cell G4 that calculates the average commute time. What function should you use?

    You can use the normal AVERAGE function. It works with time values in the same way that it works with ordinary numbers. (You could use other functions like MAX and MIN just as easily.)

  7. Now write the formula that calculates the average. If you get an average of 28 minutes and 55 seconds, you did it correctly.

    There's no trick to this. Just use a range that grabs all the cells with commuting times:

    =AVERAGE(D4:D14)

PREFER PAPER? If you'd rather do your work offline, you can print this page. Use this button to expand all the hidden sections and then print the exercises for this chapter: