Count items due in future weeks - not # of days from now

Jeana
Jeana ✭✭✭✭✭✭

I would like to count how many items are due a specific number of weeks from today.

Example paraphrased: If today is Wednesday 9/16/20 I would like to know how many items (based on Due Date) will be due in the second work week from today. Sept 28 through Oct 2nd in this case. I know how to find items due in the next 14 days, etc. I need to report on a specific number of weeks in the future from today and then count items due that week without having to change the dates.

Thanks,

Jeana

Best Answer

  • Jeana
    Jeana ✭✭✭✭✭✭
    Answer ✓

    Here's what worked for me after some thought and experimenting. Hope it helps others.

    This will result in a 1 if true. Then I can sum how many rows in this column have a 1. This tells me the total # of assets that will due in the next 7 days.

    =COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell >= TODAY(), @cell <= TODAY(+7)))

Answers

  • I would count the number of items due in the next 14 days and subtract those due in the next seven. This works if all you are looking for is a number of records.

  • Jeana
    Jeana ✭✭✭✭✭✭
    edited 09/20/20

    So here's the formula I am using to determine if an END DATE is within the next 7 days, 14 days, etc. It's also verifying that a check box is NOT checked. This works but it COUNTS any future date. I would like to determine if the date falls WITHIN the next 7 days or 14 days, etc. So I'm thinking I could use AND but I need three conditions. DONE checked, date greater than 7 days out but less than 14 days out. Something like this but it's not working.

    =COUNTIFS(AND(Done@row, <>0, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(+7)))

  • Jeana
    Jeana ✭✭✭✭✭✭
    Answer ✓

    Here's what worked for me after some thought and experimenting. Hope it helps others.

    This will result in a 1 if true. Then I can sum how many rows in this column have a 1. This tells me the total # of assets that will due in the next 7 days.

    =COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell >= TODAY(), @cell <= TODAY(+7)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!