yogi_Compute Nights Stayed From Table Of Enrolled Date And ExitDate

Google Spreadsheet   Post  #2051

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-11-2016

post by: AMontiel:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/VxlxlKhcGWI;context-place=forum/docs
Formula to calculate nights present
I am trying to create a formula that calculates the number of nights a client was present in October 2015. Column S has the client's enrollment date and Column T has the exit date. Exit dates are blank if the client hasn't exited the program.

Here is an attempt at the formula, but it is missing clients who enrolled before 10/1/2015 and exited after 10/31/2015.

=IF(OR(AND(S2<"10/1/2015",T2=""),T2>"10/31/2015"),31,T2-"10/1/2015")

Here is my second attempt.

=IF(OR(AND(S2<"10/1/2015",T2=" "),AND(S2<"10/1/2015", T2>"10/31/2015")),31,0)+IF(AND(S2>="10/1/2015",T2=" "),"10/31/2015"-S2,0)+IF(AND(S2>="10/1/2015",T2>"10/31/2015"),"10/31/2015"-S2,0)

Does anyone have an idea how to do this calculation?

-------------------------------------------------------------------------------------------------------------------