CODEWITHSUNDEEP
excelexcel-formula
Nix
Nix

Reputation: 490

Finding a shorter period of days within a longer period for calculation

I have a set of dates, in total 7 periods spanning 6 months each with corresponding calculation factor.

I will have user input of period for which they'd want the calculation to be done, which can fall within one of the 6 months periods or encompass between two or more such periods wholly or partially.

Illustration:

  1. The preset periods:

enter image description here

  1. User input:

enter image description here

I've obtained decimal value (monthly basis) of the periods input by the user for calculation. For first instance (see user input), the decimal value for the period 01-01-2015 to 29-04-2015 will be 3.97 on monthly basis. The calculation for that period would be like:

n*3.97*113%

For the second instance, the decimal value for the period 30-04-2015 to 30-06-2015 would be 2.03, which would be used to do calculation at 113% and then the result will be added to the calculation done at 119% using the decimal value 5.65 for the period 01-07-2015 to 20-12-2015:

(n*2.03*113%)+(n*5.65*119)

Think I can handle the breaking up of periods since the revision event is bi-annual on particular dates but advice regards to that is welcome. More importantly, I need help tracing the preset calculation factor (say 132%) corresponding to the period input by the user as illustrated above. Is it feasible?

Upvotes: 3

Views: 249

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

I will use the standard approach for finding the overlap between two dates and will split the task into three parts as in my answer to this recent question.

(1) The first part is finding the overlap between the user's range of dates and one or more revision periods in whole months and will need an array formula. I have chosen to use the Datedif function 1 to get the difference in months between the beginning and end of the overlaps. If there is no overlap, the start date fed in to Datedif will be after the end date, and it will return an error which can be trapped by Iferror. If the user's dates start in A2 and B2, this gives in C2:

=SUM(IFERROR((DATEDIF(IF(K$2:K$8>A2,K$2:K$8,A2),IF(L$2:L$8<B2,L$2:L$8,B2),"m")+1)*M$2:M$8,0))

which has to be entered as an array formula using CtrlShiftEnter

The above result includes the first and last months entered by the user even if they are incomplete months. It's then necessary to subtract any missing days in the first and last months.

(2) Missing days in first month as a fraction of the number of days in that month in D2:

=SUMIFS($M$2:$M$8,$K$2:$K$8,"<="&A2,$L$2:$L$8,">="&A2)*(A2-EOMONTH(A2,-1)-1)/(EOMONTH(A2,0)-EOMONTH(A2,-1))

A noted by OP, this could also have been done using sumproduct, vlookup or index/match.

(3) Missing days in last month as a fraction of days in that month in E2:

=SUMIFS($M$2:$M$8,$K$2:$K$8,"<="&B2,$L$2:$L$8,">="&B2)*(EOMONTH(B2,0)-B2)/(EOMONTH(B2,0)-EOMONTH(B2,-1))

The total is just (1)-(2)-(3) or

=C2-D2-E2

I have put the results of OP's two examples for comparison in H2 and H3: my results agree with them in the first 3 significant figures.

n*3.97*113%

(n*2.03*113%)+(n*5.65*119)

In all cases I have set n=1 and ignored the fact that the rate is a percentage.

enter image description here

This shows how the results would be calculated manually:

enter image description here

1 Pros of using Datedif:

(1) Works across year boundaries unlike just using Month function. (2) Works conveniently with Iferror to identify non-matching date ranges.

Cons of using Datedif:

(1) It is an undocumented function and may be withdrawn in future. (2) In this particular case, all date calculations are within the same year so Month would be useable.

Upvotes: 1

Related Questions