COUNTIF a date falls within two dates

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Employeestart dateend dateStatus
12-Nov-202-Jun-21Promoted
11-Mar-2126-Mar-21Promoted
128-Sep-2030-Apr-21Not promoted
15-Jan-20211-Jun-21Not promoted
11-Feb-202128-Feb-21Promoted


Output table:

StatusJan-21Feb-21Mar-21April-21
Promoted1221
Not promoted2222

I have tried a simple formula: =IF(AND(end_date>=Jan-21), SUM(employees,”0”) but it's not working. Could you please help?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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)
 
Upvote 0
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)
 
Upvote 0
Solution
@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.
 
Upvote 0
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!
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top