3 Replies Latest reply on Oct 24, 2017 1:36 PM by Manuel Velasquez

    Calculate length of stay over multiple dates

    Paul Brainard

      Good Day,

       

      I'm hoping someone with advance knowledge of calculations can help me solve an ask from one of my program directors.

       

      The ask was this - find the length of stay for all people that stayed with grant types = Federal RHY and HOPE.  The pickle for me is that Federal RHY and HOPE are individual cases and I'm not sure how to count the length of stay for only those two grant types. 

       

      In the attached workbook, there are two prime examples:

       

      Ken Doll 14 stayed from 8/1/17 to 8/7/17 with grant type = Federal RHY.  That would count as a stay.

      Ken Doll 14 also stayed from 8/15/17 to 9/19/17 with grant type = State BRS.  That would NOT count as a stay.

      (the pickle) Ken Doll 14 also stayed from 9/20/17 to 9/21/17 with grant type Federal RHY, then stayed from 9/22/17 to 9/25/17 with grant type = HOPE (here 9/20/17 to 9/25/17 would count as a stay).

       

       

      Ken Doll 86 stayed from 4/6/17 to 4/14/17 with grant type = State BRS.  That would NOT count as a stay.

      Ken Doll 86 also stayed from 4/15/17 to 4/26/17 with grant type = Federal RHY.  That would count as a stay.

       

      Thank you for taking a look.  I appreciate your time!!!

       

      Paul