Apportion Invoice value

[Thomas Kuriakose]

Respected Sirs,

We need to calculate the number of days or months from 01.01.2018 for the attached list of Invoices, that have been processed based on the type and end date to arrive at the Invoice value in 2018.

The following need to be considered -

1. The end date (B2:B37) triggers invoice for next period based on type (E2:E37)
a) If the end date is 31st December then don't calculate value
b) If the end date is not 31st December then calculate the number of days or months from 01.01.2018 to the end date in 2018.
c) The apportion of values are as follows -

i) No. of months/days in 2018 (L2) * No of Unit (F2) * Invoice per unit (J2) if the type is Quarterly (annual) - 4 billings per year
ii) (No. of months/days in 2018 (L2) * No of Unit (F2) * Invoice per unit (J2))/12 if the type is Yearly (annual) - 1 billing per year
iii) (No. of months/days in 2018 (L2) * No of Unit (F2) * Invoice per unit (J2))/6 if the type is Every half year (annual) - 2 billings per year

2. The type (E2:E37) determines number of billings -
a) Yearly (annual) - 1 billing per year
b) Quarterly (annual) - 4 billings per year
c) Every half year (annual) - 2 billings per year
d) Yearly - 1 billing per year - Ignore calculation
e) Every Half year - 2 billings per year - Ignore calculation

Kindly help on how to get this calculation.

Thank you very much,

with regards,
thomas