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
Best Answer
-
@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
to be a date. Thank you for your help! Much appreciated!!
Answers
-
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.
-
@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.
-
@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.
-
@Eric Law I am the owner of the sheet. Both Send Date and Approval Date are date columns.
-
@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?
-
@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
-
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, "")))))
-
@Eric Law Unfortunately, I received an "#Incorrect Argument Set" when I tried that.
-
@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)
-
@Eric Law Hi Eric, I'm not sure if I understand what you mean exactly. How would the Helper Date column get populated?
-
@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)
-
@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
to be a date. Thank you for your help! Much appreciated!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
