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)

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.