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
-
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.
bassam.khalil2009@gmail.com
☑️ 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"
-
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:
- 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"
- Week # Finish. The formula in this column takes the "planned finish" and determines which calendar week # it belongs in.
- 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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!