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

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.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/15/21

    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Lisa Matthews2
    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
    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}))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!