# use a pivot table with custom columns?

#### Doug Mutzig

##### Board Regular
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:

Patients per shift:

First Table looking for help on (concept)

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

### Excel Facts

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

Replies
0
Views
153
Replies
1
Views
48
Replies
25
Views
2K
Replies
1
Views
73
Replies
0
Views
195

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.

### 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