I can not always go by the patients' admit date, due to the fact that if the baby isn't a sick baby, we don't get to count that admission. So, it is quite possible that the baby could have worsened a few days after being born, and then had to be moved to the SCN/NICU. Like I said, those are my exceptions, and I will just know to look for those type of scenarios, and correct my data accordingly.

Since we should NEVER admit a patient back to the hospital, using the same account #, I would prefer that those are flagged, so that I can bring it to our admitting department's attention - this would have to be corrected on their end. SO...we can just highlight it, and then I will follow up. I do not foresee that this would be an issue.



We calculate days, admits, etc. on a daily basis, for all of our patients. For the SCN, that department was keeping manual stats that had to be reported once a week, which included # of admits, days, and now our administrators want it compared to last year as well. So, I have dumped in all of 2013, and will be dumping in the 2014 data on a separate tab. I would need to see admits and days (days would be just a count, by month, for every day they are housed in the SCN/NICU). Our final output wouldn't include patient account #'s - just totals by month.

Wait, I'm missing something. Isn't the patient number in col B the baby's ID? I mean, the patient is the baby, not the mother, right? And isn't col C the date the patient entered the NICU? Apparently one or both of those assumptions are wrong, if you can't always go by the admit date.

About desired output: What I'm hearing is that it worked alright to do this manually on a daily and weekly basis, but a) to do the comparison for a whole year's worth you need Excel to do a lot of the work for you, and b) since you're doing that you may as well make it work for you on the weekly stats too. So we're talking about two kinds of output: A comparison of 2013 with 2012, and (if it can be done conveniently) a better way of doing the weekly summary. And for that matter you have to collect daily numbers too. But first things first: The whole-year comparison is the one that would cost you the most effort.

Now, I'm hearing two different things about that:

1. "....manual stats that had to be reported once a week, which included # of admits, days, and now our administrators want it compared to last year as well". That sounds like a week-by-week count of 2013's admissions and number of days' stay, compared week-by-week with 2012.
2. "Our final output wouldn't include patient account #'s - just totals by month." Not by week, after all, then?

And you want "a count, by month, for every day they are housed in the SCN/NICU". But since this isn't by patient, I guess what you want is the total patient-days. For example, if patient A stayed 12 days and patient B stayed 5, that's 17 patient-days and that's all you want for the "days" part of this?)

Sorry for the delay in getting back to you - things have been crazy busy at work.

The patient # IS in column B, and all of these patients are the babies #, not the mom's #. Column C is NOT NECESSARILY the day they entered the NICU - that would be column J the first time that account # appeared. It is possible that baby was admitted into the mom's room, on the day of birth, but 2 days later took a turn for the worse. In that case, the admit date would be, for example, 2/22/14, but the first day they came to the NICU was 2/24/14, which would be what shows up in column J. Now, if they are still in the NICU on 2/25/14, column C would still say 2/22/14, and column J would now say 2/25/14. Basically, column J just shows every date of service that they spent in the NICU.

As far as the desired output: We are going to keep up with this spreadsheet on a daily basis - loading the new babies that entered the NICU on the previous day. Our CEO would like to see, probably by month, the # of babies for each year (2013 & 2014), with a variance column. It would also be nice to see what the average length of stay would be. (We would have to count the # of times the babies account # appeared). If a baby is in the NICU for more than a month, and if we are reporting on January, we would only count up January's days in the January comparison, and then the February dates of service in the February comparison. Does that makes sense? The other thing that we have to report is how many admits we had in a particular month. If the patient was admitted to the NICU in January, but we are reporting on February, we would NOT count the admit in February, since we are only allowed to count an admit once for their hospital stay. I had originally thought I could say if(C=J,1,""), but since there are times that baby stays in mom's room the first day, it would never count that baby as an admit to the NICU. I'm not sure how we can capture those types of babies.

Did I clear things up, or do you still have questions? Thank you for your help!