Articles

Solved week ranges like 3/5/09-9/5/09 in chronological order

May 1, 2012 at 15:52:30
Specs: Windows 7

how to serialise week ranges in excel

See More: week ranges like 3/5/09-9/5/09 in chronological order

Report •


✔ Best Answer
May 2, 2012 at 10:01:21

Mohammedshakil25,

If you had explained all of this in your first post, things would have been much simpler.

Put 30/12/06-5/1/07, your first set of dates in cell A1.

In cell A2 enter the formula:

=TEXT(DATEVALUE(LEFT(A1,FIND("-",A1,1)-1))+7,"DD/MM/YY")&"-"&TEXT(DATEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))+7,"DD/MM/YY")

It is NOT two lines, it is a single very long formula, so Cut & Paste from this post so you get it right.

Drag down your 150 weeks, and just release and you should get your dates.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
May 2, 2012 at 07:05:27

Don't understand your question.

MIKE

http://www.skeptic.com/


Report •

#2
May 2, 2012 at 08:00:02

I have to make a series like the following in Excel

30/12/06-5/1/07
6/1/07-12/1/07
13/1/07-19/1/07
........................

Until 15/1/10

I need a formula that can work like wizard rather than me writing more than 150 weeks. Hope you understand.


Report •

#3
May 2, 2012 at 08:31:51

In cell A1 enter your beginning Date: 30/1/2006

In cell A2 enter the formula: =A1+7
Drag down as many weeks as necessary

In cell B1 enter the formula: =A1+6
Drag down as many weeks as necessary

See how that works.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 2, 2012 at 08:56:13

Thanks...you're almost there but the two dates should be in the same cell A1 seperated by a hyphen like 30/12/06-5/1/07 and cell A2 should have 6/1/07-12/1/07 and so on. And dragging down should begin with the third cell. Isn't it? And after dragging for say 150 cells should I enter or just release the dragging and the date ranges will come automatically.

Please try to wrap it up!


Report •

#5
May 2, 2012 at 10:01:21
✔ Best Answer

Mohammedshakil25,

If you had explained all of this in your first post, things would have been much simpler.

Put 30/12/06-5/1/07, your first set of dates in cell A1.

In cell A2 enter the formula:

=TEXT(DATEVALUE(LEFT(A1,FIND("-",A1,1)-1))+7,"DD/MM/YY")&"-"&TEXT(DATEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))+7,"DD/MM/YY")

It is NOT two lines, it is a single very long formula, so Cut & Paste from this post so you get it right.

Drag down your 150 weeks, and just release and you should get your dates.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#6
May 2, 2012 at 11:09:22

Excellent! But did you try it yourself? Between every two rows of dates a redundant row with a funny word '</body>' is appearing. How to get rid of it? And the fonts of the generated dates are much bigger than the original. Can you please reply now so I can get down to my job and burn the midnight oil!

Report •

#7
May 2, 2012 at 11:26:36

Yes, I did, and it worked fine, went down over 200 rows with no problems.

The funny word '</body>' appears to be some type of HTML code and I have no idea why you should be seeing it.

Did you enter these date yourself, or did you import them from another application?

MIKE

http://www.skeptic.com/


Report •

#8
May 2, 2012 at 12:26:28

At last! I wrote the first date range myself. I just erased the funny word from the tool bar and reduced the font of the generated dates. The only problem remaining is one empty line between every two lines o generated dates. How to get rid of them in one go? You seem to be a jack of all trades and a master of all as well!

Report •

#9
May 2, 2012 at 12:37:00

I just erased the funny word from the tool bar and reduced the font of the generated dates.

You apparently did not get everything that was in the cell.

The only problem remaining is one empty line between every two lines o generated dates

There is still stray data in the cell.

DELETE the cell, everything in the cell must be deleted.

Better yet, Insert a brand New Column and then enter your beginning data and see if you can get it to work.

You should have just the data: 30/12/06-5/1/07 in cell A1.

MIKE

http://www.skeptic.com/


Report •

#10
May 2, 2012 at 13:00:31

Thanks very much indeed!!! You are a legend! Everything is fine now. I'll have a good night's sleep.

One last question before you pack up. I have to give a break of two rows after every four weeks to insert some headings. Can I do it by auto generation rather than manually?


Report •

#11
May 3, 2012 at 09:46:20

Hi

Are u still there? I wonder if you have time to help me untangle the last knot. How can I auto generate rather than manually enter two blank rows after each four rows because I have to work on thousands of rows?

Your instant help will be highly appreciated.

Thanks a trillion and kindest regards.


Report •

#12
May 3, 2012 at 11:38:11

You will need some VBA code to insert lines.
Unfortunately, my VBA skills are limited.

Repost your request as a new question and hopefully
someone with more VBA skills will answer.

Sorry.

MIKE

http://www.skeptic.com/


Report •


Ask Question