Calculate the fiscal weeks between two dates

Amanda MedinaIW

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!

Paul Newcome

Are you able to provide some screenshots for context?

Scott Orsey

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

Amanda MedinaIW

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

Try this formula instead:

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