Calculate the fiscal weeks between two dates

Amanda MedinaIW
edited 03/22/24 in Formulas and Functions

Hello,

I am trying to get a count of active employees for each fiscal week between two dates. Every week the count fluctuates, and I would like to create a chart to track the fluctuations. I have a project intake sheet that has start date, complete date, Techs Assigned, and customer. The projects last multiple weeks. How would I capture the number of techs assigned to a customer for FW1, FW2 etc in a way that would enable me to create a chart? Thank you in advance!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 03/22/24

    Hi @Amanda MedinaIW , If I understand correctly you would like a table that shows fiscal weeks down the left side and a count of the number of techs assigned. From this table you could chart the trend. Does this sound right? It looks like you already have a sheet that contains the start and complete date and a multiselect contact column that contains one or more techs assigned.

    To your existing sheet, I would add a column (Let's call it Tech Count) with a formula that calculates the number of Techs assigned to the project. This could look like:

    = COUNTM([Techs Assigned])

    Then I would create a new sheet and create the following columns:

    1) Fiscal Week (this is a text/number field where you name each FW... eg, "FW1", "FW2"... )

    2) First Date of the fiscal week (once you put the first one in, you could create a formula for each row below that adds 7 to the prior date)

    3) Last Date of the fiscal week ( This is = [First Date]@row+6)

    4) Count of Techs. For this you would use cross-sheet references with the SUM and COLLECT functions. It would look something like:

    =SUM(COLLECT({Tech Count column from main sheet},{Start Date column from main sheet},@cell>=[First Date]@row, {Start Date column from main sheet},@cell<=[Last Date]@row) + SUM(COLLECT({Tech Count column from main sheet},{Start Date column from main sheet},@cell<=[First Date]@row, {Complete Date column from main sheet},@cell>=[First Date]@row)

    This will sum up the number of techs for two scenarios:

    1) Projects that were started during the week (Doesn't matter when they end)

    2) Projects that were started before the beginning of the week, but haven't been completed before the week

    I hope I've covered the relevant situation for you.

    Be well

  • Thank you Scott!

    All good until I came to the formula. It gives me Invalid Operation:

    =SUM(COLLECT({Project Intake Sheet Range 1}, {Project Intake Sheet Range 2}, @cell >= [First Date]@row, {Project Intake Sheet Range 2}, @cell <= [Last Date]@row) + SUM(COLLECT({Project Intake Sheet Range 1}, {Project Intake Sheet Range 2}, @cell <= [First Date]@row, {Project Intake Sheet Range 3}, @cell >= [First Date]@row)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this formula instead:

    =SUMIFS({Count Column}, {Start Date}, @cell<= [Last Date]@row, {End Date}, @cell>= [First Date]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!