Counting Completed Dates Within the Last 12 Months


Hello - I'm trying to do a formula that will count the number of projects that have been completed within the last twelve months. I have one that will count within the last 365 days, but this result doesn't match the completed per month numbers. Here is the current formula:

=COUNTIF([Actual Implementation Date]:[Actual Implementation Date], AND(@cell <= TODAY(), @cell > TODAY(-365)))

Any help would be greatly appreciated.

Thank you!


Best Answers


  • Donna Claes
    Donna Claes ✭✭✭

    Hi Genevieve - thank you for responding! The formula does work for giving me a full year's worth, but what I'd like to get is the last twelve months - i.e. March - March. The formula I have crosses over the months and gives me partial counts for the previous month since it's going back 365 days. So when I add up the number of completed projects (based on the fact that there is a date in the "Actual Implementation Date" field) per month for 12 months, it doesn't add up to the number of completed projects year to date since the formula is adding them up based on today's date and going back 365 days.

    Here is a screenshot. The "Completed" field is currently manual. The "Completed withing Last 12 Months" is also manual and has to be updated on a regular basis. A count is then done on the "Yes" responses. The list goes back 3 years but is a running list. I would like to not have to go in and manually change the "Completed withing Last 12 Months" every month in order for the dashboard counts to be correct by month, not actual date.

    Sorry if I'm rambling 😋  - Does that make sense?

    Thanks again for your time and help with this!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!