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

SherlockKDS

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?

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?

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