count depend on month range

Hi,

I have question,

I want count number of patient depend on admission and discharge date, also count total days on the same month


here is an example it should count number of patient in specific month even the patient extent to next month will count one in next month.

So I need how to count Total Patient and Total days based on month period.


thanks a lot



please see attachment

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

It is a good question. But it is too difficult to solve this problem with formula. It will be much easier if you use code.

Have you cross-posted somewhere, i.e, mrexcel.com or others website?
to avoid wasting time for every one, pls provide the link.
I have got solution already, but I cant post at the moment.

Originally Posted by banda

I will post it in Mrexcel.com.
is it allow to post the link here?

Nooo, you are misunderstanding me
see this:

3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked. (A, Z*)

Originally Posted by banda

so it is OK to post the link here?

Sure. That is all we need before keep going

So it seems you have solution of counting #patients
For Total days, apply this formula in P7:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Base on your calculation in L7, full days of Jan is 30 days.
My current formula returns 120 days for Jan (4*30)
If Jan days to be 31 days, try again:

Please Login or Register  to view this content.

return 124 days (31*4)

@banda

Do not quote whole responses of others. It is only clutter. Click on Reply -- Do not click on Reply with Quote especially if you are only responding to one other person. They know what they have posted. You do not need to tell them.

You have been advised about crossposting here and at MrE, yet you have ignored this rule and posted at Chandoo. I urge you to re-read all the forum rules you agreed to when you joined. Future failures in this area may result in your banishment from this forum for a period of time. Don't let this happen.

Please address that crosspost with a link.

No further help to be offered this OP until compliance with this forum's rules.

Originally Posted by banda

I forgot to tell you even total # of patient is not solved I put example to see the number I expect in the column O based on the month

In O7:

Please Login or Register  to view this content.

Does the formula in #15 work?

How about Admission in the morning and discharge in the afternoon?

It would much more easier if you redesign your spreadsheet like this:

360截图1683110774117103.png

M7 formula:

Please Login or Register  to view this content.

For those patients discharged in the same day of admission, count as one day.
In this case, for row 12 in my screenshot, should it be counted as one day or two days?

Originally Posted by banda

it dose not work sir

Have you confirmed with Ctrl-shift-enter?

Capture.PNG

stay safe and take care of yourself~

Originally Posted by banda

yes I confirm ctrl **** enter and give diffrent number

Pls re-caculate again due to last 2 dates in Admission changed.

refer to your request and my reply in above Level #30.

Please Login or Register  to view this content.

The result of my calculation is exactly the same as the result requested in your example See below last 3 records in red in my screenshot example.

adf.png

My results base on below manual calculation for each row:
Jan: 31+31+31+28 = 121
Feb: 0+3+29+25+3=60
Mar:0+0+3+10=13

How your expect to be 124,64,16

Make sure you share this solution on all the other forums where you asked the same question.