Advancing Date Ranges

cvs123

New Member
Joined
Apr 30, 2014
Messages
10
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,195
Messages
6,170,661
Members
452,345
Latest member
ye4hb0i

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top