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
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