Here is the non-neat solution. It can obviously be simplified in parts.
Row 9 Formulas
Prepaid Amount:
IF(IF(IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")="","",IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")*S9)<>"",O9-IF(IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")="","",IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")*S9),O9)
Prepaid Months:
IF(IF(IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")="","",IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")*S9)="",R9,R9- IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")))
)
Prepaid Begin Month:
IF(R9=1, EOMONTH(C9,0), IF(IF(IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")<>"","Y","N")="Y",EOMONTH(T9, IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")),T9))
Bookmarks