Data associated with a date

[shadedlight]

Hello,

Can anyone think of a solution that allows me to associate my data with a specific date? In the example there are patients who prepay on certain dates. I'm looking to be able to associate the prepay date with the balance date. I don't want the prepaid date to affect earlier dates. I've included a sample sheet. Thanks

 

[NARAYANK991]

Hi ,

It is not clear , at least to me , what you want done ; you already have a formula in column D ; what exactly does the following mean ?

I'm looking to be able to associate the prepay date with the balance date. I don't want the prepaid date to affect earlier dates.

Narayan

 

[xlstime]

Hey,

Do you want to cover all "Treatment amount" before/ after prepaid or only after prepaid.

Please see attachment for all treatment amount with date associated

Hello,

Can anyone think of a solution that allows me to associate my data with a specific date? In the example there are patients who prepay on certain dates. I'm looking to be able to associate the prepay date with the balance date. I don't want the prepaid date to affect earlier dates. I've included a sample sheet. Thanks

 

[shadedlight]

I want the prepaid balance to reflect the balance after they prepay. In the example worksheet Smith didn't prepay until 8/2/2016 even though he had a treatment on 7/12/2016. In this example with ,Smith,I'm looking to associate the amount prepaid on 8/2/2016 with the prepaid amount of $100.00. On 8/2/2016 the prepaid balance will be $75.00 not $50.00. I don't want the prepaid balance to affect 7/12/2016 or any earlier dates. Thanks for bearing with me as this is difficult to explain

 

[bosco_yip]

Try…….

D2, copy down :

=SUMIFS(Prepaid!C:C,Prepaid!B:B,B2,Prepaid!A:A,"<="&A2)-SUMIFS(C$2:C2,A$2:A2,">="&INDEX(Prepaid!A$2:A$100,MATCH(B2,Prepaid!B$2:B$100,0)),B$2:B2,B2)

Regards
Bosco

 

[shadedlight]

Perfect! Thank you.