Make helper column to sort rows by work week (Monday to Friday) based on a Date column

Hello, I believe I have formula to to sort rows by work week (Monday to Friday) based on a Date column but it keep saying: #UNPARSEABLE

The formula I have been using is = "Week of " + TEXT([Date]@row - WEEKDAY([Date]@row, 2) + 1, "mmm d, yyyy") - my Date Column is name the same.

Any suggestions?

Find more posts tagged with

Accepted answers

Adam Murphy

@SherlockKDS You can use this formula to give you the date of Monday of the week of a given date:

=IF(WEEKDAY(Date@row) = 1, Date@row - 6, Date@row - (WEEKDAY(Date@row) - 2))

Will that work?

All comments

Adam Murphy

HI @SherlockKDS, sorry if I am misunderstanding, but why not just use the WEEKNUMBER formula? Like =WEEKDAY(Date@row)

Hope this helps!

SherlockKDS

@Adam Murphy - thank you! Close but I would like it to say something like: May 5 with anything that has date between 5-11 - it to show by work week when communications are going out so we can group by work week and not just individual date.

Nathan Slatton

Instead of Plus + use And &. I think that should work.

Adam Murphy

@SherlockKDS You can use this formula to give you the date of Monday of the week of a given date:

=IF(WEEKDAY(Date@row) = 1, Date@row - 6, Date@row - (WEEKDAY(Date@row) - 2))

Will that work?

SherlockKDS

@Adam Murphy @Nathan Slatton - it worked, thank you so much for the help!

Nathan Slatton

@SherlockKDS No Problem

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.