Chaplain Visits Per Shift

[Richard_James]

Greetings,

I need a formula that extracts from column F, Visits to Patients Per Shift, and represent that result in a pie chart and a bar graph. Column F describes the date and time of each Chaplain visit per patient. Please note that column F is the result in another spreadsheet from a report that I do not have the option to change or separate.

For purposes of this formula, our shifts are defined as follows.
1st Shift: 12:30 AM — 8:30 AM
2nd Shift: 8:30 AM — 4:30 PM
3rd Shift: 4:30 PM — 12:30 AM

Thank you, with blessings, Richard

[#2]

@Richard_James 

I'm finding it difficult to picture what you're working with. The most helpful thing you could do to remedy that (for me, but I suspect for others as well) would be to post a sample--with real names removed and replaced (maybe by names of biblical figures?)--of the actual spreadsheet. Not an image of the spreadsheet, the real thing (with names changed/removed), posting it on OneDrive or GoogleDrive, with a link pasted here to grant access.

And blessings for this work; I've volunteered at times in the past in a hospital chaplaincy role--it's important work.

[#3]

@mathetes 

Formula Help with Counting Chaplain Visits Over Three Shifts Per Day

The Date and Time column E is the record of the Progress Note. My question is, ”What is the formula to calculate in Excel version 16.65 to get the number of Chaplain Visits occurrences Per Shift if shifts are defined as 1st Shift: 12:00 AM to 8:00 AM, 2nd Shift: 8:00 AM to 4:00 PM, and 3rd Shift: 4:00 PM to 12:00 AM.” I also want to represent that result in a pie chart. Please note that column F is the result in another spreadsheet from a report that I do not have the option to change or separate.

In other words, how many times did Chaplains file a Progress Note during the 1st Shift: between 12:00 AM to 8:00 AM, the 2nd Shift: between 8:00 AM to 4:00 PM, and the 3rd Shift: between 4:00 PM to 12:00 AM.?

Thanks for your advisement.

[#4]

@Richard_James 

Those are real names, I believe. That's a file that you should take down immediately (per site policy for protecting privacy). Post files only with false names.

[#5]

How do I delete it?

[#6]

click on the three dots in the top left corner of your post, select Edit...that will then allow you to delete that attached file. Put it back with false names (or no names, if you're not needing this summary by name)

[#7]

Thank you!

[#8]

@Richard_James 

This is by no means a finished summary, but I wanted to check to see if it's even in the right direction. What is in the attached is a summary by day of the number of reports filed by shift. It no doubt would be more meaningful if we reduced it to by month by shift, but, as I said, it's a start.

Also not the most elegant formula, and I'm afraid it uses the most recent version of Excel, which may render it inaccessible to you, now that I think of it. Are you not in a position to update your software? There are some VERY useful functions in Excel 2021 or a subscription to Microsoft 365

[#9]

If it works on a MacBook Pro using macOS Monterey, I will get the subscription to Microsoft 365.

[#10]

It does. I'm on an iMac at the moment, on the Ventura OS -- another update I'd recommend.

[#11]

My Mac will not update to Ventura OS. I'm using MacBook Pro / macOS Monterey version 12.6.5.

[#12]

@Richard_James 

Well, then, I am not sure. I'd be surprised if it doesn't still work. I also have an old MacBook Air, and Microsoft 365 does work on it. Even works on my iPad.

In any event, I've updated that initial attempt to report by month. Here's what the summary looks like. File attached.

Formulae are still less than fully elegant. I suspect SUMPRODUCT might be the more elegant solution, but I've never been able to fully figure that one out. This could be my opportunity.

Actually, come to think of it, with the data revised as I've done it, a Pivot Table would be all you'd need--no formulas at all, except in the derivation of the shift number--and a graph can easily be derived from that.