#### cvs123

##### New Member
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.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Comfy

##### Well-known Member
A disgustingly long formula but here you are:

Excel 2010
A
115/09/2016-15/09/2017
215/09/2017-15/09/2018
315/09/2018-15/09/2019
415/09/2019-15/09/2020
515/09/2020-15/09/2021
615/09/2021-15/09/2022
715/09/2022-15/09/2023
Sheet1
Cell Formulas
RangeFormula
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

##### New Member
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

##### Well-known Member
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.

Replies
7
Views
606
Replies
4
Views
347
Replies
3
Views
762
Replies
20
Views
832
Replies
3
Views
577

1,195,696
Messages
6,011,178
Members
441,592
Latest member
Vasant bangalore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back