Proper Counting of Days

[dscheeringa]

I am using Excel 2003 to create a spreadsheet to show how many billable days for a consumer's stay. If, the admit date is equal to the discharge date, the total comes out zero. I have worked out that issue. Now I want to show how many billable days broken down by month. The index will not show a sum of 1 if the admit date is the same as the discharge date. Having the same dates is not a common occurrence, but it does happen. See the yellow highlighted row for my example.

Thanks!

[TMS]

I'm not quite sure what it is you are counting. If you have the same start and end date, the implication is that, actually, you have a count of 1.

However, if you have, say, 13, start: September and end: 14, September, would you count that as 1 or 2? If you just subtract the dates, you get 1. But, based on the logic for start and end dates the same, that would be 2.

If that logic is correct for what you want, always add 1 when you subtract the two dates.

See your first entry:

Regards, TMS

[dscheeringa]

Sorry my example XLS is outdated, I had to dump the use of DAYS360 as it rounds the months to 30 day months. I now subtract the discharge date from the intake date to get total bed days. Prime example is row 30. Intake date and discharge date are the same. Subtracting discharge from intake dates will give me a total of "0". so I use a formula of [=IF(G30-F30=0,1,(G30-F30))] to force a count of "1". We bill for all days except the discharge date, so subtracting discharge from admit works. except when one checks in and out on the same day, I am still billing for one day, savvy? But now look at Cell P30. The index formulas count the bed days used within the month, but if the admit and discharge dates are the same, "0" will be the total. and throws off my billable days for the month by 1. This is where I get lost. not to familiar with circular references yet.

[TMS]

I've asked for help but, in the meantime, this seems to work:

J2: =IF(MAX(0,MIN($F2,J$1)-$E2)-SUM($I2:I2)+IF(AND($E2=$F2,MONTH($E2)=MONTH(J$1)),1,0)<0,0,MAX(0,MIN($F2,J$1)-$E2)-SUM($I2:I2)+IF(AND($E2=$F2,MONTH($E2)=MONTH(J$1)),1,0))

copied down and across.

I note that row 27 is wrong but I guess that's because of the DAYS360

Regards, TMS

[daddylonglegs]

One way to do this is to dispense with helper columns and just have a single formula in AA9:AA20, i.e. this "array formula" in AA9 confirmed with CTRL+SHIFT+ENTER and copied down

=SUM(IF(F$2:F$34<X9,0,IF(E$2:E$34>EOMONTH(X9,0),0,IF(F$2:F$34=E$2:E$34,1,IF(F$2:F$34>EOMONTH(X9,0),EOMONTH(X9,0)+1,F$2:F$34)-IF(E$2:E$34<X9,X9,E$2:E$34)))))

Now if you have this formula in G2 copied down

=IF(E2=F2,1,F2-E2)

Your totals in G37 and AA21 should match (assuming all dates are within 2012) - see attached

[daddylonglegs]

Actually I see from your original formula that if you have a stay from 27th June to 3rd July that counts 3 days in July and 3 in June (effectively not counting the start date), whereas my version is counting 4 in June and 2 in July (not counting the end date) so if you want that to still apply then you can amend the AA9 formula (copied down) to this

=SUM(IF(F$2:F$34<X9,0,IF(E$2:E$34>EOMONTH(X9,0),0,IF(F$2:F$34=E$2:E$34,1,IF(F$2:F$34>EOMONTH(X9,0),EOMONTH(X9,0),F$2:F$34)-IF(E$2:E$34<X9,X9-1,E$2:E$34)))))

confirmed with CTRL+SHIFT+ENTER

or if you want to keep the helper columns just use this formula in J2 copied across and down

=IF($F2<=J$1-DAY(J$1),0,IF($E2>J$1,0,IF($E2=$F2,1,MIN(J$1,$F2)-MAX($E2,J$1-DAY(J$1)))))

[dscheeringa]

We don't count the end date unless it is the same as the start date. That is the crux of the quandary.

[dscheeringa]

daddy, I inserted the formula in my current test spreadsheet and confirmed and copied it down, but May only showed 7 and the rest came out to 0. All the cell placements are correct, not sure what happened. Attached is the latest test bed that I am working on. Crisis Stabilization House Intake History testtesttest.xls

[daddylonglegs]

We don't count the end date unless it is the same as the start date. That is the crux of the quandary.

OK, I get that but in the workbook you posted in Post 1 of this thread it's actually the start date that isn't counted - for example see row 27, start date is 29th June and end date is 3rd August, in the helper columns the June column has 1, July has 31 and August has 3, which means you are counting the end date (3rd August), hence 3 days in August, but you're not counting 29th June hence 1 day in June. Is that the way it should be or do you want to count 2 days in June and 2 in August (counting the start date but not the end)?

Also do you want to retain the helper columns or is that just a means to an end, i.e. to get the totals?

[dscheeringa]

jason did the helper columns to use as an index. not necessarily needed. The Intake sheet is the money sheet. the little table X9-AD21 is just a dashboard for me.

The discharge date is supposed to be the one not counted. But in reality, as long as one day is not counted, it would be [I]supposed[I] that that would be the discharge date. Was that redundant or what?

But as in the case of row 27, the day not to be counted would be 3 August. Rarely would a stay transcend into a 3rd month. But, never say never.

Thanks! Dan

[daddylonglegs]

OK in your latest example you haven't correctly "array entered" the formula. Enter formula in AA9 then press F2 key to select formula and then hold down CTRL and SHIFT keys and press ENTER - if done correctly then curly braces should appear around the formula bar. Now you can copy the formula down to AA20

Also you now have more data so you need to extend the row range - I suggest this version, it won't matter if some rows are blank

=SUM(IF(F$2:F$100<X9,0,IF(E$2:E$100>EOMONTH(X9,0),0,IF(F$2:F$100=E$2:E$100,1,IF(F$2:F$100>EOMONTH(X9,0),EOMONTH(X9,0)+1,F$2:F$100)-IF(E$2:E$100<X9,X9,E$2:E$100)))))