Date formula question
Good morning, we have a sheet we are using to track leads. I've been asked to set up an automated update request to send leads that have a certain status out on the first business day of each month.
In the automated actions, there is no way to set a recurring update request like there is on the row-level update request. So I need to find a way to create a formula that will fill-in an update date in a date column that can be used to trigger the update.
Here is a published link to the sheet: https://app.smartsheet.com/b/publish?EQBCT=872f03d505b44ad58688db39b0ce0950
Thank you in advance for any feedback or suggestions.
Comments
-
Hi Kelly,
Try this. (Update the columns to match yours)
The formula is looking at a date column called Today.
=DATE(YEAR(Today@row); MONTH(Today@row); 1)
The same version but with the below changes for your and others convenience.
=DATE(YEAR(Today@row), MONTH(Today@row), 1)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
I hope this helps you!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée,
Thank you, but I'm not sure this is quite what I'm trying to do.
I put the formula in, and referenced the "created" date column in the formula. It returns the first day of that month, but that date is in the past so I don't think it can be used to trigger an update.
If the leads get added the last week of October, then the update request needs to go out the first week in November.
-
Happy to help!
Add a column (Today) with =TODAY() in the cell of that row and then set up the rule to look at the update request date in combination with your other criteria.
Would that work?
Edit: Maybe I'm thinking about this the wrong way. I'll get back to you a little later.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Change the month criteria to something along the lines of MONTH(date) + 1
-
Thanks Paul, but I think that still isn't quite it because then if the date the lead was entered was 10/26, the update date would come out as 11/26 which is too late. The update needs to go out during the first week of the next month.
-
It sounds to me as my original formula would work if you have a column with the date for the when the lead was entered. Do you want to use created as that date?
Edit: Add a helper column (Today) that reference the Created column.
Would that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Paul,
I knew there was something that I missed
Thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!