Formula to calculate a specific date for a week # for display in Card View

Lisa Matthews2

Hello,

I'm trying to create a filter in Card View which displays tasks by calendar week. In order to do this, I have had help from this amazing community in determining the week # for each task's finish date. I am having trouble now with figuring out how to show the "Friday" (end of work week) date in the column headers in Card View. Below are some images of how I started and what I would like to do but cannot figure out how to get there.

Grid View:

Card View:

Thank you, in advance, for your help!

--Lisa M.

Bassam Khalil

Hi @Lisa Matthews2 

Hope you are fine, if i understand your question you need to define for each week the date that met Friday, if this what you need please try the following:

add a helper column call it [Friday Date] and use the following formula to define it:

=IFERROR(IF(WEEKDAY([Planned Finish]@row) = 6, "Friday", ""), "")

then use the following formula for Week # which will display only the week number if the date isn't for Friday and it will display the date of the day if it's Friday:

=IFERROR(IF([Friday Date]@row = "Friday", "Friday - " + [Planned Finish]@row, 
[Week #Finish]@row), "")

We can merge the 2 formula but i keep it in this way to make it easier for Smartsheet new users to understood.

Lisa Matthews2

Hello Bassam,

Thank you for your patience in trying to help me while I work out how to communicate what I am trying to accomplish. I will reference the image you posted above to describe what I would like to figure out:

1. Planned Finish. This is a date which is assigned to the task. It could be calculated from dependencies and durations. The planned finish date may land on any work day of a calendar "week number"
2. Week # Finish. The formula in this column takes the "planned finish" and determines which calendar week # it belongs in.
3. Friday Date. The formula in this column should display the Friday date of whichever "week # finish" appears it belongs to. For example the 18-Aug-2021 [planned finish] belongs in week 33 [week # Finish] but the actual work week for week 33 ends on a Friday and the Friday date for week 33 is 20-Aug-2021. The Friday date column should display 20-Aug-2021.

Hopefully, this helps to clarify a little more. My initial post may have been missing some details. Thank you very much for helping me. I have been trying to keep working on this, not getting any further along by myself.

Thank you,

Lisa M.

Kimberly Loveless

There might be an easier way than what I am thinking but...

I have done something similar to create a column that pulls the Monday but it could work for a Friday date instead. I created a second sheet with just the week numbers and the dates I wanted to pull in. and then did it for the years 2021 and 2022 and then used a cross sheet formula to help pull in the right date.

What the sheet would look like:

Then a formula would be:

=IF(YEAR([Planned Finish]@row) = "2021", INDEX({Friday Dates}, MATCH([Week # Finish]@row, {Week Number 2021})), IF(YEAR([Planned Finish]@row) = "2022", INDEX({Friday Dates 2022}, MATCH([Week # Finish]@row, {Week Number 2022}))))