Split Start End date into multiple years

The attached file shows how you can do this using formulae. It makes use of two helper columns in D and E (coloured blue) to evaluate the number of years needed for each record, and then the cumulative number. Copy the formulae down as far as required.

Hope this helps.

Pete

Hi Pete,

Thanks a lot for this formulae. However I am also looking if this can be done via VBA, a macro that loops through all the customers, splits the dates in years and inserts necessary rows.
In reality I have a list of +1000 start end dates that need to be split and looping through it is better as I need to add some more restrictions

Put your data in A1 then try my code:

HTML Code: