Calculate number of people in meetings on specific date, between two times

[dode629]

I have meetings listed by Date, Start Time, End Time, and number of people in the meetings.

I want to calculate how many people are in meetings on a certain date between two times.

I am running into an issue if I select say 11:00PM - 2:00AM because that is not one day, it is two days.

What formula can I use to take this into account and calculate the number of people in meetings between two dates AND between two times, all in one formula?

Here is what I currently have:
ColumnW = Date
ColumnX = Start Time
ColumnY = End Time
ColumnZ = Number of people
H3 = Date being searched
H4 = start time being searched
H5 = end time being searched
H6 = this cell will return the number of people in a meeting at the specified date and start/end time

=SUMIFS(Z:Z, W:W, H3, X:X, ">=0", X:X, CONCATENATE("<", H5), Y:Y, CONCATENATE(">", H4))

 

[Sachin.Medhe]

use given function

=Countifs(Need to give date range(W:W),H3,Need to give start time range(X:X),H4,Need to give end time rane(Y:Y),h5,Need to give people name range(Z:Z),"<>")

 

[Peter_SSs]

I'm not sure what the logic would be. For the sample data below, what would be the result in the blue cell?

- At 9:35 PM (which is in the search range) 0 people were in meetings
- At 10:05 PM (which is in the search range) 9 people were in meetings
- At 11:05 PM (which is in the search range) 5 people were in meetings

Perhaps I haven't understood the question, or perhaps my sample data is not possible.

Some of your sample data (not too much), the expected results and an explanation of why that is the expected result(s) may help clarify.

Note that my dates are in d/mm/yyyy format.

Excel Workbook
	G	H	I	W	X	Y	Z
1				Date	Start	End	No
2				1/03/2011	8:00:00 AM	2:00:00 PM	5
3	Search Date	2/03/2011		2/03/2011	8:00:00 PM	11:00:00 PM	4
4	Search Start	9:30:00 PM		2/03/2011	10:00:00 PM	2:00:00 AM	5
5	Search End	11:50:00 PM		3/03/2011	1:00:00 AM	3:00:00 AM	12
6	Result
Attendees

 

[dode629]

Hi Peter - here is some sample data. Basically, I am calculating because I want a maximum of 40 people in a meeting at one time, so I want to see how many people may join a meeting at a given time and date. I left out part of my formula sorry, it is actually:

A16 = 40

So basically, I am taking the sum of people in meetings at the given date and times and then returning the number of people who may join a meeting.

=A16-(SUMIFS(Z:Z, W:W, H3, X:X, ">=0", X:X, CONCATENATE("<", H7), Y:Y, CONCATENATE(">", H6)))

<TABLE style="WIDTH: 428pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=571 border=0><COLGROUP><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 8886" width=243><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 182pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #95b3d7" width=243 height=20>Meeting Name</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7" width=68>Date</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7" width=85>Start Time</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7" width=79>End Time</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #95b3d7" width=96># of People</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test1</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:00 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:30 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test2</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:00 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:30 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test3</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/12/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:00 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:30 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test4</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:00 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1:30 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test5</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:00 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>5:00 AM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Test6</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12:30 AM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1:30 PM</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>2</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 371pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=495 border=0><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 320pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=427 colSpan=7 height=20>Enter the Start Date of the Meeting (MM/DD/YYYY):</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=68>1/11/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" colSpan=7 height=20>Enter the Start Time of the Meeting:</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">12:00 AM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" colSpan=7 height=20>Enter the End Time of the Meeting:</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1:30 AM</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 371pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=495 border=0><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 320pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=427 colSpan=7 height=20>Number of people who may join a meeting:</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=68>30</TD></TR></TBODY></TABLE>

So 10 people are in meetings at the given date and times, thus 30 people may join a meeting at that date and time.

 

[dode629]

Also, your blue box would yield 31 (40-9). The issue occurs if you change the end time to say 1:00AM, the result would be 36 (40-4), but the expected result is actually 31 (40-9).

This is because the formula sums the number or attendees if the start time is a positive number, if the end time is greater than the start time (this is my issue), and if the start time is less than the end time (also my issue).

 

[Peter_SSs]

So 10 people are in meetings at the given date and times

How did you calculate that?
Which 10 people?
Which meetings?