Rolling 2 week task count

Hello,

I am trying to do a cross sheet formula that counts the number of tasks that are not complete and have a due date in the next 14 days for a specific department. I hope I am close but it is coming back as unparseable.

Could someone please review and tell me where my mistake is? Please find my formula below.

=COUNTIFS({sheet reference}, “Department”, {sheet reference date column}, >Today+14, {sheet reference status column}, “”, {sheet reference status column}, “doing”)

Thank you,

Find more posts tagged with

Formulas

Project Management

Comments

Dale Murphy

Try

>Today(14)

instead of

>Today+14

Paul Newcome

DM is correct. When referencing today, you are actually using a function, so it will need to be opened and then closed correctly for it to work.

TODAY()

You can add days to it in two different ways. You can either put the appropriate number of days within the parenthesis.

TODAY(14)

Or you can simply add them after.

TODAY() + 14

Either way, TODAY is still a function and needs those opening and closing parenthesis.

You may also want to double check your quotes.

When writing formulas in certain programs such as Microsoft Word, "smart quotes" are used (which are the slanty ones) and will not work in formulas within Smartsheet.

You need to use the quotes generated by Smartsheet and other programs more geared towards data such as Notepad (I guess you could call them "not smart quotes"? haha).

Look at the two sets of quotes below and you should see what I mean. The top set is from your formula posted above. These are "smart quotes". The second set is taken directly from Smartsheet.

“ ”

" "

Notice the difference? Smartsheet certainly does, and it does NOT like the "smart quotes".

ilene_healy68056

Doesn't TODAY(14) equate to today + 14 days? and not within the next 14 days? How would that look differently?

Thanks for clarifying

Ilene

Paul Newcome

@ilene_healy68056

Within the next 14 days could also be read as "greater than or equal to Today and Less than or equal to 14 days after today".

AND(@cell >= TODAY(), @cell <= TODAY(14))

AND([Date Column]@row >= TODAY(), [Date Column]@row <= TODAY(14))

ilene_healy68056

Thanks! I will give it a shot and build upon the other formula you just helped me with

Paul Newcome

@ilene_healy68056 Happy to help! 👍️ I hadn't even realized that I had helped you with another. Haha

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.