Formula to return a date that always lands on Wednesday
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)
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]#)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!