"

Chapter 7 – Count and Date Functions

Introduction

Chapter 3 is all about managing and examining dates and functions around those data.

  • Use DateDif to calculate the difference between two dates
  • Use aggregates such as MAX/MIN to examine dates
  • Know when to use ROUND in various scenarios

Guided Exercises
More Practice
Extra Worksheets

Guided Exercises

In your IU OneDrive, open the Chapter07_CountAndDateFunctions file.

The EmployeeAnalysis worksheet contains a small sample set of data related to employees from the city.

But first, let’s explore the DATEDIF function. This function returns the difference in number of days, months, or years between two dates. You give it the earlier date, the later date, and the type of units you want to return (D, M, or Y) as a string in quotes.

=DATEDIF( EARLY_DATE , LATER_DATE, UNITS_TO_RETURN )

If A1 has 6/1/2024 and A2 has 6/30/2024, the following would return 29.

=DATEDIF( A1, A2, "D" )

Let’s work with these functions!

  1. Column C contains the Hire Date for each employee. Using the Hire Date and today’s date in G3, in cell E6, use a function to find the number of years each employee has been employed. Fill the function down through cell E17.
  2. The employees’ current salaries are in column F. All the employees are to receive a raise based on the salary rate increase percentage indicated in cell G2. Beginning in cell G6, use the current salaries and the rate increase to calculate each employee’s new salary. Format the NewSalary values as Currency. Fill the function down through cell G17.
  3. In cell G18, use functions to calculate how much more the company is spending on the employees’ salaries after the raise than they spent on salaries before the raise.
  4. In cell J5, use a function to return the number of employees represented in this sample.
  5. In cell J7, use a function to return the earliest Hire Date.
  6. In cell J9, use a function to return the most recent Hire Date.
  7. In cell J11, use a function to find how many employees have benefits.
Table 7.1: Count Functions
Function Purpose
COUNT Count all cells with number values
COUNTA Count all cells that are not empty
COUNTBLANK Count all cells that are empty
COUNTIFS Count all cells in a range based on some criteria

 

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.

More Practice

What you should work on before our next class

In your IU OneDrive, open the Chapter07_CountAndDateFunctions file.

The Practice worksheet contains data related to transactions that occurred at a furniture warehouse. This worksheet is a review of most of the skills we have learned so far in chapters 1-3.

  1. Center the text in cell A1 across the range A1:K1. Make the font bold, 16pt, with a yellow font color and a dark blue fill color.
  2. The Receipt Numbers are to be listed in column A, beginning with receipt number 1 in cell A6 and continuing down column A with consecutive numbers. Use Excel’s autofill feature to do this.
  3. The PurchaseDates in column B do not look like dates at all! Select B6:B113 and format the PurchaseDate values to look like 14-Mar-2012.
    Hint: You will need to open more formatting options to find the correct format for these dates.
  4. Format the Prices in column E and the S&H (shipping and handling) Fee in column G as Currency.
  5. Format the Discount% values in column F as Percentage with 1 decimal place displayed.
  6. In cell H6, calculate the GrandTotal for the transaction in row 6 using the following formula:

Quantity * Price * (1-Discount%) + S&H Fee

If done correctly, the GrandTotal in cell H6 should be $50. Format the values as Accounting and fill your formula down to cell H113 to calculate each transaction’s GrandTotal. Cell H10 should be $6,313.50.

  1. In cell H114, use a function to find the total revenue from all transactions.

Managers of this furniture warehouse are considering increasing the prices of their items by the percentage indicated in cell K3. They would like to know how this slight increase would have affected the prices and overall grand totals for each transaction.

  1. In cell J6, use a formula to calculate the new price of the item in row 6. Use the current price in cell E6 and the possible price increase percentage in cell K3 to do this. Write your formula so it can be filled down to cell J113 to calculate the new price of each item. Format the new prices as Currency.
  2. In cell K6, use the new price that you just calculated in cell J6 and the same quantity (D6), discount%(F6), and S&H fee (G6) to calculate the New GrandTotal for the transaction in row 6. Fill the formula down to K113 to calculate the New GrandTotal for each product. Format the New GrandTotal values as Accounting.
  3. In cell K114, use a function to find the total revenue from all transactions if the small price increase would have been in effect when these transactions occurred.
  4. In cell J116, write a formula to calculate how much more revenue the company would have brought in if the new prices had been in effect when these transactions occurred.
  5. In cell N3, use a function to display today’s date. Format cell N3 as a Short Date.
  6. In cell M6, use a function to calculate how many years have passed since the transaction in row 6 has taken place. Fill the function down to cell M113.
  7. In cell N6, use a function to calculate how many months have passed since the transaction in row 6 has taken place. Fill the function down to cell N113.
  8. In cell O6, use a function to calculate how many days have passed since the transaction in row 6 has taken place. Fill the function down to cell O113.
  9. In cell D117, use a function to find the average quantity sold per transaction. Round the value to 1 decimal place.
  10. In cell D118, use a function to find the highest number of items sold in one transaction.
  11. In cell D119, use a function to find the lowest number of items sold in one transaction.

You may now save and close your Excel workbook. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.

Extra Worksheets

CountFunctions Worksheet

We have a little bit of data here that might represent how many bookings a nail technician has at a nail salon over the course of two weeks. So we have the days and then we have the number of bookings. We’re going to answer some questions using functions and not just figuring out the answer ourselves and typing it in. It would be easy to figure out the answers right now since we have a small sample of data, but that is so you can practice and check your work. This prepares you for when you have larger sets of data. We want to use functions as much as possible when we have larger sets of data or if the data changes so that our functions do the work for us and we can trust our results are correct. We don’t have to worry about checking our answers with larger sets of data.

  • First, how many days are represented?
    • o Try COUNT first – why does this not work?
    • o Retry with the COUNTA function, remember this counts non-blank cells.
  • How many days have bookings?
    • o Use COUNT again.
    • o Now, you might be wondering, couldn’t we just use COUNTA for that one, too? And yes, you can but only because of how this data is set up. You won’t always be able to just use COUNTA, for example, what if instead of these cells being blank, they had something in them like “None” or “closed.” COUNTA would still count those cells and that’s not right. So, if you know you are just counting numbers, then use COUNT.
  • How many days do NOT have bookings?
    • o COUNTBLANK is your friend in this one.

DateFunctions-Extra Worksheet

In cell B3, we want today’s date to be displayed. BUT, I want today’s date to always be displayed no matter when this file is opened and we don’t want to type in today’s date because it won’t change. Instead, we use the TODAY function. The TODAY function always displays the current date.

Here in Column A, we have some of our K160 and K201 TAs birthdays from a few years ago. Remember, dates are numbers, so these are the number of days that had passed since 1/1/1900 on the day you were born. But, they make no sense to us. In Excel, the numbers associated with dates are serial numbers.

  • Click in cell A6, then on your keyboard, press Ctrl, Shift, and the down arrow. Now the column is selected.
  • Format as Short Date.

Now we need to calculate the difference between dates, but not just using math. The function we use for this is called the DateDif function as we learned above. Do that for columns B, C, and D.

So, what about Weeks in column E? – you can’t put in “W” it doesn’t work.

There are always 7 days in a week. So, if we have the number of days and every 7 days is a week, we can divide to get the number of weeks. Our TAs are not a whole number of weeks old. There’s a bunch of decimals. That will happen when you divide, especially by 7.

  • Try to use ROUND in front of this DATEDIF, to 0 (zero) decimal places).
  • Change that to use INT().
  • Fill that down, and now you have accurate data.
  • Decrease the decimals so you see none (just whole numbers).

Remember, DATEDIF + WEEKS = INT (not ROUND)

License

K160: Introduction to Business Decision Making 2e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.

Share This Book