Calculate number of tasks completed between two dates

Brandon.Ford

Currently I'm using 2 helper columns to calculate the start and end dates of a month and using those to search through another sheets "Completed Date" to find the number of tasks that were completed in that month. However, right now my formula is only returning zero.

Below is my helper columns. Start-10 is the first day of the month 10 months in the past. End-10 is the last day of the month 10 months in the past.

Start-10 Formula:

=DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) <= 10, 1, 0), IF(MONTH(TODAY()) - 10 <= 0, MONTH(TODAY()) - 10 + 12, MONTH(TODAY()) - 10), 1)

End-10 Formula:
=IF(MONTH(TODAY()) - 9 <= 0, DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - 9 + 12, 1), DATE(YEAR(TODAY()), MONTH(TODAY()) - 9, 1)) - 1

These are returning the dates that I need but I'm not sure they are actually considered dates which could be part of the problem.

My final formula is using these dates to search another sheet "Completion Date". It should count the number of tasks completed between these two dates. Ideally I need it to also find the status column of that row and count "On Time" vs "Late" but I haven't even been able to get the more simplified version of this formula to work. Currently I'm not getting any syntax errors but it's only returning zero.

Formula:
range 2 being "Completed Date" and range 4 being "Status"

=IF(COUNTIFS({Task Completion History Range 2}, ">=" + TODAY(-10), {Task Completion History Range 2}, "<=" + TODAY(), {Task Completion History Range 4}, <>"", {Task Completion History Range 4}, <>"No Data") = 0, 0, COUNTIFS({Task Completion History Range 2}, ">=" + TODAY(-10), {Task Completion History Range 2}, "<=" + TODAY(), {Task Completion History Range 4}, "On Time") / COUNTIFS({Task Completion History Range 2}, ">=" + TODAY(-10), {Task Completion History Range 2}, "<=" + TODAY(), {Task Completion History Range 4}, <>"", {Task Completion History Range 4}, <>"No Data"))

Jgorsich

I'm glad the first message was helpful!

Before addressing the problem you are aware of - your percentage on time formula needs an iferror() wrapper to ensure that if there is ever a month with 0 projects started/finished that it won't break any dependent formulas by throwing a "#Divide by zero" error.

To get your date of 12 months in the past - it might be a LOT easier to just do "=today()-365-day(today())"

Doing it this way avoids having to handle wrapping around the year and having to use the date() function. You can also sub in any other date for "today()" (likely from a column of "months to summarize") pretty easily.

That being said, if you want to stick with a variant of what you had - you are ALWAYS going to subtract 1 year (twelve months before Dec 31st is still in a different year - unless you actually mean "11 months up to 12 months"), so that if() statement can be removed. Additionally, 12 months prior is ALWAYS going to be a different month (with the possible exception of 2/29 based on how you look at it) UNLESS you are trying to round to the nearest first of the month (where 12 months before 5/15/25 is 4/1/24 while 12 months before 5/16 is 5/1/24), but that doesn't seem to be what you are trying to do.

1 month before 12/30 = 11/30

2 months before 12/30 = 10/30

3 months before 12/30 = 9/30

11 months before 12/30 = 1/30

12 months before 12/30 = 12/30 of the year before.

This collapses your date formula down as follows:

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

Now, if what you actually want is "the first of the month that falls between 11 and 12 months in the past", where for 12/15/24 you pick 1/1/24, then you'd need it slightly more complicated.

=DATE(if(month(today())=12,year(today(),YEAR(TODAY()) -1)), IF(MONTH(TODAY()) =12,1, MONTH(TODAY()) + 1), 1)

Whichever you choose, to convert that date into just the name of the month, do the following.

First - note that TEXT() isn't a valid Smartsheet function (Functions List | Smartsheet Learning Center). If that was recommended to you via the AI engine, be aware that I've caught it recommending Excel functions that don't exist inside of Smartsheet before.

As far as I can tell, there is no native way to easily convert to just the name of the month for a date. You'd have two choices that pop out for me:

1. Just a long if() statement: "=if(month([12monthsBack]@row)=1,"Jan",if(month([12monthsBack]@row)=2,"Feb"…))))))))))))"
2. A separate pair of columns that has the numbers 1-12 ("MonthNum") and the names of the months ("MonthName") that you index through:

=index([MonthName]:[MonthName],match(month([12monthsBack]@row),[MonthNum]:[MonthNum],0))

Jgorsich

2 things - first, you are making your conditional a string and you don't want it to be. Where you have ">=" + TODAY(-10) replace it with >=Today(-10) without the quote marks or "+" symbol, this will keep it numeric. Do the same for "<=" + TODAY() and delete the quotes and +. This alone will force the 0 since the values in the date columns aren't evaluated as strings and so can never be equal to a string formatted conditional.

Second, in your formula you are saying the following:

if there are 0 rows where {Task Completion History Range 2} is today or up to 10 days ago AND where {Task Completion History Range 4} has data, show a 0, otherwise get me the percentage of entries that are on time and completed in the last 10 days.

Are you sure this is what you meant to ask it to do? Based on your description, it seems like you need to replace your today(-10) functions with [Start-10]@row and your today() functions with [End-10]@row to do what you are looking for. This would make it say:

if there are 0 rows where {Task Completion History Range 2} is between the start and end date AND where {Task Completion History Range 4} has data, show a 0, otherwise get me the percentage of entries that are between the start and end date.

Brandon.Ford

Jgorsich,

Thank you, that was very helpful. I actually copied the wrong formula in here but your feedback got me close enough to figure out what I needed to do. I ended up breaking the formula down into several different columns so I could use each piece of data in reports but here was the final (working) result.

Calculating number of tasks completed in the month 12 months ago that were "On Time"

=COUNTIFS({Task Completion History Range 2}, >=[Start-12]@row, {Task Completion History Range 2}, <=[End-12]@row, {Task Completion History Range 4}, "On Time")

Calculating number of tasks completed in the month 12 months ago that were "Late"

=COUNTIFS({Task Completion History Range 2}, >=[Start-12]@row, {Task Completion History Range 2}, <=[End-12]@row, {Task Completion History Range 4}, "Late")

Calculate percentage of tasks completed in the month 12 months ago that were "On Time"

=IF(([-12 On Time]@row + [-12 Late]@row) = 0, "0%", ROUND(([-12 On Time]@row / ([-12 On Time]@row + [-12 Late]@row)) * 100, 2) + "%")

HOWEVER
I am running into another problem. I have the formula below that calculates the first day of the month from 12 months ago. It seems like it's not a valid date in date format. I'm wanting to use the result of this formula to give me the month in text in another column but it always returns "Unparseable". So for example I want 03/05/25 to return "March" or "Mar" in this other column. What am I doing wrong?

Formula to calculate the first of the month, 12 months in the past:

=DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) <= 12, 1, 0), IF(MONTH(TODAY()) - 12 <= 0, MONTH(TODAY()) - 12 + 12, MONTH(TODAY()) - 12), 1)

Keep in mind, the formula is written like this because I'm doing this for all months (-11, -10, -9 etc.). Simply subtracting 1 year isn't an option.

This is the formula I'm trying to use to return the month as text

=TEXT([Start-12]@row, "MMMM")

Any help would be greatly appreciated.

Jgorsich

I'm glad the first message was helpful!

Before addressing the problem you are aware of - your percentage on time formula needs an iferror() wrapper to ensure that if there is ever a month with 0 projects started/finished that it won't break any dependent formulas by throwing a "#Divide by zero" error.

To get your date of 12 months in the past - it might be a LOT easier to just do "=today()-365-day(today())"

Doing it this way avoids having to handle wrapping around the year and having to use the date() function. You can also sub in any other date for "today()" (likely from a column of "months to summarize") pretty easily.

That being said, if you want to stick with a variant of what you had - you are ALWAYS going to subtract 1 year (twelve months before Dec 31st is still in a different year - unless you actually mean "11 months up to 12 months"), so that if() statement can be removed. Additionally, 12 months prior is ALWAYS going to be a different month (with the possible exception of 2/29 based on how you look at it) UNLESS you are trying to round to the nearest first of the month (where 12 months before 5/15/25 is 4/1/24 while 12 months before 5/16 is 5/1/24), but that doesn't seem to be what you are trying to do.

1 month before 12/30 = 11/30

2 months before 12/30 = 10/30

3 months before 12/30 = 9/30

11 months before 12/30 = 1/30

12 months before 12/30 = 12/30 of the year before.

This collapses your date formula down as follows:

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

Now, if what you actually want is "the first of the month that falls between 11 and 12 months in the past", where for 12/15/24 you pick 1/1/24, then you'd need it slightly more complicated.

=DATE(if(month(today())=12,year(today(),YEAR(TODAY()) -1)), IF(MONTH(TODAY()) =12,1, MONTH(TODAY()) + 1), 1)

Whichever you choose, to convert that date into just the name of the month, do the following.

First - note that TEXT() isn't a valid Smartsheet function (Functions List | Smartsheet Learning Center). If that was recommended to you via the AI engine, be aware that I've caught it recommending Excel functions that don't exist inside of Smartsheet before.

As far as I can tell, there is no native way to easily convert to just the name of the month for a date. You'd have two choices that pop out for me:

1. Just a long if() statement: "=if(month([12monthsBack]@row)=1,"Jan",if(month([12monthsBack]@row)=2,"Feb"…))))))))))))"
2. A separate pair of columns that has the numbers 1-12 ("MonthNum") and the names of the months ("MonthName") that you index through:

=index([MonthName]:[MonthName],match(month([12monthsBack]@row),[MonthNum]:[MonthNum],0))

Brandon.Ford

Thank you so much! That was everything I had left to figure out.

Against my better judgement, I'm going with the nested IF statements. I don't really want to create another sheet to reference.
I just started using Smartsheet. There seem to be several functions that work in Excel that Smartsheet doesn't support and doesn't seem to have a graceful solution for. Especially how Date calculations are handled.

I appreciate your help.

Jgorsich

Glad I could help!

Especially if you were a heavy Excel user, like me, bookmark this link:

Functions List | Smartsheet Learning Center

There are only 89 possibly functions (vs Excel's 471). Workarounds get interesting.