Hi,
I'd really appreciate some help with some teething issues I have with my formula.
Scenario:
I have a list of customers that have a direct debit set-up for every month and each have different starting and ending times spanning multiple years. I'm trying to count the number of months that payment is received from each customer. FYI, each customer has a different start date as they begin their debit differently from one another and the debit begins 1 month from when they sign-up. I also know how much they have paid in total, because they sign-up for a fixed amount of time in advance (years).
I've tried using this formula:
but it gives me 1 extra month at the beginning of Fiscal Year 2017, i.e., for customer 1#, there should only be 24 months in total and for customer 2#, 47.
Can anyone help? Pretty please???
Best,
BJ
I'd really appreciate some help with some teething issues I have with my formula.
Scenario:
I have a list of customers that have a direct debit set-up for every month and each have different starting and ending times spanning multiple years. I'm trying to count the number of months that payment is received from each customer. FYI, each customer has a different start date as they begin their debit differently from one another and the debit begins 1 month from when they sign-up. I also know how much they have paid in total, because they sign-up for a fixed amount of time in advance (years).
I've tried using this formula:
=SUMPRODUCT(--(EOMONTH(DATE(F$1,8,1)-1,ROW($1:$12))>=$B2),
--(EOMONTH(DATE(F$1,8,1)-1,ROW($1:$12))<=$C2))
but it gives me 1 extra month at the beginning of Fiscal Year 2017, i.e., for customer 1#, there should only be 24 months in total and for customer 2#, 47.
| Customer No. | Start Date | End Date | Total Payment | Total Months | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | ||||
| 1 | 23/06/2018 | 23/06/2020 | £5,616.00 | 25 | 2 | 12 | 11 | 0 | 0 | 0 | ||||
| 2 | 07/09/2017 | 31/08/2021 | £7,500.00 | 48 | 11 | 12 | 12 | 12 | 1 | 0 | ||||
| Fiscal Year Start | Fiscal Year End | |||||||||||||
| FY 2017 | 01/08/2017 | 01/08/2018 | ||||||||||||
| FY 2018 | 01/08/2018 | 01/08/2019 | ||||||||||||
| FY 2019 | 01/08/2019 | 01/08/2020 | ||||||||||||
| FY 2020 | 01/08/2020 | 01/08/2021 | ||||||||||||
| FY 2021 | 01/08/2021 | 01/08/2022 | ||||||||||||
Can anyone help? Pretty please???
Best,
BJ
