Formula help needed, this is WAY over my abilities

[berger_d]

Hi, I know I've often found great solutions here and hopefully this type of post is allowed, if not my apologizes and I'll delete it.

Other than doing my best to outline the requirements below, I don't really know how to attempt to write a formula that will accomplish what I'm needing. Here we go...

Here is a sample file with data for the below outlined criteria: Excel Sample.xlsb (it is a view only link)

I need a formula created in Excel (Office 365 on Win10 in case it matters) to calculate the # of months an asset should be invoiced for renewals in a given quarter.

Assumption: This date/quarter span will need to be entered as a "helper" value(s) in the spreadsheet.

Here are the renewals billing criteria:
Any assets (each individual line of data is for an individual asset) with a Term Date after the billing period will be billed via another process as rental.
Billing period Qtr's are - Jan-Mar, Apr-Jun, Jul-Sept, & Oct-Dec
Three columns in the sample file determine the calculation. These are Column F - Term Date, G - Cap Date, & I - Disposal Date.

The formula needed would be entered into Column O - # of Months. The sample file has the values entered in manually (barring any manual error on my part, I believe all these values are correct).

The blank columns are needed as they have other file data, but I've eliminated the data from the sample file as it doesn't impact this calculation. If helper columns are needed, they may be placed starting in column AB onward. Also, row 1 is blank except for the column totals in P1:R1 and helper data could be entered here as well if needed. I'd prefer NOT to use any scripting or macros unless it's absolutely necessary due to IT restrictions at work.

The only correct values for this calculation are 0-3, anything else would be an error for # of Months.

The following criteria needs to be considered for each asset (each line of data).

The Term Date (column F) needs to be prior to each month in the billing quarter, if it is not then it would not be billed for that month.
Example: If for Q1 Jan-Mar, the Term Date is 1/1/2020 (assume no other criteria impact this example) then the # of Months would = 3
Example: If for Q1 Jan-Mar, the Term Date is 2/1/2020 (assume no other criteria impact this example) then the # of Months would = 2
Example: If for Q1 Jan-Mar, the Term Date is 3/1/2020 (assume no other criteria impact this example) then the # of Months would = 1
Example: If for Q1 Jan-Mar, the Term Date is 4/1/2020 (assume no other criteria impact this example) then the # of Months would = 0

The Cap Date (column G) needs to be after each month in the billing quarter, if it is not then it would not be billed for that month.
Example: If for Q1 Jan-Mar, the Cap Date is 1/1/2020 (assume no other criteria impact this example) then the # of Months would = 0
Example: If for Q1 Jan-Mar, the Cap Date is 2/1/2020 (assume no other criteria impact this example) then the # of Months would = 1
Example: If for Q1 Jan-Mar, the Cap Date is 3/1/2020 (assume no other criteria impact this example) then the # of Months would = 2
Example: If for Q1 Jan-Mar, the Cap Date is 4/1/2020 (assume no other criteria impact this example) then the # of Months would = 3

If the line meets the Term Date and Cap Date criteria (i.e. is billable for one or more months in the billing quarter) then the Disposal Date (column I) needs to be considered.

Disposal Date "grace period". The grace period is the first 15 days of each billing month. If a Disposal Date is within the first 15 days of the month, it would NOT be billable for that billing month, assuming this is also within the current billing period. If the Disposal Date is the 16th or later in the month, it would be billable for that billing month, assuming this is also within the current billing period.
Example: If for Q1 Jan-Mar, the Disposal Date is "any past date"-1/15/2020 the then # of Months would = 0
Example: If for Q1 Jan-Mar, the Disposal Date is 1/16-2/15 the then # of Months would = 1
Example: If for Q1 Jan-Mar, the Disposal Date is 2/16-3/15 the then # of Months would = 2
Example: If for Q1 Jan-Mar, the Disposal Date is 3/16-"any future date" the then # of Months would = 3

If the Disposal Date is BLANK it would follow the Term Date and Cap Date rules above.

If the Disposal Date is prior to the Term Date it would NOT need to be billed.
Example: If for Q1 Jan-Mar, the Disposal Date is 11/10/2019 and the Term Date is 2/1/2020 then the # of Months would = 0

If the Disposal Date is after the Term Date AND before the Cap Date it would need to be billed until the Disposal Date.
Example: If for Q1 Jan-Mar, the Disposal Date is 3/10/2019, the Term Date is 1/1/2020, and the Cap Date is 10/1/2020 then the # of Months would = 2
Example: If for Q1 Jan-Mar, the Disposal Date is 3/16/2019, the Term Date is 1/1/2020, and the Cap Date is 10/1/2020 then the # of Months would = 3

 

[JJenkx]

I believe I could make the formula but I am having trouble understanding the logic. If you are still looking for a solution reply here

 

[berger_d]

I believe I could make the formula but I am having trouble understanding the logic. If you are still looking for a solution reply here

Hi @JJenkx I appreciate the reply. I'm not sure what else I could add to clarify. Do you have any specific questions about above requirements that I could answer to help clarify the logic?

 

[KRice]

Could you explain two things further, please?

Any assets (each individual line of data is for an individual asset) with a Term Date after the billing period will be billed via another process as rental.
Billing period Qtr's are - Jan-Mar, Apr-Jun, Jul-Sept, & Oct-Dec

In the first statement the term "billing period" is used, but it is not defined. In the second statement "billing period Qtr's" is used. Is the "billing period" a quarterly event, a monthly event, something else...does it occur on the 1st of the month, the 15th, when?
I am confused about what is meant in the 1st statement by the phrase "after the billing period". The 2nd statement suggests that there are 4 quarterly billing periods, so any given Term Date will always be after some billing period, therefore according to the 1st statement, some other process for billing the asset would be involved.

Later the following appears:

The Term Date (column F) needs to be prior to each month in the billing quarter, if it is not then it would not be billed for that month.
Example: If for Q1 Jan-Mar, the Term Date is 1/1/2020 (assume no other criteria impact this example) then the # of Months would = 3

In this example, the Term Date is given as 1/1/2020, and there is a requirement for the Term Date "to be prior to each month in the billing quarter". The January 2020 billing quarter would be Jan, Feb, Mar 2020. And 1/1/2020 is not prior to January....it's in January, so how can the answer given (3) be correct. My reading of the rule would give 2 as the correct answer....January is prior to February and March in that billing quarter, so the latter two months would be considered.

 

[berger_d]

Hi @KRice thank you so much for your reply, I'll do my best to answer these questions.

Could you explain two things further, please?

In the first statement the term "billing period" is used, but it is not defined. In the second statement "billing period Qtr's" is used. Is the "billing period" a quarterly event, a monthly event, something else...does it occur on the 1st of the month, the 15th, when?

Yes, the Billing Period is quarterly for these calculations.

You also asked when the billing period occur... due to the 15 day grace period, I'd have to answer that it doesn't occur until the 16 of the month following each calendar quarter (Q1: Jan-March would not process/occur until April 16).

I am confused about what is meant in the 1st statement by the phrase "after the billing period". The 2nd statement suggests that there are 4 quarterly billing periods, so any given Term Date will always be after some billing period, therefore according to the 1st statement, some other process for billing the asset would be involved.

The "after the billing period" statement is intended to acknowledge that there is another rental billing process that is done for the assets while they are within the original term of the contract.

Later the following appears:

In this example, the Term Date is given as 1/1/2020, and there is a requirement for the Term Date "to be prior to each month in the billing quarter". The January 2020 billing quarter would be Jan, Feb, Mar 2020. And 1/1/2020 is not prior to January....it's in January, so how can the answer given (3) be correct. My reading of the rule would give 2 as the correct answer....January is prior to February and March in that billing quarter, so the latter two months would be considered.

I'm unsure how to better write this requirement, other than possibly that the Term Date must be prior or equal to the billing quarter and that is why the example answer is correct at (3).

 

[berger_d]

@KRice & @JJenkx I've added some additional examples for each quarter and added some details to the requirements. All this can be found in the sample file: Excel Sample.xlsb

Thanks for any feedback you may have.

 

[KRice]

Are you still looking for ideas about this? If so, please have a look at this and post any clarifications, and perhaps add a brief note to column AT explaining how you arrived at the manually-determined numbers. There are a number of rules that are unclear. For example, if the Term analysis yields one number and the Cap analysis another number, which of those prevails? Does one take the min of the two, the max...do both have to be >0 in order for the Disposal analysis to be considered, or only one? I think the green cells (term analysis), blue cells (cap analysis), and peach cells (disposal analysis) are fairly close to the initial set of rules. But how one set relates to another is the issue, and the order in which the rules are applied also matters. Those details are unclear. Please have a look at columns AC:AS.

Excel Sample_berger.xlsb

Shared with Dropbox

www.dropbox.com

I wanted to call your attention to column AS where I've attempted to capture all of the conditions you described, but there are some gaps. For example, in post #1 you mentioned:

If the Disposal Date is after the Term Date AND before the Cap Date it would need to be billed until the Disposal Date.

What happens if that condition is not met?

In post #1 you mentioned:

If the Disposal Date is BLANK it would follow the Term Date and Cap Date rules above.

What precisely does this mean? If the term analysis yields 3 and the cap analysis yields 1, what is the correct answer?

Some additional clarity on these details should lead to a solution.

 

[berger_d]

@KRice Thank you so much for your reply. I worked with someone on fiverr that was able to provide a formula solution for me. She completed it before the holiday weekend and I applied her solution yesterday to about 3 years of historical files as a test. It worked 100% of the time!!! Here is the solution she provided in case it could help anyone else. (I do not pretend to fully understand this formula)

Please follow these steps:

1) Type the first day of the quarter into cell I1 (e.g. 4/1/2020 for Q2)
2) Type the last day of the quarter into cell J1 (e.g. 6/30/2020 for Q2)
3) One time only, paste this formula into cell O3 and copy down as needed

=IF(COUNTBLANK(I3:J3)=2,IF(F3-1<$I$1,IF(G3>$J$1,3,MONTH(G3)-MONTH($I$1)),IF(G3>$J$1,IF(F3>$J$1,0,MONTH($J$1)+1-MONTH(F3)),MONTH(G3)-MONTH(F3))),IF(OR(J3="",J3>I3),IF(I3<F3,0,IF(I3<G3,IF(F3-1<$I$1,IF(I3>$J$1,3,IF(DAY(I3)<16,MONTH(I3),MONTH(I3)+1)-MONTH($I$1)),IF(I3>$J$1,IF(F3>$J$1,0,MONTH($J$1)+1-MONTH(F3)),IF(DAY(I3)<16,MONTH(I3),MONTH(I3)+1)-MONTH(F3))),IF(F3-1<$I$1,IF(G3>$J$1,3,MONTH(G3)-MONTH($I$1)),IF(G3>$J$1,IF(F3>$J$1,0,MONTH($J$1)+1-MONTH(F3)),MONTH(G3)-MONTH(F3))))),IF(J3<F3,0,IF(J3<G3,IF(F3-1<$I$1,IF(J3>$J$1,3,IF(DAY(J3)<16,MONTH(J3),MONTH(J3)+1)-MONTH($I$1)),IF(J3>$J$1,IF(F3>$J$1,0,MONTH($J$1)+1-MONTH(F3)),IF(DAY(J3)<16,MONTH(J3),MONTH(J3)+1)-MONTH(F3))),IF(F3-1<$I$1,IF(G3>$J$1,3,MONTH(G3)-MONTH($I$1)),IF(G3>$J$1,IF(F3>$J$1,0,MONTH($J$1)+1-MONTH(F3)),MONTH(G3)-MONTH(F3)))))))

 

[KRice]

Thanks for the response. The formula refers to a date in J3...I wasn't aware that column J (after row 1) held any relevant data. What is in J3?

 

[berger_d]

Thanks for the response. The formula refers to a date in J3...I wasn't aware that column J (after row 1) held any relevant data. What is in J3?

Column J has Received Date data, it is treated similarly as the Disposal Date data.