Setting up a Date Range to calculate...

[RL]

I would like to set up a list of date ranges for each week
Monday-Friday, and be able to drag and drop so as to
update through end of year. For example:
June 2-6
June 9-13
June 16-20
....
Sept 15-22

If I type June 2-6 into the cell, it either turns it into
June 2, 2026 or it goes from June 2-6 to June 2-7...2-8
etc.

Please help. Thanks a lot!!!!!!!

 

[Jean-Paul Viel]

Hi,



You will have to do that in few steps.

1.. Enter in the first cell a date like A1 "Jun 2"
2.. Enter in the second cell the next week A2 "Jun 9"
3.. Fill it down
4.. In B1 Enter the formula: =TEXT(A1,"mmm dd")&"-"&TEXT(A1+4,"dd")
5.. Fill it down and you will see what you want.
6.. Select what you see in B and Copy.
7.. Do Edit > paste special> Value to the place where you want it.

 

[JohnI in Brisbane]

robert,

Put your first date in A2-

June 2-6

and the following formula in A3-

=TEXT(DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND("-",A2)-FIND("
",A2)-1)&LEFT(A2,FIND(" ",A2)-1)&YEAR(NOW()))+7,"mmmm
d")&"-"&TEXT(DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND("-",A2)-FIND("
",A2)-1)&LEFT(A2,FIND(" ",A2)-1)&YEAR(NOW()))+11,"d")


copy down for more values.
..
WARNING: The Formula above may give incorrect results at the end of some
Februarys due to leap year problem.

regards,

JohnI