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.

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…