Counting between date ranges

[Sleepingsouls]

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"\[$-C09\]dd\\-mmm\\-yy\;\@";}--></style>Hi Guys,

I work for a large company, we have a very fluid team dynamic and are often moving people between teams to get the best out of everyone. I have recently tried to set up an excel spreadsheet to let me know how many people will be on my team on any given day.

Below is a quick example of what I am trying to do - I want to give a date range people will be working for me and have that converted into a number of people I will have on a date.

Thanks in advance.

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"\[$-C09\]dd\\-mmm\\-yy\;\@";}--></style>

Employee	Start Date	End Date
a	01-Jan-18	01-Feb-18		01-Jan-18	0
b	01-Jan-18	20-Jan-18		02-Jan-18	0
c	08-Jan-18	01-Feb-18		03-Jan-18	0
d	20-Jan-18	01-Feb-18		04-Jan-18	0
e	16-Jan-18	29-Jan-18		05-Jan-18	0
				06-Jan-18	0
				07-Jan-18	0
				08-Jan-18	0
				09-Jan-18	0
				10-Jan-18	0
				11-Jan-18
				12-Jan-18
				13-Jan-18
				14-Jan-18
				15-Jan-18
				16-Jan-18
				17-Jan-18

<!--StartFragment--> <colgroup><col width="87" span="6" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[Joe4]

Welcome to the Board!

Let's say that your Start Dates are in cells B2:B6
and your End Dates are in cells C2:C6
and dates you want to list/check are in column E starting in cell E2.

Then place this formula in F2 and copy down for all values in column E:

=SUMPRODUCT(--(B$2:B$6<=E2),--(C$2:C$6>=E2))

 

[Sleepingsouls]

Thank you so much! I was using Countifs - I spent hours trying to get it to work but just couldn't get any other value than 0.

Again Thank you

 

[Sleepingsouls]

Maybe you can also help me understand this formula. the -- what does that mean. (I thought I was pretty well versed in Excel... Apparently not)

 

[MARK858]

The double unary (--) converts True and False to 1 and 0 so it can be calculated. See the link below...

https://exceljet.net/excel-functions/excel-sumproduct-function