Date Formlulas for Dashboard Counter

Hello, Trying to set up formulas from a sheet to post to a dashboard, somehow came up short. I have a number of projects listed in my sheet with a [Go Live] date. I would like to count the following:

Number of projects that are within 30 days of Go Live (but not past go live)
Number of projects that are within 7 days of Go Live (but not past go live)
Number of projects that have already gone live.

The last one seems to be working with the formula: =COUNTIF([Go Live]:[Go Live], >TODAY())

The other two, are not working as I could not figure the conditions correctly. Thanks in advance for any help

Find more posts tagged with

Formulas

Comments

Mike Wilday

Try these... I used Countifs and gave the conditions of less than or = to today, and greater than or equal to Today minus the number of days you were looking for.

Within 30 days of Go live

=Countifs([Go Live]:[Go Live], <=Today(), [Go Live]:[Go Live], >= Today(-30))

Within 7 days of Go Live

=Countifs([Go Live]:[Go Live], <=Today(), [Go Live]:[Go Live], >= Today(-7))

Randy Van Winkle

The first one is calculating correctly, but the second one is giving a value of 1, but I have two values for 3/1/19 and 1 value for 3/4/19, so it should read a value of 3

Mike Wilday

Sorry!!! Those should be Today(30) and Today(7) respectively. Remove the minus signs and you should get a solid count.

Randy Van Winkle

Both values changed to 0 when I do that. Go Live within 30 should read 12 and within 7 should read 3

Mike Wilday

Oh, I see the problem. Try these. With the adjustment of the Today formula I also needed to swap the < and > symbols.

Within 30 days of Go live

=Countifs([Go Live]:[Go Live], >=Today(), [Go Live]:[Go Live], <= Today(30))

Within 7 days of Go Live

=Countifs([Go Live]:[Go Live], >=Today(), [Go Live]:[Go Live], <= Today(7))

Randy Van Winkle

That worked! Great, Thanks!

Mike Wilday

Glad I could help!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?