distribution of days between two dates across months

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,053
Office Version
  1. 365
Platform
  1. Windows
hi all,

just need a tidier way of getting my days allocated to the months in which they fall. my data represents the duration of stay for patients in hospital. I have an admission date and a discharge date (in most cases). I am using the following formula to distribute the total number of days to the months in which the patient stayed.

Header row: 4
Admission Date: $c5
Discharge Date: $E5

$AB$4:$AY$4: Month names from July to June (for two years. ie Financial years ending June 30,2018)

$AB$2:$AY$2: end of each month named in Row 4
$AB$3:$AB$3: first day of each month

For the example, I am using an admission date of May 2, 2017 and discharge date May 4, 2017.

The formula i have so far is:

=IF($C5>AC$2,0,
IF($E5<AC$3,0,
IF(AND($E5=AC$3,$C5<=AB$2),0,
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3,
IF($E5=$C5,1,
IF($C5<=AB$2,AC$2-AC$3+1,
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1)))))))

with the following logic:


=IF($C5>AC$2,0, '''''''if the Admission Date is past current month end, 0 days, ELSE
IF($E5<AC$3,0, ''''''''if the Discharge Date is prior to the current month, 0 days, ELSE
IF(AND($E5=AC$3,$C5<=AB$2),0, '''''if the Discharge Date = the first day of month AND Admission Date is equal to or before the end of the previous month, 0 days, ELSE
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3, '''''''if Admission Date is before start of month AND Discharge is before or equal to month end, Discharge Date minus Start of month, ELSE
IF($E5=$C5,1, '''''if Admission and Discharge dates are equal, 1 day, ELSE
IF($C5<=AB$2,AC$2-AC$3+1, '''''if Admission date is before or equal to the end of the previous month, end of current month minus start of current month plus 1 day, ELSE
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1))))))) '''''if Admission Date is greater than or equal to start of current month AND Discharge is less than or equal to month end, then Discharge Date minus Admission Date, ELSE end of current month minus Admission Date plus 1 day

I think the only thing i have not accounted for and can't seem to add in the right place is those patient stays when there is no Discharge Date at the time of the report run. In these situations, if, the Admission Date is prior to the start of the current month, the current month's value should be EOMONTH - Start of Month, and if its within the current month, the value to show would be the EOMonth date minus Admission Date.

any suggestions?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Has the forum corrupted your post, I see what looks like missing < > signs

=IF($C5>AC$2,0,
IF($E5 IF(AND($E5=AC$3,$C5<=AB$2),0,
IF(AND($C5 IF($E5=$C5,1,
IF($C5<=AB$2,AC$2-AC$3+1,
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1)))))))
 
Last edited:
Upvote 0
Special-K99 said:
Has the forum corrupted your post, I see what looks like missing < > signs

=IF($C5>AC$2,0,
IF($E5 IF(AND($E5=AC$3,$C5<=AB$2),0,
IF(AND($C5 IF($E5=$C5,1,
IF($C5<=AB$2,AC$2-AC$3+1,
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1)))))))
Click to expand...

Thanks Special K99. yes. how odd. that's the first time in all my years on the board that this has occurred to me. I didn't even notice when I was putting the descriptions in next to the individual statements. ??????

I'll try again, with the formula wrapped in code tags.

Code: 
=IF($C5>AC$2,0,
IF($E5<AC$3,0,
IF(AND($E5=AC$3,$C5<=AB$2),0,
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3,
IF($E5=$C5,1,
IF(AND($C5<=AB$2,$e5=>AD$3),DAY(EOMONTH(AC$4,0)),
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1)))))))


where

Admission Date: $c5
Discharge Date: $E5

Current Month End: AC$2
Current Month Start: AC$3

Prior Month End: AB$2


Code: 
=IF($C5>AC$2,0,
'''''''if the Admission Date is AFTER current month end, 0 days, ELSE

Code: 
IF($E5<AC$3,0,
'''''''''if the Discharge Date is BEFORE current month start, 0 days ELSE

Code: 
IF(AND($E5=AC$3,$C5<=AB$2),0,
'''''if the Discharge Date = the first day of month AND Admission Date is equal to or before the end of the previous month, 0 days, ELSE

Code: 
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3,
'''''if Admission Date is BEFORE current month Start AND Discharge Date is BEFORE or EQUAL to current month end, Discharge Date - Current Month Start, ELSE

Code: 
IF($E5=$C5,1,
''''''if Admission and Discharge dates are equal, 1 day, ELSE

Code: 
IF(AND($C5<=AB$2,$e5=>AD$3),DAY(EOMONTH(AC$4,0)),
''''''''if Admission date is BEFORE or EQUAL to the end of the previous month AND Discharge Date is AFTER or EQUAL to start of next month, Days in Current Month, ELSE

Code: 
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,
'''''''if Admission Date is AFTER or EQUAL to current month start AND Discharge is BEFORE or EQUAL to current month end, Discharge Date minus Admission Date,

Code: 
AC$2-$C5+1)))))))
''''Final: for all else, Current Month End date MINUS Admission Date plus 1


From my original post:

I think the only thing i have not accounted for and can't seem to add in the right place is those patient stays when there is no Discharge Date at the time of the report run. In these situations, if, the Admission Date is prior to the start of the current month, the current month's value should be EOMONTH - Start of Month, and if its within the current month, the value to show would be the EOMonth date minus Admission Date.

any suggestions?
 
Last edited:
Upvote 0
ajm said:
Thanks Special K99. yes. how odd. that's the first time in all my years on the board that this has occurred to me. I didn't even notice when I was putting the descriptions in next to the individual statements. ??????

I'll try again, with the formula wrapped in code tags.

Code: 
=IF($C5>AC$2,0,
IF($E5<AC$3,0,
IF(AND($E5=AC$3,$C5<=AB$2),0,
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3,
IF($E5=$C5,1,
IF(AND($C5<=AB$2,$e5=>AD$3),DAY(EOMONTH(AC$4,0)),
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,AC$2-$C5+1)))))))


where

Admission Date: $c5
Discharge Date: $E5

Current Month End: AC$2
Current Month Start: AC$3

Prior Month End: AB$2


Code: 
=IF($C5>AC$2,0,
'''''''if the Admission Date is AFTER current month end, 0 days, ELSE

Code: 
IF($E5<AC$3,0,
'''''''''if the Discharge Date is BEFORE current month start, 0 days ELSE

Code: 
IF(AND($E5=AC$3,$C5<=AB$2),0,
'''''if the Discharge Date = the first day of month AND Admission Date is equal to or before the end of the previous month, 0 days, ELSE

Code: 
IF(AND($C5<AC$3,$E5<=AC$2),$E5-AC$3,
'''''if Admission Date is BEFORE current month Start AND Discharge Date is BEFORE or EQUAL to current month end, Discharge Date - Current Month Start, ELSE

Code: 
IF($E5=$C5,1,
''''''if Admission and Discharge dates are equal, 1 day, ELSE

Code: 
IF(AND($C5<=AB$2,$e5=>AD$3),DAY(EOMONTH(AC$4,0)),
''''''''if Admission date is BEFORE or EQUAL to the end of the previous month AND Discharge Date is AFTER or EQUAL to start of next month, Days in Current Month, ELSE

Code: 
IF(AND($C5>=AC$3,$E5<=AC$2),$E5-$C5,
'''''''if Admission Date is AFTER or EQUAL to current month start AND Discharge is BEFORE or EQUAL to current month end, Discharge Date minus Admission Date,

Code: 
AC$2-$C5+1)))))))
''''Final: for all else, Current Month End date MINUS Admission Date plus 1


From my original post:

I think the only thing i have not accounted for and can't seem to add in the right place is those patient stays when there is no Discharge Date at the time of the report run. In these situations, if, the Admission Date is prior to the start of the current month, the current month's value should be EOMONTH - Start of Month, and if its within the current month, the value to show would be the EOMonth date minus Admission Date.

any suggestions?
Click to expand...


Moderators: why are the greater than and less than symbols being stripped out of my post?
 
Upvote 0
For anyone else looking at distribution of days across intervals, look at Chip Pearson's page on the subject: http://www.cpearson.com/excel/distribdates.htm
i just have to tailor the formula I have chosen off his site a bit.

It is
Code: 
=MAX(0,(MIN($E5,DATE(YEAR(AC$4),MONTH(AC$4)+1,0))-MAX($C5,AC$4)+1))

for my dates above, Admission Date 2 May and Discharge 4 May, this is 2 days in our system, not 3 (as given by Chip's formula).

However, if i remove the plus one from MAX($C5,AC$4)+1, I get the right answer BUT then I get 0 days when the Admission and Discharge Dates are the same day.

will try putting an If statement in front, to the effect that If ADmission and Discharge Dates are EQUAL, give me a 1, Else do the formula.
 
Upvote 0
ajm said:
Moderators: why are the greater than and less than symbols being stripped out of my post?
Click to expand...

I think it's to do with HTML tags

Usually placing a space around the < > symbols solves this
(See I got away with those two)
 
Last edited:
Upvote 0
Special-K99 said:
I think it's to do with HTML tags

Usually placing a space around the < > symbols solves this
(See I got away with those two)
Click to expand...

now that you mention it, i think I have seen that written down somewhere. cheers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Allocation Across Months
Replies
3
Views
442
NickFru
N
B
  • Solved
Amount spread between two dates, use start date unless passed use alternate date
Replies
5
Views
566
myall_blues
myall_blues
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
342
bebo021999
B

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top