Fiscal Week Formula

Amanda Medina

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)

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

David Joyeuse

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!

Amanda Medina

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

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

Amanda Medina

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

Amanda Medina

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

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