Determining patient census at the end of each month and on demand

joeystraw

New Member
Joined
Feb 2, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
I have a basic table with patient names, admission dates and discharge dates. I need a way to determine the head count of admitted patients at the end of every month (i.e. monthly report) as well as on demand (entering a specific month/year and getting a census). Current census count is simple and straightforward...if patient has an admit date but no discharge date then the count with these 2 criteria. Querying past census counts is what I can't figure out.

Book2.xlsx
ABCDEFG
3BedCurrent Patient?Last NameFirst NameAdmit DateAdmitted?Discharge Date
4C-133YBoyleJacoby12/24/2018TRUE2/2/2020
5C-129YRodriguezJaime2/25/2020TRUE6/13/2020
6C-131YSnowCarsen4/18/2020TRUE
7C-124YSteeleDrake5/26/2018TRUE
8B-113YCuevasRoyce1/8/2018TRUE9/1/2020
9B-116YRussellColeman1/8/2019TRUE5/15/2020
10B-112YRichardBeckett2/19/2019TRUE4/30/2019
11A-110YBryanGeorge5/10/2018TRUE9/25/2019
12C-132YSampsonLandyn7/8/2020TRUE
13C-122YValentineTrystan3/20/2018TRUE4/23/2019
14C-128YNealAryan11/3/2019TRUE2/17/2020
15A-108YLaneAdriel10/23/2018TRUE10/22/2019
16A-104YPerkinsCANDICE2/11/2018TRUE1/3/2019
17A-107YBowersKINDRA11/16/2019TRUE
18B-120YBairdPAIGE4/7/2018TRUE
19C-123YMoraYEN1/25/2020TRUE11/6/2020
20B-121YBirdBRITTNEY2/18/2018TRUE8/7/2019
21C-127YMaloneJANICE5/13/2019TRUE5/17/2020
22B-114YWalterANTOINETTE12/31/2017TRUE1/11/2018
23B-117YMannMARY2/18/2018TRUE1/1/2020
24B-111YWheelerJUDY6/29/2017TRUE
25B-118YMorganSHANNON4/16/2018TRUE8/8/2020
26A-105YToddBRYANA2/26/2018TRUE5/24/2018
27A-103YWolfeVIVIANNA11/5/2018TRUE7/3/2019
Sheet1
Cells with Data Validation
CellAllowCriteria
A4:A27List=BED
B4:B27List=Current_Pt
F4:F27List=TRUE_FALSE
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Hi JoeyStraw,

Can you test if this works for you?

JoeyStraw.xlsx
ABCDEFGHIJK
3BedCurrent Patient?Last NameFirst NameAdmit DateAdmitted?Discharge DateMonthYearHead Count
4C-133YBoyleJacoby12/24/2018TRUE2/2/20201202011
5C-129YRodriguezJaime2/25/2020TRUE6/13/2020
6C-131YSnowCarsen4/18/2020TRUE
7C-124YSteeleDrake5/26/2018TRUE
8B-113YCuevasRoyce1/8/2018TRUE9/1/2020
9B-116YRussellColeman1/8/2019TRUE5/15/2020
10B-112YRichardBeckett2/19/2019TRUE4/30/2019
11A-110YBryanGeorge5/10/2018TRUE9/25/2019
12C-132YSampsonLandyn7/8/2020TRUE
13C-122YValentineTrystan3/20/2018TRUE4/23/2019
14C-128YNealAryan11/3/2019TRUE2/17/2020
15A-108YLaneAdriel10/23/2018TRUE10/22/2019
16A-104YPerkinsCANDICE2/11/2018TRUE1/3/2019
17A-107YBowersKINDRA11/16/2019TRUE
18B-120YBairdPAIGE4/7/2018TRUE
19C-123YMoraYEN1/25/2020TRUE11/6/2020
20B-121YBirdBRITTNEY2/18/2018TRUE8/7/2019
21C-127YMaloneJANICE5/13/2019TRUE5/17/2020
22B-114YWalterANTOINETTE12/31/2017TRUE1/11/2018
23B-117YMannMARY2/18/2018TRUE1/1/2020
24B-111YWheelerJUDY6/29/2017TRUE
25B-118YMorganSHANNON4/16/2018TRUE8/8/2020
26A-105YToddBRYANA2/26/2018TRUE5/24/2018
27A-103YWolfeVIVIANNA11/5/2018TRUE7/3/2019
Sheet1
Cell Formulas
RangeFormula
K4K4=COUNTIFS($E$4:$E$27,"<="&EOMONTH(DATE($J$4,$I$4,1),0),$G$4:$G$27,">="&EOMONTH(DATE($J$4,$I$4,1),0))+COUNTIFS($E$4:$E$27,"<="&EOMONTH(DATE($J$4,$I$4,1),0),$G$4:$G$27,"")
 
Solution

joeystraw

New Member
Joined
Feb 2, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Works perfectly...thank you for your assistance and quick reply!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,567
Members
417,151
Latest member
ChickenTenderer

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