Using dates in sheet summary formulas
Hello,
I have a sheet that is pulling data from Microsoft Dynamics CRM using the connector where the date data is not coming across correctly if the column is setup as a date format. I have therefore setup the column in the sheet as a text field.
I have a summary formula which uses this column to summarize data by quarter but I cannot get it to work using a column that is a text format. How do I convert the text format to a date format in a summary field? I have also tried to create another field in a date format that stores the corrected date and reference that in the summary formula, but the summary formula does not seem to like using fields that are based off calculations as I get an Invalid Data Type error.
Here is my current formula for my summary field that works if the sheet column is a date format. How do I change it to reference a text column with date data?
=SUMIFS([Sales Order Value LC]:[Sales Order Value LC], Region:Region, OR(@cell = "USA East", @cell = "USA West", @cell = "USA Central"), [Close Date]:[Close Date], AND(@cell <= DATE(2023, 9, 30), @cell >= DATE(2023, 7, 1)))
Thanks
Answers
-
Why dont you use the column that the CRM is feeding to parse a new date column.
I've built a few solutions in the past where I've had the same issue, then I built a day, month, year and date column.
for example the day column was =LEFT([CRM date]@row,2) and the used MID formulas to parse the month and year.
The the date column was =DATE(Year@row,Month@row,Day@row)
Would that work?
-
I tried that and I have no problem parsing the day, month and year into separate columns but I get an invalid data type when parsing the date column using your approach. I wonder if its because I have to use an IF to parse the day and month due to the leading 0.
=IF(LEFT([Close Date]@row, 1) = 0, MID([Close Date]@row, 2, 1), LEFT([Close Date]@row, 2))
-
Hey @Warren B
Would you be able to post a screen capture showing your full formula in the sheet and how the date comes in when it's a text value?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!