# Graphing Start and End times for multiple data sets (by half hour)

1. ## Graphing Start and End times for multiple data sets (by half hour)

I have uploaded the Start and End times where I rounded by the half hour for patients coming into a hospital unit and leaving the unit.
I want to be able to graph the duration of each patient's stay, which will show our volume throughout the day.
An example of what the final product should look like is in the attachment.

2. ## Re: Graphing Start and End times for multiple data sets (by half hour)

After a couple of days with no response, I will venture something.

Looking at your "sample of what I need" sheet, it looks like it should be a COUNTIFS() function of some kind. In "pseudo-function code" =COUNTIFS(day of week,B\$1,INPACU time,"before "&\$A2,OutPACU time,"after"&\$A2). (help file for the COUNTIFS() function, if you are unfamiliar: https://support.office.com/en-us/art...c-aa8c2a866842 ). At this point, if I can get some suitable "criteria ranges" in the "Original timestamps" sheet, then I can use this COUNTIFS() function to count the records.

Your "day of the week" criteria seems to spill across midnight so that a simple WEEKDAY() function will be insufficient to determine weekday. Your weekday seems to span from 8 AM to 2 AM of the following morning. I decided that, if I were doing this, I would like to "map" the timestamps onto a "standard week". Since Excel stores dates and times as numbers, a simple MOD(date time stamp,7) will return a value of 0 to 7 (corresponding to the first Sat to Fri week in Excel's calendar system). I would prefer a Sun to Sat week, so I modify this to move Saturday to the end. =MOD(date time stamp,7)+IF(WEEKDAY(date time stamp)=7,7,0). In C2 of the Original time stamps, I enter =MOD(A2,7)+IF(WEEKDAY(A2)=7,7,0). Copy and paste/fill into columns C and D of Original Timestamps. This formula returns a number from 1 to 8 where 1.000 corresponds to midnight Sunday morning and 8.0000 corresponds to midnight Saturday night.

I'm not sure how you want to handle the missing timestamps, so I am just letting Excel treat those as 0 (midnight Saturday morning), which means that these will probably be erroneously included in some of the counts.

Back to the output table on Sample of what I need, now I just need a COUNTIFS() to see if the day of the week + time stamp value is between the input time and the output time of each record and count how many. Because the day of the week text is not very useful, I overwrite the days of the week at the top with the corresponding number (2 for Mon, 3 for Tues,...,6 for Fri). Then my COUNTIFS() function can be =COUNTIFS('Original Timestamps'!\$C\$2:\$C\$1000,"<="&B\$1+\$A2,'Original Timestamps'!\$D\$2:\$D\$1000,">="&B\$1+A\$2).

I get different results from the values you show in your file, and I am not sure why. Spot checking a few examples suggest that the result is close. There feels like there will be some need for additional development (how to exclude overnight stays as your note on the chart suggests), how to deal with the blanks in the starting data, and probably some things I did not understand from your description. That seems like a possible starting place to build from, though.

Will something like that work for you?

3. ## Re: Graphing Start and End times for multiple data sets (by half hour)

Thank you so much for your detailed explanation. I realized in pasting the charts and graph into the shared file, I lost the values of the original graph.

4. ## Re: Graphing Start and End times for multiple data sets (by half hour)

In response to PM: I looked at the new file. Unfortunately, the new file does not include the raw data. I can only recommend the same sequence I suggested earlier. Add helper columns to the raw data that will be suitable for a COUNTIFS() function. Then use a COUNTIFS() function on the summary page to count up how many are present at each half hour mark. Was there part of my suggested sequence of steps that you did not understand or that you had difficulty implementing?

There are currently 1 users browsing this thread. (0 members and 1 guests)