INVALID DATA TYPE error when using MONTH()

Hi,

I'm working on a formula that will use a date to reference a value in another sheet:

The sheet that will be referenced looks like this:

I'm planning on using INDEX and MATCH to get a date value from a row in the first sheet to return a percentage value (based on the month that row occurs in) from this sheet. The problem I'm running into off the bat is that the the months in this referenced sheet is throwing an INVALID DATA TYPE error, even when using a simple MONTH() command (for example, =MONTH([Column2]14) for the cell containing 01/01/2021). I've tried formatting in MM/DD/YYYY, MM-DD-YYYY, and using the date formatting button within Smartsheets itself but am still getting the same error. Has anyone run into this issue or can provide any help?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Without digging too deep into the ranges, lets start with removing that last ", 0" bit.

=INDEX({US B&M}, MATCH(MONTH([Scale Date]@row), {testrange}, 0), MATCH("US B&M", {testrange}, 0), 0)

=INDEX({US B&M}, MATCH(MONTH([Scale Date]@row), {testrange}, 0), MATCH("US B&M", {testrange}, 0))

If that doesn't work, then we need to look at your ranges. Based on the names of your ranges (if those are in fact what you are using), we will also need to adjust those.

You should have three different ranges established.

{INDEX RANGE} = [Column3]2 through [Column7]13 = You should only select the percentages since those are the only things you want to pull.

{ROW MATCH RANGE} = Month2 thorough Month13 = You should select only those rows within the Month column that contain actual month numbers. You do not want to include the cell that contains "Month".

{COLUMN MATCH RANGE} = [Column3]1 through [Column7]1 = You should only select the cells in the first row that contain data you will actually be matching on to determine which column to pull from.

All comments

Paul Newcome

What is the column type?

Faheem Shameem

Hi Paul,

The column type is Text/Number.

Paul Newcome

That would be the issue. Since it is a text/number type column, it is storing it as a text string and not an actual date value.

Are you able to provide more details along with the formula you are currently trying to use the MONTH function in?

Faheem Shameem

I see, tried changing the column type to date but it appears that the %'s change to dates as well. If i just transpose all the dates to be in one column (and format it to Date) and move the "Row#" fields be where the dates are now, would that work? For reference, I'm not actually using the MONTH() function in my final formula I'm just using it to triage and figure out what's wrong. I'm converting an excel formula but changing the Hlookup to an INDEX for Smartsheets.

Original formula:

=HLOOKUP([date to lookup],[reference field],MATCH("Row1",[reference field],0),TRUE)

Adapted formula:

=INDEX([date column], MATCH("Row1", [reference field], 0))

So if this formula is looking at a cell where a purchase was made in Jan of 2021 then it would return 100%, if it was made in Mar 2021 it would return 4.64%, etc.

Paul Newcome

I would suggest restructuring if you are able so that you have the dates in a date type column.

If you are unable to do that, it should be possible with an extra column on the target sheet and an extra row on the source sheet.

The other problem you are going to run into is that using

INDEX({range to pull from}, MATCH(...............))

You are only ever going to pull from one column. You would need an

INDEX({range to pull from}, MATCH(...............), MATCH(...............))

So that you can match on the "Row 1" to determine which row and the "date" to determine which column.

Faheem Shameem

Hi Paul,

This has been very helpful and I came across some of your responses in other threads that were also helpful. So I ended up with making this formula that is referencing the below sheet

=INDEX({US B&M}, MATCH(MONTH([Scale Date]@row), {testrange}, 0), MATCH("US B&M", {testrange}, 0), 0)

If I'm understanding Index and Match correctly, this formula will take the month from [Scale Date] (which is a column that exists in the sheet where I'm building this formula) and match it to the appropriate row in the 'Month' column. This designates the row that date will be pulled from. The second Match will look for the column containing "US B&M". The first part of the Index should then pull the appropriate cell from the field {US B&M}, which is Column 3. {testrange} is all of the cells you see here.

However, I'm getting an Invalid Value error when running the formula. I don't think that value the formula is looking for is outside of the expected range, but maybe I'm not understanding fully?

Paul Newcome

Without digging too deep into the ranges, lets start with removing that last ", 0" bit.

=INDEX({US B&M}, MATCH(MONTH([Scale Date]@row), {testrange}, 0), MATCH("US B&M", {testrange}, 0), 0)

=INDEX({US B&M}, MATCH(MONTH([Scale Date]@row), {testrange}, 0), MATCH("US B&M", {testrange}, 0))

If that doesn't work, then we need to look at your ranges. Based on the names of your ranges (if those are in fact what you are using), we will also need to adjust those.

You should have three different ranges established.

{INDEX RANGE} = [Column3]2 through [Column7]13 = You should only select the percentages since those are the only things you want to pull.

{ROW MATCH RANGE} = Month2 thorough Month13 = You should select only those rows within the Month column that contain actual month numbers. You do not want to include the cell that contains "Month".

{COLUMN MATCH RANGE} = [Column3]1 through [Column7]1 = You should only select the cells in the first row that contain data you will actually be matching on to determine which column to pull from.

Faheem Shameem

Hi Paul,

Following these steps worked, I'm getting what is expected now. Thank you for all your help!

Paul Newcome

Happy to help. 👍️

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.