Counting Completed Dates Within the Last 12 Months

Donna Claes

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!

Donna

Genevieve P.

Hi Donna,

Your formula seems to work as expected for me... what's the difference that you're seeing? Is there a second criteria that needs to be added in, to look to see if the project has been marked as "Completed"?

If so, you could add this additional criteria into a COUNTIFS (plural):

=COUNTIFS(Status:Status, "Complete", [Actual Implementation Date]:[Actual Implementation Date], AND(@cell <= TODAY(), @cell > TODAY(-365)))

If this isn't returning the right number, could you provide a screen capture of your sheet in grid view, along with what you're using to calculate the individual months? (Removing any sensitive data).

Thanks!

Genevieve

Genevieve P.

Hi Donna,

Ah, yes! Gotcha. So you want to look back at one year ago, starting on the first of the Month last year. Do you want it to end on Today's Date, or on the first of this month, as well?

Here's how you would get a date from last year, in the same month as today, starting on the first of that month:

=DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)

The DATE function is looking for you to tell it what YEAR, MONTH, and DAY (in that order) you want to return. In this case, you want the YEAR to be TODAYS's year, minus 1. Then the MONTH of last year should be the same as TODAY's month. The day, however, should always be the 1st of that month... so you just need the 1.

We can incorporate that into the COUNTIFS formula:

=COUNTIFS(Finish:Finish, <=TODAY(), Finish:Finish, >DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1))

This is counting from Today's date though, not the first of this current month. To count from the first of this current month, you could do this:

=COUNTIFS(Finish:Finish, <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Finish:Finish, >DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1))

Now, to automatically return the "Yes" for the [Completed within Last 12 Months] column, you could use similar pieces of that formula, but within an IF statement:

=IF(Finish@row > DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), "Yes", "No")

Is this what you were looking for? Let me know if I can help further, or if I've misunderstood!

Cheers,

Genevieve

Genevieve P.

Hi Donna,

Your formula seems to work as expected for me... what's the difference that you're seeing? Is there a second criteria that needs to be added in, to look to see if the project has been marked as "Completed"?

If so, you could add this additional criteria into a COUNTIFS (plural):

=COUNTIFS(Status:Status, "Complete", [Actual Implementation Date]:[Actual Implementation Date], AND(@cell <= TODAY(), @cell > TODAY(-365)))

If this isn't returning the right number, could you provide a screen capture of your sheet in grid view, along with what you're using to calculate the individual months? (Removing any sensitive data).

Thanks!

Genevieve

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!

Donna

Genevieve P.

Hi Donna,

Ah, yes! Gotcha. So you want to look back at one year ago, starting on the first of the Month last year. Do you want it to end on Today's Date, or on the first of this month, as well?

Here's how you would get a date from last year, in the same month as today, starting on the first of that month:

=DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)

The DATE function is looking for you to tell it what YEAR, MONTH, and DAY (in that order) you want to return. In this case, you want the YEAR to be TODAYS's year, minus 1. Then the MONTH of last year should be the same as TODAY's month. The day, however, should always be the 1st of that month... so you just need the 1.

We can incorporate that into the COUNTIFS formula:

=COUNTIFS(Finish:Finish, <=TODAY(), Finish:Finish, >DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1))

This is counting from Today's date though, not the first of this current month. To count from the first of this current month, you could do this:

=COUNTIFS(Finish:Finish, <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Finish:Finish, >DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1))

Now, to automatically return the "Yes" for the [Completed within Last 12 Months] column, you could use similar pieces of that formula, but within an IF statement:

=IF(Finish@row > DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), "Yes", "No")

Is this what you were looking for? Let me know if I can help further, or if I've misunderstood!

Cheers,

Genevieve