Doug Mutzig
Board Regular
- Joined
- Jan 1, 2019
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
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
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
