Counts between a start and end date, with end date usually null. | General Excel Questions & Answers

Counts between a start and end date, with end date usually null.

Karen Hill

Member

Dashboards

Members

Forum Posts: 3

Member Since:
April 9, 2017

Offline

May 9, 2017 - 1:36 am

I'm trying to get a count of student enrollments by month, but am struggling with which formula to use. I keep getting stuck on how to look between two dates, and check if one of them has a null value. I've attached an example excel worksheet that I hope clearly explains what I'm trying to do.

Enrollments start on July 1st, so for the first month of school the date range is longer. After that they are a regular month range of the 1st to last days of the month.

Thank you for any help you can offer!

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

May 9, 2017 - 11:14 am

Hi Karen

See if this helps. I don't have sufficient data to test but the 1st part (Sept and before) looks OK. I am using UK date (dd/mm/yyyy).

Can you clarify how do you want the answer to be like for your question below?:

"I keep getting stuck on how to look between two dates, and check if one of them has a null value."

Is it just a simple count or need the output to be in a specific format?

Adding an attachment (with sufficient data) with the expected result would help.

Sunny

Karen Hill

Member

Dashboards

Members

Forum Posts: 3

Member Since:
April 9, 2017

Offline

May 9, 2017 - 10:25 pm

Hi Sunny,

Thank you for looking at this! I thought the attachment did have sufficient data, as it's what I have to work with. I only changed the stateIDs. And yes, I am looking to count the number of students. At one point I had a 'counter' column and was trying to use a sumifs - but again, it's the dates I'm struggling with. I could have mentioned that I'm trying to take this raw data and go to the analysis stage and get it ready for slicers (Excel 2016), so I can get the student enrollment numbers by month. I will then need to do the same thing for teachers so I can see the ratio between students and teachers throughout the school year.

This is what I'm trying to get to (in pseudo code):

For Oct: Count if StartDate <= 10/31/2016 AND (EndDate ="" OR EndDate > 10/31/2016)

For Nov: Count if StartDate <= 11/30/2016 AND (EndDate = "" OR EndDate > 11/30/2016)

I think your formula is close, but it doesn't handle the blank dates (which means a student has not exited - they are still here so need to be counted in the enrollment numbers. I don't know how to do the OR piece in the formula to check for blank cells.

=COUNTIFS(Table1[School],$G15,Table1[StartDate],"<="&I$14,Table1[StartDate],">="&EOMONTH(I$14,-1),+1)

Again, thank you for your help!

Karen

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

May 10, 2017 - 3:03 am

Hi Karen

From my understanding, you wanted a count As At a certain month (after deducting Ended students)

i.e. if 98 students joined in Sept'16 and none left for the year, then in Oct, Nov and Dec '16 the count will still be 98 for each month. If 1 student left in Jan'17 and another in Feb'17, then the count will be 97 and 96 respectively. Am I correct?

I have split the answer into two tables just to be sure. One shows the total count (before deducting the Ended students) and another the count of the Ended students As At the Month. The difference between them is the answer you are looking for.

If it is, then you can just easily join the two formulas together as one.

I hope I got it right this time.

Sunny

Karen Hill

Member

Dashboards

Members

Forum Posts: 3

Member Since:
April 9, 2017

Offline

May 11, 2017 - 4:04 am

Hi Sunny,

Yes, that is what I need to do. I tried joining the two formulas, but I'm not getting an expected result. It appears to only see the latter part. I'm taking the dashboard course and I hope to get more savvy in these areas! Can you see what's wrong with the formula below? And also, the "<>" portion in the formula - is that where it is handlling the null values?

=COUNTIFS(Table1[School],$G16,Table1[StartDate],"<="&H$14,Table1[School],$G26,Table1[EndDate],"<>"&"",Table1[EndDate],"<="&H$14)

Thank you!

Karen

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

May 11, 2017 - 9:10 am

Hi Karen