Advancing Date Ranges

[cvs123]

I have a date range such as "9/15/2016-9/15/2017" in cell A1. Is there a way to easily advance the date range by a year? For example, I would like "9/15/2017-9/15/2018" to appear in cell B1, "9/15/2018-10/15/2018" to appear in cell C1, and so on. Usually I would just add "+1" to the previous cell, but this does not work with a range of dates in a single cell. I am using Excel 2010.

 

[Comfy]

A disgustingly long formula but here you are:

Excel 2010
	A
1	15/09/2016-15/09/2017
2	15/09/2017-15/09/2018
3	15/09/2018-15/09/2019
4	15/09/2019-15/09/2020
5	15/09/2020-15/09/2021
6	15/09/2021-15/09/2022
7	15/09/2022-15/09/2023
Sheet1

Cell Formulas
Range	Formula
A2	=TEXT(DATE(YEAR(DATEVALUE(LEFT(A1,FIND("-",A1)-1)))+1,MONTH(DATEVALUE(LEFT(A1,FIND("-",A1)-1))),DAY(DATEVALUE(LEFT(A1,FIND("-",A1)-1)))),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(DATEVALUE(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1))))+1,MONTH(DATEVALUE(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)))),DAY(DATEVALUE(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1))))),"dd/mm/yyyy")

May I ask why the dates are concatenated into one cell?

Could they be split over two?

 

[cvs123]

Thanks very much, Comfy. You whipped that up like it was nothing!

The dates could be segmented into separate cells. I'm just working off a model that someone else developed.

 

[Comfy]

Thanks very much, Comfy. You whipped that up like it was nothing!

The dates could be segmented into separate cells. I'm just working off a model that someone else developed.

If you can I would say split this over two columns Date From | Date To

This will help if you want to report on the data or manipulate it.