View original (French)

Increment date every x cells

Solved
DraGula Posted messages 4 Registration date   Status Member -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

Quick question in Excel:
I would like to increment a date by one week every 50 cells.
My table:
Basic information from "A" to "F".
In G I have a reference number (from 1 to 2500)
In H, I would like to have a date, identical for 50 cells, then increment by one week for another 50 cells and so on until the end of the table.

I've fiddled around with incrementing, but nothing fits my needs...
Any simple ideas?

Thanks!
DraGula

4 answers

Answer 1 / 4
tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
 
Hello,
To test:
In H1 a date, in H2 a formula to copy down:
=$H$1+7*INT((ROW()-1)/50)
1
Answer 2 / 4
DraGula Posted messages 4 Registration date   Status Member
 
Excellent!
Thank you very much, it works perfectly :-)
0
Answer 3 / 4
DraGula Posted messages 4 Registration date   Status Member
 
I'm abusing your help, but do you know how I can only include weekdays?
I've slightly evolved my initial need:

Column H: Same date on 50 cells and increment of 1 > OK, but of course I have my Saturdays and Sundays included... How can I do +1 on 50 cells x 5 only?

Column I: Week number on 250 cells and increment of 1 > That's good!

Thank you!
0
tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
 
If the departure date is neither a Saturday nor a Sunday, we will never land on the weekend with an increment of 7 days.
0
DraGula Posted messages 4 Registration date   Status Member
 
Yes, but just, if I increment by 1, yes I will fall on Saturday/Sunday.
0
Answer 4 / 4
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Good evening,

I would like to have a date, identical across 50 cells and then increment by one week across another 50 cells
If you start on a Monday, all your dates will be a Monday..

If you want to increment by one day without weekends, using your date in A2:
=A2+(MOD(ROW()-2,50)=0)*(1+(WEEKDAY(A2,2)=5)*2)
to be tested...

eric
--
Never will you respond to an unsolicited PM...
Well, that's done.
-1
DraGula Posted messages 4 Registration date   Status Member
 
Indeed, in my initial request I wanted 50 by 50, I later reformulated for 1 by 1.
I should have reopened a thread for my new request.
The suggested formula gives me inconsistent results, I will analyze that and reopen another thread if necessary.
Thank you!!!
++
0
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Hello,

indeed.
From memory there were 2 formulas, one in B2 and one in B3 to copy down.
I must have only put the 2nd one.
You wake up 2 days later, a bit late and not sure you want to deal with this again. Next time try to keep a closer eye on it.

Eric

edit:
in B2: =A2+(WEEKDAY(A2,2)>5)*(8-WEEKDAY(A2,2))
in B3: =B2+(MOD(ROW()-2,50)=0)*(1+(WEEKDAY(B2,2)=5)*2) to copy down
0