Date Formula

Hello! I have created a sheet, that based on the month, a workflow will be triggered to send an email out on a certain date. For example, if the month is August, Sept or October, an email be triggered to be sent on 12/15/23. If Nov, Dec or January is checked, then the email will be triggered to be sent on 3/15/23. Smartsheet helped me with the formula (see attached document)

So, I set a test up to see if I would receive an email today (12/15) for my test line that had September checked. However, the field does not appear to be a date field. When I try to add 7 days to the send date, the date is not correct (it shows up as 12/15/20237).

I'd appreciate any help! Please let me know if you need more information.

Thank you!! Sheryl

Find more posts tagged with

Formulas

Automations

Accepted answers

Sheryl Sheldon

@Eric Law Hi Eric, I think that may work! When I created the "Helper" column and put the formula in the "Send Date" field, the date appeared

Smartsheet.docx

to be a date. Thank you for your help! Much appreciated!!

All comments

Eric Law

https://community.smartsheet.com/discussion/114427/date-formula

Sheryl, I know that sometimes automations take a little bit to run. Have you tried testing the automation by using the run now option? It's located on the saved workflows screen and it will let you "test" automations without needing to wait for a date.

Sheryl Sheldon

@Eric Law Hi Eric, so I waited until about 9:00 to receive the notification. Then, I did test it manually and received the email at that point when I kicked it off manually. However, since the "Approval Date" field does not add 7 days to the "Send Date" field, but makes the date 12/15/20237, I don't think the "Send Date" field is a true date hence why I did not receive an email at around 8:00 am this morning.

Eric Law

@Sheryl Sheldon are you the owner or admin of the sheet? Usually Smartsheet will ONLY allow you to use a run on date automation with a date field, so it seems like Send Date is a date column. But, in case it isn't, you should probably ensure that it is by editing column properties.

Sheryl Sheldon

@Eric Law I am the owner of the sheet. Both Send Date and Approval Date are date columns.

Eric Law

@Sheryl Sheldon how did the dates populate? Were they copy pasted or entered through a form? How will you be populating these dates? Did you click into it and select the date, or did you type it out?

Sheryl Sheldon

@Eric Law The "Send Date" is populated from this formula:

=IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, ""))))

The Approval Date is also a formula: =[Send Date]@row + 7

Thank you for your help in trying to figure this out! I appreciate it!!

Sheryl

Eric Law

https://community.smartsheet.com/discussion/comment/409865#Comment_409865

Try putting DATE around that IF and see if it works

Like =DATE(IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, "")))))

Sheryl Sheldon

@Eric Law Unfortunately, I received an "#Incorrect Argument Set" when I tried that.

Eric Law

@Sheryl Sheldon So... this is going to be easier if you had a helper column that you put that "date" in and reference it. Then you can use the following formula in the Send Date column. This is assuming that your date is always 15. Hope this fixes it all!

=DATE(VALUE(RIGHT([Helper]@row, 2)), VALUE(LEFT([Helper]@row, FIND("/", [Helper]@row) - 1)), 15)

Sheryl Sheldon

@Eric Law Hi Eric, I'm not sure if I understand what you mean exactly. How would the Helper Date column get populated?

Eric Law

@Sheryl Sheldon You need to convert your text into a date. The reason for the helper column is to simplify your formula. Without the helper column, you would need to replace the 3 [Helper]@row with the formula you have in the Send Date column. Which is fine... unless you need to change something or something gets changed and you need to fix it.

Does that make sense?

i.e. Helper =IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, ""))))

Send Date =DATE(VALUE(RIGHT([Helper]@row, 2)), VALUE(LEFT([Helper]@row, FIND("/", [Helper]@row) - 1)), 15)

Instead of

Send Date =DATE(VALUE(IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, "")))), 2)), VALUE(LEFT(IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, "")))), FIND("/", IF(OR(CONTAINS("November", [Invoice Period by Month]@row), CONTAINS("December", [Invoice Period by Month]@row), CONTAINS("January", [Invoice Period by Month]@row)), "3/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("April", [Invoice Period by Month]@row), CONTAINS("February", [Invoice Period by Month]@row), CONTAINS("March", [Invoice Period by Month]@row)), "6/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("May", [Invoice Period by Month]@row), CONTAINS("June", [Invoice Period by Month]@row), CONTAINS("July", [Invoice Period by Month]@row)), "9/15/" + [Year of Invoice Period]@row, IF(OR(CONTAINS("August", [Invoice Period by Month]@row), CONTAINS("September", [Invoice Period by Month]@row), CONTAINS("October", [Invoice Period by Month]@row)), "12/15/" + [Year of Invoice Period]@row, ""))))) - 1)), 15)

Sheryl Sheldon

@Eric Law Hi Eric, I think that may work! When I created the "Helper" column and put the formula in the "Send Date" field, the date appeared

Smartsheet.docx

to be a date. Thank you for your help! Much appreciated!!

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.