Excel Formula to calculate yearly overlap percentage columns from two date columns

Question

I have 2 columns Start Date and End Date? and I want to calculate a percentage from those dates based on associated years that they span across, in order to allocate the appropriate percentage to each year in their own separate columns.

My expected output is:

  Start      End Date?    Year 1 - 2020    Year 2 - 2021    Year 3 - 2022
1/1/2020    12/30/2022         100%             100%             100%
1/1/2020    6/30/2021          100%             50%               0%  
6/1/2021                        0%              50%              100%
6/1/2021    6/30/2022           0%              50%              50%
1/1/2021    6/30/2021           0%              50%               0% 
                               100%             100%             100%

This assumes that if there is a start date that is blank, the item is continuous and should be 100% for all years, and if there is an end date that is blank the item will be 100% each year after the year it starts. There will always be a blank end date when there is a blank start date.

Currently I am using an extremely long IFS formula, comparing the start Column A and end dates Column B to start and end dates for each year Column AP & Column AQ respectively

=IFS(OR(ISBLANK($B8),$B8=AP$1,AND($B8<AP$1,$C8>=AQ$1),AND($B8<AP$1,ISBLANK($C8))),100%,OR($C8<AP$1,$B8>AQ$1),0%,OR(AND($B8>AP$1,$C8>AQ$1),AND($B8>AP$1,$C8<AQ$1),AND($B8>AP$1,ISBLANK($C8)),AND($B8<AP$1,$C8<AQ$1)),(IF($B8<AP$1,DATEDIF(AP$1,$C8,"d")/DATEDIF(AP$1,AQ$1,"d"),DATEDIF($B8,$C8,"d")/DATEDIF(AP$1,AQ$1,"d"))))

The way I approached building this massive IFS formula, was by thinking there are three outcomes for each year: 100%, 0% or a percentage in between. The first two sub-if's seem to be working fine for capturing the 100%'s
OR(ISBLANK($B18),$B18=AP$1,AND($B18<AP$1,$C18>=AQ$1),AND($B18<AP$1,ISBLANK($C18))),100%

or the 0%'s

OR($C18<AP$1,$B18>AQ$1),0%

but the section of the formula I've built to capture percentages in between doesn't seem to be capturing all situations, for example if there are blanks...

OR(AND($B23>AP$1,$C23>AQ$1),AND($B23>AP$1,$C23<AQ$1),AND($B23>AP$1,ISBLANK($C23)),AND($B23<AP$1,$C23<AQ$1)),(IF($B23<AP$1,DATEDIF(AP$1,$C23,"d")/DATEDIF(AP$1,AQ$1,"d"),DATEDIF($B23,$C23,"d")/DATEDIF(AP$1,AQ$1,"d"))))

I also feel as though there must be some way to do this without the nauseatingly long IFS formula...

Are you willing to explore Excel VBA? It will be much more maintainable than using "nauseatingly long" formula. :-) — Wils Mils

Variatus Variatus · Accepted Answer · 2020-07-13T02:27:19

Actually, the required formula isn't such a bad monster as it would appear on first sight. It can be built in simple steps, quite logically, and that makes it easy to maintain. Here it is. (067)

=IFERROR(IF($A2="",1,IF($A2>=EDATE(F$1,12),0,IF(OR($B2="",$B2>=EDATE(F$1,12)),1,IF(DATEDIF(F$1,$B2,"m")>=12,1,DATEDIF(F$1,$B2,"m")/12)))),0)

Key is the contents of F$1. It must be the first day of a year. You can format it as yyyy but you can't have "Year 1 - 2020" unless you want to do some rather fancy footwork. I entered 1/1/2020 in F1, [G1]=EDATE(F1,12) and copied that formula to the right.

I had a start date in A2 and an end date in B2. The result is returned as a percentage, meaning 1 = 100%.

IF($A2="",1 If there is no start date charge 100%.
IF($A2>=EDATE(F$1,12),0 If the start date is after the end of the year in F1. In fact, EDATE(F$1,12) is exactly the date in G1 but I didn't want to refer to another column.
IF(OR($B2="",$B2>=EDATE(F$1,12)),1 If there is no end date or the end date is greater than G1, charge 100%
IF(DATEDIF(F$1,$B2,"m")>=12,1 Also charge 100% if the time passed from F1 to B2 is greater than a year. This is an oversight. The condition could be included in the previous OR().
With all other conditions removed, the remainder is a fraction of a year which is calculated thus, DATEDIF(F$1,$B2,"m")/12. the DateDif function rounds the months in whatever way. You could use "d" and divide by 365.25 if you want a more precise result.
A #NUM error occurs in the DateDif function if the end date (F1 for this function) is before the start date (B2). I didn't manage to capture this error in the division. Therefore I wrapped the entire function in IFERROR().

Excel Formula to calculate yearly overlap percentage columns from two date columns

1 Answers