Spreadsheet: 74299d1274844903-formula-too-long-formula
Sheet: Sheet1

 | Period
 |  | Start | End | Months | Workdays
 | [NAME] | 2010-02-01 00:00:00 | 2010-03-21 00:00:00 | 1.666666666666667 | 35
 | QG Setup | 2010-03-22 00:00:00 | 2010-04-25 00:00:00 | 1.1 | 25
 | QG Plan | 2010-04-26 00:00:00 | 2010-05-28 00:00:00 | 1.066666666666667 | 25
 | [NAME] 2010 | 2010-05-29 00:00:00 | 2010-12-31 00:00:00 | 7.066666666666666 | 155
 | QG Exec 2011 | 2011-01-01 00:00:00 | 2011-02-13 00:00:00 | 1.4 | 30
 | QG Close | 2011-02-14 00:00:00 | 2011-04-10 00:00:00 | 1.866666666666667 | 40
 | visit
 | start | end
 | [NAME]
 | [NAME] | [NAME] | [NAME] | [NAME] | [NAME] | [NAME] | [NAME] | [NAME]
 | From | Until | [NAME] | QG Setup | QG Plan | [NAME] 2010 | QG EXEC 2011 | QG Close
 | 0 | 0 | 0 | 0 | 3 | 93 | 31 | 0
 | 2010-06-21 00:00:00 | 2010-07-02 00:00:00 | 0 | 0 | 0 | 12 | 0 | 0
 | 2010-08-16 00:00:00 | 2010-08-27 00:00:00 | 0 | 0 | 0 | 12 | 0 | 0
 | 2010-11-01 00:00:00 | 2011-01-10 00:00:00 | 0 | 0 | 0 | 61 | 10 | 0
 | 2011-01-11 00:00:00 | 2011-01-31 00:00:00 | 0 | 0 | 0 | 0 | 21 | 0
 | 2010-05-22 00:00:00 | 2010-06-05 00:00:00 | 0 | 0 | 3 | 8 | 0 | 0
 | IF($C18="","", IF(AND($B18>VLOOKUP(D$14,TP,2,0),$B18<VLOOKUP(D$14,TP,3,0),$C18>VLOOKUP(D$14,TP,2,0),$C18<VLOOKUP(D$14,TP,3,0)),IF(TODAY()>$C18,0,$C18-MAX($B18,TODAY())+1)) + IF(AND($B18>VLOOKUP(D$14,TP,2,0),$B18<VLOOKUP(D$14,TP,3,0)+1,$C18>=VLOOKUP(D$14,TP,3,0)),IF(TODAY()>VLOOKUP(D$14,TP,3,0),0,VLOOKUP(D$14,TP,3,0)-MAX(TODAY(),$B18)+1)) + IF(AND($B18<VLOOKUP(D$14,TP,2,0)+1,$C18>=VLOOKUP(D$14,TP,3,0)),IF(TODAY()>VLOOKUP(D$14,TP,3,0),0,VLOOKUP(D$14,TP,3,0)-MAX(VLOOKUP(D$14,TP,2,0),TODAY())+1)) + IF(AND($B18<VLOOKUP(D$14,TP,2,0)+1,$C18>=VLOOKUP(D$14,TP,2,0),$C18<VLOOKUP(D$14,TP,3,0)),IF(TODAY()>$C18,0,$C18-MAX(VLOOKUP(D$14,TP,2,0),TODAY())+1),0))
 | [NAME] (from Cell D17)
 | IF($C18="","",
 | As I am dragging down the formula and copy it, it shall first check, if there is a date entered in the "until" column. If it's not, just leave the cell blank
 | IF(AND($B18>VLOOKUP(D$14,TP,2,0),$B18<VLOOKUP(D$14,TP,3,0),$C18>VLOOKUP(D$14,TP,2,0),$C18<VLOOKUP(D$14,TP,3,0)),IF(TODAY()>$C18,0,$C18-MAX($B18,TODAY())+1))
 | Then I check:
 | - is the start date within the respective period? (check the period by the vlookup in the table above, named TP)
 | - is the end date within the respective period?
 | If both is true, it checks the following stuff, as it shall only display future figures (i.e. from =today()). Thus it checks next:
 | - if today lies after the end of the visit, make 0 (as the time is apparently in the past), otherwise:
 | - if today is before the end of the visit, take the end of the period and subtract the higher of today and the beginning of the stay
 | +
 | IF(AND($B18>VLOOKUP(D$14,TP,2,0),$B18<VLOOKUP(D$14,TP,3,0)+1,$C18>=VLOOKUP(D$14,TP,3,0)),IF(TODAY()>VLOOKUP(D$14,TP,3,0),0,VLOOKUP(D$14,TP,3,0)-MAX(TODAY(),$B18)+1))
 | So, if from above, the start and end date do not lie in the respective period, it checks the following:
 | - if the start date is in the respective period, and the end date is after the end of the respective period
 | This means basically, the visit starts in a respective period, but takes longer than the end
 | Then it checks with the end of the period:
 | - if today is after the end of the period, make 0
 | - otherwise (i.e. today is before the end of the period), take the end of the period and subtract the higher of today and the start of the stay
 | +
 | IF(AND($B18<VLOOKUP(D$14,TP,2,0)+1,$C18>=VLOOKUP(D$14,TP,3,0)),IF(TODAY()>VLOOKUP(D$14,TP,3,0),0,VLOOKUP(D$14,TP,3,0)-MAX(VLOOKUP(D$14,TP,2,0),TODAY())+1))
 | So, now we have two cases ruled out: the visit is completely in the respective period, and the visit starts in the respective period but ends after it
 | Thus, now it checks:
 | - if the start date is before the beginning of the period and the end date is after the end of the period
 | This basically means that the visit takes longer than a whole period
 | Then again it makes the comparison with today as above with the difference of course, that:
 | - if today lies after the end of the respective period, make 0 (as the time is apparently in the past), otherwise:
 | - if today is before the end of the period, take the end of the period and subtract the higher of today and the beginning of the stay
 | +
 | IF(AND($B18<VLOOKUP(D$14,TP,2,0)+1,$C18>=VLOOKUP(D$14,TP,2,0),$C18<VLOOKUP(D$14,TP,3,0)),IF(TODAY()>$C18,0,$C18-MAX(VLOOKUP(D$14,TP,2,0),TODAY())+1),0))
 | Now we just have one case left, which is the following:
 | - the start of the visit is before the beginning of the period but the end is within the period
 | So, the visit overlaps with the beginning of the period
 | Then again it makes the comparison with today as above with the difference of course, that:
 | -if today is after the end of the visit, make 0, otherwise
 | - take end of the visit and subtract the higher of the start of the period and today