CODEWITHSUNDEEP
excelexcel-formula
Chazcon
Chazcon

Reputation: 31

Excel formula for every 2 weeks

On my planner I have a section with recurring dates (see pic below) that I use to copy/paste cells into the main body of my planner as needed.

enter image description here

I use the formula below (Thursday shown) for 'recurring every week', but what about 'recurring every 2 weeks'? I'll need to use the DATE() function I think, copy/paste and then edit the date in the formula each time, I'm OK with that, but struggling to come up with the right formula.

=IF(WEEKDAY(TODAY())=5,TODAY(),TODAY()-WEEKDAY(TODAY())+5 + IF(WEEKDAY(TODAY())>=5,7))

Upvotes: 0

Views: 5300

Answers (2)

Michelle
Michelle

Reputation: 1

I know this is old but I just needed to do this so thought this might help others.

  1. Enter the first date (I entered June 6, 2023 into A2 and it made it 06-Jun-23)
  2. In the cell below it type =$ then select the cell with date and add +14 (for example =$A2+14)
  3. Select the 2 cells and move your cursor to the bottom right corner until you see a bolded cross (not the move arrow one)
  4. Click and drag down for the number of rows you want dates for and excel will do the math of 14 days from the previous cell.

Upvotes: 0

Dominique
Dominique

Reputation: 17493

In order to create a 2-week switch, I'd go for a weeknumber mod 2:

=MOD(WEEKNUM(TODAY(),11),2)

This gives 1 for odd, and 0 for even week numbers.

Good luck

Upvotes: 1

Related Questions