# COUNTIF a date falls within two dates

#### Tatum2020

##### New Member
This is my current dataset. I am trying to populate the output table to count how many employees in January 2021 were promoted because the month of January 2021 fell between their start and end date.

 Employee start date end date Status 1 2-Nov-20 2-Jun-21 Promoted 1 1-Mar-21 26-Mar-21 Promoted 1 28-Sep-20 30-Apr-21 Not promoted 1 5-Jan-2021 1-Jun-21 Not promoted 1 1-Feb-2021 28-Feb-21 Promoted

Output table:

 Status Jan-21 Feb-21 Mar-21 April-21 Promoted 1 2 2 1 Not promoted 2 2 2 2

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### JamesCanale

##### Board Regular
MrExcelPlayground.xlsm
ABCDE
17Employeestart dateend dateStatus
1812-Nov-202-Jun-21Promoted
1911-Mar-2126-Mar-21Promoted
20128-Sep-2030-Apr-21Not promoted
2115-Jan-211-Jun-21Not promoted
2211-Feb-2128-Feb-21Promoted
23
24Status1/15/20212/15/20213/15/20214/15/2021
25Promoted1221
26Not promoted2222
Sheet22
Cell Formulas
RangeFormula
B25:E26B25=COUNTIFS(\$B\$18:\$B\$22,"<"&B\$24,\$C\$18:\$C\$22,">"&B\$24,\$D\$18:\$D\$22,\$A25)

#### jtakw

##### Well-known Member
Hi,

Try this:

Book3.xlsx
ABCDEF
1Employeestart dateend dateStatus
212-Nov-202-Jun-21Promoted
311-Mar-2126-Mar-21Promoted
4128-Sep-2030-Apr-21Not promoted
515-Jan-211-Jun-21Not promoted
611-Feb-2128-Feb-21Promoted
7
8Output table:
9
10Status1/1/20212/1/20213/1/20214/1/2021<Format as mmm-yy
11Promoted1221
12Not promoted2222
Sheet835
Cell Formulas
RangeFormula
B11:E12B11=COUNTIFS(\$B\$2:\$B\$6,"<="&EOMONTH(B\$10,0),\$C\$2:\$C\$6,">="&B\$10,\$D\$2:\$D\$6,\$A11)

#### jtakw

##### Well-known Member
@JamesCanale , just want to point out, if you change B21 to 1/16/21 or later in January of 2021 in your sample, your formula will produce incorrect results.

#### Tatum2020

##### New Member
Hi,

Try this:

Book3.xlsx
ABCDEF
1Employeestart dateend dateStatus
212-Nov-202-Jun-21Promoted
311-Mar-2126-Mar-21Promoted
4128-Sep-2030-Apr-21Not promoted
515-Jan-211-Jun-21Not promoted
611-Feb-2128-Feb-21Promoted
7
8Output table:
9
10Status1/1/20212/1/20213/1/20214/1/2021<Format as mmm-yy
11Promoted1221
12Not promoted2222
Sheet835
Cell Formulas
RangeFormula
B11:E12B11=COUNTIFS(\$B\$2:\$B\$6,"<="&EOMONTH(B\$10,0),\$C\$2:\$C\$6,">="&B\$10,\$D\$2:\$D\$6,\$A11)
Thank you , it worked!

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Replies
3
Views
55
Replies
19
Views
263
Replies
1
Views
67
Replies
4
Views
199
Replies
5
Views
116

1,130,016
Messages
5,639,559
Members
417,099
Latest member
duhafnusa4

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back