#INVALID DATA TYPE for some functions using DATE

Jessica Venable

Hi: I am trying to generate a sum (cumulative) of all effort "points" for projects started in the current year.

CASE 1: My reference columns are: "Project Start" (date) and "Effort Count (Est. Total)" (text/number).

I used the formula: =SUMIF([Project Start]:[Project Start], YEAR(@cell) = 2020, [Effort Count (Est. Total)]:[Effort Count (Est. Total)]). But this returned #INVALID DATA TYPE.

CASE 2: I used the same syntax to calculate the sum of all project costs for projects started in the current year.

My reference columns are: "Project Start" (date) same as above and "Upcharge Amt" (text/number)

I used =SUMIF([Project Start]:[Project Start], YEAR(@cell) = 2020, [Upcharge Amt]:[Upcharge Amt]). This formula worked fine and returned a result that was about what I anticipated.

My question is:

(1) What is wrong for the CASE 1 Effort Count formula that it is returning #INVALID DATA TYPE?

(2) How can I update the formulas in both CASE 1 and CASE 2 so that it is referencing the CURRENT year, rather than 2020? I don't want to have to update the formulas every year!

Thanks for your help!

Paul Newcome

@lewis hamilton Your post is not something that can be used directly in Smartsheet.

@Jessica Venable You are most likely getting the error because you have blanks or non-date values in your [Project Start] column. Since a blank or non-date value does not have a year, then the YEAR function is going to error out. Try incorporating an IFERROR statement into your original formula like so...

=SUMIF([Project Start]:[Project Start], IFERROR(YEAR(@cell), 0) = 2020, [Upcharge Amt]:[Upcharge Amt])

Jessica Venable

Thank you @lewis hamilton . I have not seen Sarthak Grover's answer - I may not have been searching the forums correctly.

But to be honest, I have NO IDEA what your response means in my case...! I’m really just a novice coder trying to create a workaround formula :) I will search for Sarthak Grover's posts for more insight.

Paul Newcome

@lewis hamilton Your post is not something that can be used directly in Smartsheet.

@Jessica Venable You are most likely getting the error because you have blanks or non-date values in your [Project Start] column. Since a blank or non-date value does not have a year, then the YEAR function is going to error out. Try incorporating an IFERROR statement into your original formula like so...

=SUMIF([Project Start]:[Project Start], IFERROR(YEAR(@cell), 0) = 2020, [Upcharge Amt]:[Upcharge Amt])

Jessica Venable

@Paul Newcome : That worked! Thank you so much. I appreciate your help.

So do you recommend that I always include IFERROR statement to account for potentially non-date/etc. values?

Paul Newcome

I do recommend it. Even if it isn't necessarily needed, it doesn't hurt to include it just in case.

A little more detail on the IFERROR, how it works, and why specific numbers are used:

The IFERROR essentially replaces the error with whatever you put in that second portion. In the above case it was 0. Generally speaking that is a pretty safe "standard". However... If you instead are using "greater than" or "less than" instead of "equals", you need to look at the exact data you are using and adjust the output accordingly.

For example... If you wanted to count everything that happened before the year 2020:

YEAR(@cell) < 2020

If you use the IFERROR to output a 0, you may end up with inaccurate results because 0 is less than 2020. In this case you would want to output a number that is greater than 2020.

IFERROR(YEAR(@cell), 2021) < 2020

Same goes for greater than. You want to make sure you are outputting a number that is less than the minimum.

Say you wanted to compare to look between today and 7 days ago.

AND(@cell <= TODAY(), @cell >= TODAY(-7))

Incorporating the IFERROR into each of these date evaluations will actually require the output of both to be different from the other.

So for the first argument of being less than today, you would want the IFERROR to output tomorrow TODAY(1), but in the case of greater than or equal to 7 days ago, tomorrow actually works for that, so you would want to output 8 days ago TODAY(-8).

AND(IFERROR(@cell, TODAY(1)) <= TODAY(), IFERROR(@cell, TODAY(-8)) >= TODAY(-7))

Of course this is assuming you want errors to be EXCLUDED from your counts. If you wanted to go ahead and include errors, then the opposite of the above would be true.

So using the solution above, if you wanted errors to be INCLUDED in the total from the SUMIF, then you would need to use 2020 instead of 0.

=SUMIF([Project Start]:[Project Start], IFERROR(YEAR(@cell), 2020) = 2020, [Upcharge Amt]:[Upcharge Amt])

So long story short... The IFERROR function can be used in a lot of different ways. Just make sure that you think about the data you are evaluating and how exactly you are evaluating it and intend to use it.

Jessica Venable

@Paul Newcome : this is awesome. Thank you for such a thorough explanation. I appreciate your help.

Paul Newcome

Happy to help. 👍️