COUNTIF a date falls within two dates

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
31
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

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
Joined
Jan 13, 2021
Messages
161
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Nov 25, 2020
Messages
31
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top