Date formula question

Kelly Ospina

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.

 

 

 

 

 

 

Andrée Starå

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

Kelly Ospina

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. 

 

Andrée Starå

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.

Paul Newcome

Change the month criteria to something along the lines of MONTH(date) + 1

 

 

Kelly Ospina

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. 

 

 

Andrée Starå

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?

Paul Newcome

I meant something like Andree's formula but just changing the month portion a little bit.

 

=DATE(YEAR(Today@row), MONTH(Today@row) + 1, 1)

 

It bumps the month up by one, but leaves the day at the 1st.

Andrée Starå

Paul,

I knew there was something that I missed 

Thanks!