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>[TABLE="width: 522"]
<!--StartFragment--> <colgroup><col width="87" span="6" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]Employee[/TD]
[TD="width: 87"]Start Date[/TD]
[TD="width: 87"]End Date[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="class: xl63, align: right"]01-Jan-18[/TD]
[TD="class: xl63, align: right"]01-Feb-18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]01-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="class: xl63, align: right"]01-Jan-18[/TD]
[TD="class: xl63, align: right"]20-Jan-18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]02-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="class: xl63, align: right"]08-Jan-18[/TD]
[TD="class: xl63, align: right"]01-Feb-18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]03-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="class: xl63, align: right"]20-Jan-18[/TD]
[TD="class: xl63, align: right"]01-Feb-18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]04-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="class: xl63, align: right"]16-Jan-18[/TD]
[TD="class: xl63, align: right"]29-Jan-18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]05-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]06-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]07-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]08-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]09-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]10-Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]11-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]12-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]13-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]14-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]15-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]16-Jan-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]17-Jan-18[/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[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