Chapter 3 – 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

In your IU OneDrive, open the Chapter03_CountAndDateFunctions file. You may want to try to turn on AutoSave in the upper left corner. If it doesn’t load, that’s okay, Excel will prompt you to save when you close your file at the end.

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

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 cell E5, use a function to find the number of years each employee has been employed by this company. Fill the function down through cell E16.
  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 H2. Beginning in cell G5, use the current salaries and the rate increase to calculate each employee’s new salary. Format the NewSalary values as Accounting. Fill the function down through cell G16.
  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 3.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.

More Practice

What you should work on before our next class

In your IU OneDrive, open the Chapter03_CountAndDateFunctions file. You may want to try to turn on AutoSave in the upper left corner. If it doesn’t load, that’s okay, Excel will prompt you to save when you close your file at the end.

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 the 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.

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.