Sequence weekly dates in every 13 rows

[Goodvibe]

Hi everyone,

I'm trying to do a sequence of dates on a weekly basis (from sunday to saturday) and position each weeks in every 13 rows without doing all the 53 manual entries.

Column "A" represents the START DATE of the week and column "B" the END DATE of the week.

In the file that I've attached (sorry, the format of excel that I use is in french) I mentionned only 4 weeks out of the 53 periods of the year to give you an example of how it would look like. If you need more details let me know and thanks in advance for your help/solutions .

[AliGW]

In C2 copied across:

=$A2+COLUMNS($D:D)-1

Copy paste the cell to the other rows and drag copy across.

Set cell formatting to CUSTOM: d

[Artik]

I think I misunderstood the assignment, but the solution may also be useful. The idea is to insert dates in columns A and B every 13th row.
1. W A3 formuła (English)

Please Login or Register  to view this content.

(French)

Please Login or Register  to view this content.

2. copy A3 to B3.
3.
a/ To not destroy the borders in the following rows copy A3:B3, select the range to paste and use Paste only formula.
b/ If you can destroy the borders, select A3:B3 and drag down.
4.
a/ Select columns A and B, set up the autofilter
b/ Select in column A (or B) the filter criterion: empty only.
c/ Select "empty" cells and clear with Delete key
d/ remove autofilter

Done

Artik

[Goodvibe]

Hi AliGW!

It didn't solve the issue regarding what formula can be used to space each week period in every 13 rows but indirectly, you actually helped me solve the other challenge that I've had in regard of the individual days in each week period, thanks Ali, I appreciate it .

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Goodvibe]

Hi Artik!

Sorry, your formula was giving me a VALUE error? To make it less confusing, I've updated the file attachment with only 1 sheet showing the 53 weeks which I'm trying to space each week every 13 rows. But thanks for trying to give me a solution. If my request is still not clear, let me know and I'll try to explain it differently .

[Goodvibe]

The original question is not yet resolved but you still did help me with another issue that I had . Thanks for reminding me regarding the reputation icon, I totally forgot. I wasn't connected for a while).

[JohnTopley]

See attached Sheet1 where Artik's formula has been applied. Is this what you require?

[Goodvibe]

Excellent, that's exactly what I was looking for. Thanks to everyone for figuring out the solution, that helps .

If you don't mind and if it's not too long for you, could you explain the formula to space the weeks every 13 rows (because I want to understand how the formula is constructed).

If not, no worry, still appreciate the help and time you took to solve it .

[JohnTopley]

I changed the formula to:

=IF(MOD(ROWS($1:1),12)=0,A$2+(ROWS($1:1)/12)*7,"")

using ROWS($1:1) makes it independent of the start row

See attached which shows the values of the ROWS() and MOD (ROWS(),12) calculations

ROWS($1:1) dragged down simply provides a count of 1,2,3 ......

[Goodvibe]

At my actual knowledge of Excel, I wouldn't have figured that formula by myself. Thanks again for all your help !