Help with using System Created date in a SUMIFS formula
Hello!
I am attempting to create a formula in a metric sheet that simply adds a revenue column in another sheet with 2 criteria. 1st criteria is that the "Duration" column = "One Time", the second criteria is where the system generated creation date is greater then/equal to 1/1/2024.
I've tried several formulas and am getting either inparsable or incorrect arguement errors. I finally got the following formula to work but the value returned is incorrect as 0 because you see in my data that there are rows that meet my criteria and the value I expect to be returned is 6,670
=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Created Date}, >="01/01/24")
Thank you for your help in advance!
Best Answer
-
Hi
There are two problems
1) The format of the Date you are looking for.
"01/01/24" is a text string. So you can't do mathematical comparisons (like less than) on it. If you enter the date within a DATE function like this
DATE(24,01,01)
smartsheet will know this is a date
The order is year, month, day, so Jan 31 is DATE(24,1,31)
Then you can use it in your logical expression.
2) The system generated Created Date column is not a date type column so you can't do date comparisons directly
You can however use the DATEONLY function to extract the date part and then use that. You would need to do this in the sheet that holds the date. You can create another column and use
=DATEONLY(Created@row)
You can then make this a column formula and hide the column.
In your SUMIFS refer to the new column instead of {2024 Admin Solutions Out of Scope Created Date}
Your final formula would look something like:
=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Just Date}, >=DATE(24,01,01))
Answers
-
Hi
There are two problems
1) The format of the Date you are looking for.
"01/01/24" is a text string. So you can't do mathematical comparisons (like less than) on it. If you enter the date within a DATE function like this
DATE(24,01,01)
smartsheet will know this is a date
The order is year, month, day, so Jan 31 is DATE(24,1,31)
Then you can use it in your logical expression.
2) The system generated Created Date column is not a date type column so you can't do date comparisons directly
You can however use the DATEONLY function to extract the date part and then use that. You would need to do this in the sheet that holds the date. You can create another column and use
=DATEONLY(Created@row)
You can then make this a column formula and hide the column.
In your SUMIFS refer to the new column instead of {2024 Admin Solutions Out of Scope Created Date}
Your final formula would look something like:
=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Just Date}, >=DATE(24,01,01))
-
@KPH - Thank you for your assistance. I suspected that I could not use the system created date column as I intended. I have used the =DATEONLY before and will use that. Thank you for the clarification on order of month etc.
Really appreciate your help!! Thanks.
-
No problem, glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!