Rolling 2 week task count

jbedoka

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,

Dale Murphy

Try

>Today(14)

instead of 

>Today+14

dm

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

Hi

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))

or

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