Need to calculate advance per week with a range of two dates.

Maria Chicas

Dear all,

I need to support to determine a formula that allows me To perform a weekly count of how many days (or if If any) will happen this week based on range of between 2 dates.

Example

Today is 3/19/2020

Start Date End Date

3/16/2020 04/02/20

This week will have a count = to 5 days from within that range. (3/16 - 3/20)

Next week they will have another 5 (3/23 - 3/27)and so on until we reach the end date's week when will have only 4 days that week (3/30 - 4/2 since that day is a Thursday)

04/23/20 04/27/20

This range will have zero days this current week since dates are on the future

03/11/20 03/12/20

This range will have Zero since the dates are in the past.

Thank you!

Genevieve P.

Hi Maria,

Would you be able to post a screen capture of how your sheet is set up? (Blocking out any sensitive data).

You could use a NETWORKDAYS function to calculate the days between specific dates, see here: https://help.smartsheet.com/function/networkdays

The NETWORKDAYS function doesn't include weekends, which it sounds like is what you're looking for. However, it would span across your whole selection, so if you input the Start Date and End Date for that first date range, it would give you the total working days without breaking it down into the different weeks:

=NETWORKDAYS([Start Date]@row, [End Date]@row)

We could then build on that, say to minus off the number of Networkdays between the Start Date and Today, but it would be helpful to see how your sheet is built, and where you want the day count to be returned, before creating formulas.

Thanks!

Genevieve