use a pivot table with custom columns?

Doug Mutzig

Board Regular
Joined
Jan 1, 2019
Messages
52
Hi Everyone!

I am working with a table of data (essential fields are Year, Month, Date, Shift Start, Start care, End Care, Total care time rounded, ..
In the table I calculate a decimal period for the time between starting patient care and ending patient care with it rounded to the nearest 30 min.

We break up how much time is recorded based on the number of patients into 4 columns: 1-2 patients, 3-4 patients, 5-6 patients, 7-8 patients we then input the highest number of patients, per half hour period
Ex = 3 patients from 4-6:20p and then 1patient is discharged at 6:20p within a 4 hour shift (4-8). This results in a recording of 2.5hrs in the 3-4 patients column and 1.5hrs in the 1-2 patients column. Total hours between columns should never go above 4.0. These patients could have different techs assigned to them but we look at the Shift period (I use shift start) to determine total number of patients in the shift and then record hours in the columns.

Up until now they have been doing this manually in a spreadsheet that I am trying to update and somewhat automate for them (see images). Instead of combining information on one line which makes it difficult to calculate and also know which patient has what times, I have split them into column information for 4 patients (the max each tech can have at a time).

I can pull the data from the table into a pivot table and see how many patients per shift, but I am hitting a wall as I want to then calculate the hours per the 4 columns (1-2, 3-4, 5-6, 7-8). I can't for the life of me figure out how to have excel calculate this.

Sooo what I am trying to do now is a table that has half hour increments (from 30min to 4 hours) as the columns, and and Year, Month, Date, Shift Start for the row labels (i.e. pivot table). What I don't know how to do is have the half hour increments as columns in the pivot table since that are not a part of the base table or how to have the table count the number of patients (from the 4 rounded total patient time columns).

I am also trying to do a table that has the 1-2, 3-4, 5-6, 7-8 patients as columns and sums the total number of hours (but that comes from the rounded total time for the 4 individual patients).

Does anyone know how to do this? Or have a different idea that gets the same end result (really the second table is the key one)

Data Table:
Data_Table.png


Patients per shift:
Patients_per_Shift.png


First Table looking for help on (concept)
End_Product_01.png


Second Table looking for help on (concept) - Primary end result I am looking for
End_Product_02.png
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,127,501
Messages
5,625,166
Members
416,075
Latest member
TechJosh

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