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

  • Stefan
    Stefan ✭✭✭✭✭✭

    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.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 09/13/20

    Hi @Amanda Medina

    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?

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    @Amanda Medina

    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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!