Look Who it Was Random video

Excel - For School ADM Calculation - Does This Date Range Contain a Certain Date - Episode 1133

732 views · Published 29 October 2009 · 2:39 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: All Public Schools track a Average Daily Membership or ADM. Given a list of students with start and end date, how many of those records include October 1?

Welcome back to the MrExcel netcast! In this episode, we will be discussing a common problem faced by many schools - how to calculate the number of students enrolled on a specific date. This is a crucial piece of information for schools as it often determines their funding. So, let's dive into the world of Pivot tables and see how we can solve this problem using Excel.

I recently received a call from Patricia, who works at a school in Arizona. She needed to figure out how many students were enrolled on October 1st. This is a question I have encountered many times before, as our school here in Ohio also receives funding based on the number of students enrolled on a specific date. So, I immediately recognized the problem and was happy to help Patricia find a solution.

The first step in solving this problem is to use an IF statement. However, in this case, we need to check for two conditions - the start date and the end date. To do this, we will use the AND function. We will check if the start date is less than or equal to the date we want to analyze, and if the end date is greater than or equal to the same date. We will then assign a value of 1 if the conditions are met, and 0 if they are not. This will allow us to easily count the number of students enrolled on the specified date.

To ensure that our formula works correctly, we did a quick test by changing the dates on one of the records. And as expected, the formula accurately calculated the number of students enrolled on the new date. This is a great way to double-check our work and ensure that our formula is working correctly.

I hope this tutorial was helpful in solving a common problem faced by schools. Thank you for watching this netcast from MrExcel. Don't forget to like, share, and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) The common problem of determining enrollment on a specific date
(00:21) Using an IF statement with an AND function
(01:01) Testing the formula
(01:42) Summing up the results
(01:52) Testing the formula with different dates
(02:02) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Analyzing data with Pivot tables
Calculating enrollment on a specific date in Excel
Common problem in school districts: calculating enrollment on a specific date
Counting records based on date conditions in Excel
Formula to check if a date is within a range in Excel
How to use IF statement in Excel
Measuring enrollment in a school district using Excel
MrExcel netcast: solving data analysis problems with Excel
Summing values based on conditions in Excel
Testing formulas in Excel
Using AND function in Excel
YouTube video on Pivot table analysis


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152709/

Patricia called the other day with a problem which will be familiar to everyone who works in a public school district. The state pays the school for every student who is enrolled on the magic date of October 1. Given a start date and end date, how can you tell if the date is in that range? Episode 1133 shows you how.

More from this channel