Formula to return a date that always lands on Wednesday

bcassidy33

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)

Katie Buchwell

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))

bcassidy33

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?

bcassidy33

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]#)