• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with formula to extend dates in a column - I don't have a file, sorry.

GretchenMae

GretchenMae

New Member
Is there a way to automatically run a list of dates in a single column, that will show the month as an abbreviation (ie..JUL) and the dates for each weekday. Such as JUL 4-8 in cell B5 and then JUL 11-15 in cell B6, and so on? Thank you. I can type it manually but thought there might be a formula that can do this. Any help is appreciated, thank you.
 
I think it may be just easier to type or do concatenation with helper column.

But here's my take on it.

In B1 (or any cell out of the way), you will need first day of the month.
7/1/2015

In another cell (E1) use below formula to calculate first Monday of the month.
=B1+MOD(8-WEEKDAY(B1,2),7)

Starting from A4 used following formula.
=TEXT($E$1+7*(ROW(A1)-1),"mmm")&" "&DAY($E$1+7*(ROW(A1)-1))&"-"&DAY($E$1+7*(ROW(A1)-1))+4

Copy down.

Edit: How do you want Week where it straddles 2 month displayed? Above will not accommodate and will show something like Aug 29-33.

Edit2: Below will accommodate months straddling week. Meh, I've got feeling there's more efficient way to do this, but I'm drawing blank.

=TEXT($E$1+7*(ROW(A1)-1),"mmm")&" "&DAY($E$1+7*(ROW(A1)-1))&"-"&IF(DAY($E$1+7*(ROW(A1)-1))+4>DAY(EOMONTH($E$1,1)),TEXT(EOMONTH($E$1+7*(ROW(A1)-1),1),"mmm")&" "&DAY($E$1+7*(ROW(A1)-1))+4-DAY(EOMONTH($E$1,1)),DAY($E$1+7*(ROW(A1)-1))+4)
 

Attachments

Last edited:
Just realized, above will cause issue with some week (ex Nov 28 to Dec 2 week). Back to the drawing board.
 
*Sigh* This is it. Checked for 2 year duration and didn't find an error.

=TEXT($E$1+7*(ROW(A1)-1),"mmm")&" "&DAY($E$1+7*(ROW(A1)-1))&"-"&IF(DAY($E$1+7*(ROW(A1)-1))+4>DAY(EOMONTH($E$1+7*(ROW(A1)-1),0)),TEXT(EOMONTH($E$1+7*(ROW(A1)-1),1),"mmm")&" "&DAY($E$1+7*(ROW(A1)-1))+4-DAY(EOMONTH($E$1+7*(ROW(A1)-1),0)),DAY($E$1+7*(ROW(A1)-1))+4)
 
Chihiro said:
*Sigh* This is it. Checked for 2 year duration and didn't find an error.
Click to expand...
Hi Chihiro ,

Sorry , but I think there is still some problem. Of course , OP can confirm what the input for this will be.

What input does your formula expect in E1 ?

Narayan
 
@NARAYANK991
This in E1
=B1+MOD(8-WEEKDAY(B1,2),7)

Where B1 is first day of month.

Couldn't find errror, but I like your method better. Mine will ignore the week that does not start on the month specified.
 
Chihiro said:
@NARAYANK991
This in E1
=B1+MOD(8-WEEKDAY(B1,2),7)

Where B1 is first day of month.

Couldn't find errror, but I like your method better. Mine will ignore the week that does not start on the month specified.
Click to expand...
Hi Chihiro ,

Then it's OK. I thought E1 contained the input , but if B1 is the input to your formula , then it's OK.

Narayan
 
I finally got it to work. Thanks a bunch to both of you!!
@Chihiro
@NARAYANK991

Help again.
@NARAYANK991
I just noticed that the dates I am using were off slightly. I think I am doing something wrong.

I have attached the file I am using but the date begins with Jul 02 when next week the week days begin on Jul 04. Thank you for any help you can offer.
 

Attachments

Last edited:
Hi ,

See the file now.

You need to do 2 things before you can use the formula , which is as follows :

=TEXT(IF(WORKDAY($E$1,1) - $E$1 = 2, $E$1 - WEEKDAY($E$1,1) + 9, $E$1 - WEEKDAY($E$1,1) + 2) + 7 * (ROW(A1) - 1), "mmm dd") & " - " & TEXT(IF(WORKDAY($E$1,1) - $E$1 = 2, $E$1 - WEEKDAY($E$1,1) + 9, $E$1 - WEEKDAY($E$1,1) + 2) + + 7 * (ROW(A1) - 1) + 4, "mmm dd")

1. The formula refers to E1 ; this cell is supposed to contain the start date , from where all the remaining dates will be derived.

Hence , if you want to have all the dates in the period July 2016 till October 2016 , you need to enter the start date of either July 1 , 2016 or which ever is the start date in JUly 2016 , in cell E1.

2. If you copy the above formula to any location , there is every possibility that Excel will change the highlighted reference , from A1 to something different.

You need to bring back the reference to A1 ; the start always has to be A1 , so that as you copy it down , it changes by a week each time , starting from the start date in E1.

If this reference changes from A1 to something else , then the starting week will be some random week.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top