Fiscal Week Formula
Hello,
I am looking for a formula to calculate the Fiscal Week duration of a project.
The formula should be in the highlighted cells and should return the Fiscal weeks that have a value in the FW column.
I am using this formula, but it returns Invalid Data Type
=WEEKNUMBER([FW 37]1:[FW 52]1, 1, 0)
Answers
-
Hello @Amanda Medina,
WEEKNUMBER can only be used with dates.
Why don't you just use a column for start and ende date and get the WEEKNUMBER for those 2 cells? You could even combine the two week numbers so that they look like your example.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Stefan's answer is probably the easiest one to do what you're trying to achieve here.
Still, if you want to use your sheet the way it is set up right now, I would suggest you to add an helper row at the top of your sheet.
Under FW37, put 37, then drag the cell all the way to FW 52. This will populate the row with weeknumbers 37 to 52.
Then, in your FW Duration use some COLLECT function to grab them.
=MIN(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, 1)) This will return the lowest number.
=MAX(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, 1)) will grab the highest one.
For the display, you'll need to COUNT cells to adapt it, depending if FW is on more than one week.
=IF(COUNT([FW 37]@row:[FW 52]@row)=1, COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, 1), MIN(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, 1)) + "-" + MAX(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, 1)))
Hope it helped!
-
Thank you David! This worked great except that it is only capturing the cell if there is a "1". There are instances that there is number higher than a "1" in the cell. Is there a way to capture those as well?
-
Oh sure, I just assumed you had 1 in the weeks you wanted to mark.
Now, if you got other numbers we can go with this:
=IF(COUNT([FW 37]@row:[FW 52]@row)=1, COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, <>""), MIN(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, <>"")) + "-" + MAX(COLLECT([FW 37]$1:[FW 52]$1, [FW 37]@row:[FW 52]@row, <>"")))
This will grab every FW where the cell is not empty.
Just for curiosity, if those numbers aren't always 1, I suppose you're doing something out of this?
-
Hi David,
Sorry, I should have been more clear. This formulas is collecting cells that have zeros in them. How about a formula that only captures cells with numbers greater than zero?
Yes, I am capturing the number of teams assigned to projects for a given week. 😊
-
The first formula suggestion returns this result:
And the Second formula returns this result:
How can I get the formula to return 39-41?
-
@Amanda Medina You need to also incorporate the "helper row" that was mentioned above. Formulas cannot pull text from column names, so you will need a row that will contain each week's number in the appropriate columns.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!