Formula to return a date that always lands on Wednesday

11/30/21
Answered - Pending Review

Hi,

I'm trying to create a formula that takes a date and returns a date that is the following Wednesday. So it is always a date that starts on a Wednesday. This is the formula I've tried, but it's not working.

=IF(WEEKDAY([Task Name]29)<=4,(4-WEEKDAY([Task Name]29))+([Task Name]29),(11-WEEKDAY([Task Name]29))+([Task Name]29)

Tags:

Answers

  • Hi,

    Have you tried replacing the "[Task Name]29" with "[Task Name]@row". I tried using it with @row and it seemed to work really well. You may want to also check that the column you are grabbing the date from is a date column.

    =IF(WEEKDAY([Start date]@row) <= 4, (4 - WEEKDAY([Start date]@row)) + ([Start date]@row), (11 - WEEKDAY([Start date]@row)) + ([Start date]@row))


  • Oh I meant to write "End Date". This is the formula I'm trying to use.

    =IF(WEEKDAY([End Date]29)<=4,(4-WEEKDAY([End Date]29))+([End Date]29),(11-WEEKDAY([End Date]29))+([End Date]29)

    I was still having errors and noticed that it's because I'm trying to use a column formula. If I use this formula in a cell it works, but it won't work in a column formula. Any idea why?

  • I figured it out. Column formulas don't allow cell references. Since I needed a specific cell and not @row, I was able to rewrite it this way to make it work.

    =IF(WEEKDAY([DEVO Schedule Opening Date]#)<=4,(4-WEEKDAY([DEVO Schedule Opening Date]#))+([DEVO Schedule Opening Date]#),(11-WEEKDAY([DEVO Schedule Opening Date]#))+([DEVO Schedule Opening Date]#)

Sign In or Register to comment.