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

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

BullandKhmer

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?

Warren B

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))

Genevieve P.

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.