Macro to calculate Length of Stay

Hello!
We calculate a metric called Length of Stay which subtracts the date an animal arrived at the shelter from the date it left. It works fine to do it this way except in cases where animals arrive and must be fostered. When they are fostered the time they spend in a foster home must be subtracted from total LOS, complicating the calculation.

We use a method of data entry which outcomes animals heading into a foster home as OUTCOME_TYPE: FOSTER. When that animal is done fostering, we do another Intake as INTAKE_TYPE: FOSTER. So an animal could have multiple entries as it goes out twice and comes in twice.

I attached a list of 4 cats who were fostered plus one (KARA) who was not, for a total of 5 cats. I would like a macro to calculate the number of days between the animals' arrival and final outcome in column G. In the 4 out of 5 cases attached where the animal went to foster (Outcome Type = FOSTER) and came back from foster (Intake_TYPE = FOSTER) this calculation should subtract the number of days spent in foster from the final LOS calculation. So that first of two outcome dates would be subtracted from the second Intake Date. For KARA, the macro should calculate just the time between her intake as a TRANSFER and her outcome as an ADOPTION- or 13 days.

Ideally, the macro could simplify the multiple entries and replace 3 rows with one for each animal, so for CARTER it could go from this

ANIMAL_ID ANIMAL_NAME INTAKE_DATE OUTCOME_DATE OUTCOME_TYPE INTAKE_TYPE LOS:
A0711031 CARTER 7/5/2015 12:00:00 AM 7/5/2015 12:00:00 AM FOSTER TRANSFER
A0711031 CARTER 9/3/2015 12:00:00 AM 9/23/2015 12:00:00 AM FOSTER FOSTER
A0711031 CARTER 10/14/2015 12:00:00 AM 11/5/2015 12:00:00 AM ADOPTION FOSTER


to something like this:
ANIMAL_ID ANIMAL_NAME INTAKE_DATE OUTCOME_DATE OUTCOME_TYPE INTAKE_TYPE LOS:
A0711031 CARTER 7/5/2015 12:00:00 AM 11/5/2015 12:00:00 AM ADOPTION TRANSFER 123


I think the foster stint could be recognized by sorting by intake date date, and then looking for entries where FOSTER is present in Column E in the first row and then column F in the following row but I am not sure how to do this so that it can also calculate LOS for animals which were not in this foster cycle. Or how to manage the simplifying need from 3 to 1 rows.

Thank you so so much in advance for any help.

Thank you. I was really hoping for a macro that would insert the LOS in the sheet as it is, is it possible you could assist with that?