Help with Date Formula

Help Please!

I am generating an alert that will send based off a date that I'm creating in a field. It will be the combination of Today's Year, a month that's calculated as the "month before a renewal date", and the day will be the 2nd of the month.

I have researched enough to see that I need to use the DATE function in order to have Smartsheet view the data as a date (and not just numbers/text). I am having success using [_Today's Year]@row but when I try to pull in [_Month of Reminder}@row, I'm getting an invalid data type error message.

Would someone mind taking a look to see if you can find where I'm going wrong?

Here's the current formula:

Here's my error message:

And here's the calculation for how I'm getting to the _Month of Reminder:

Thanks in advance for any help you can provide!

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

Is [_Date to Trigger Workflow] formatted as date?

Danielle Arteaga

Hoping I can maybe simplify this for you a little bit.

Here's the sample data I worked with:

The Renewal Date column is set as a DATE column type and was manually entered.

The Year column contains this formula: =YEAR([Renewal Date]@row)

The Month column contains this formula: =MONTH([Renewal Date]@row)

The Reminder Month column contains this formula: =Month@row-1

The Year, Month, and Reminder Month columns can all be hidden after creation, because they are just there to "help" create your Reminder Date.

The Reminder Date column is also a DATE type column. (Very important, because if you don't set it to that type, you'll get an error for your formula.) It contains this formula:

=IF(Month@row = 1, DATE(Year@row - 1, 12, 2), DATE(Year@row, [Reminder Month]@row, 2))

This formula first checks if the Reminder Month is 0. This will happen when the Renewal Date is in January. So, we force the month to become December and we make sure the year is one year less than the Renewal Date. (See the row for Kelly - renewal date is January 12, 2024, but Reminder Date is December 2, 2023, even though Reminder Month = 0)

Otherwise, the formula is basically adding the Year and Reminder Month columns together with a forced Day of 2.

Hope this helps!

Beth Fantozzi 1

@Carson Penticuff Yes, the column is set to a date column. Since my error message is #INVALID DATA TYPE, that was my though too...that the column type wasn't lining up

Beth Fantozzi 1

@Danielle Arteaga Thank you for your response and I appreciate the effort! The use case that you are proposing doesn't quite line up with what I need it to do. It's close though!

My issue is that when I use the DATE function and have the formula pull in the month from [Reminder Month]@row, I get an error message. The formula works when I pull in Year@row in the DATE function but not when I try to pull in the month.

When I put in a new column with just a number representing the month (NOT by using a formula), it works. So there must be something about pulling in the results of a formula to use as data for the date?

I've just stumbled into a solution by using the value function. I added a helper column and am using VALUE to return just the number from the formula-created [Reminder Month]@row.

While I do have a solution - I'm wondering if someone can weigh in on why I needed to do this? Hoping to learn something larger here!

TIA

Danielle Arteaga

Is your column titled [_Date to Trigger Workflow] set as a Date column type? The error you're getting suggests that it is not. That might be very quick fix to your problem. Otherwise, I'm at a loss for why you're getting that error.

Beth Fantozzi 1

@Danielle Arteaga Yes, that column is set as a Date column type. I was able to get the DATE function to work by adding a helper column that used VALUE to pull in the month number from the formula I'd used to calculate the month. So I have a working formula now :)

I appreciate your help, Danielle!

Danielle Arteaga

Glad you found a solution, Beth! Sometimes, just posting in here and thinking through it away from the sheet is more help than any advice you get. ☺️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.