Automation to create trigger when date moves out of current month

KGetchell · 01/20/22

One of the ways we utilize managing our projects in SmartSheet is to use the data to provide invoice projections.

Our data sheets have a "Projected Invoice Date" column, which is driven by a formula of another column + X days. I think have a separate sheet (which i deploy to a Dashboard), which shows our weekly and monthly invoice projections ($).

Due to the nature of our industry, project completion dates (which would trigger invoicing) are very fickle and change often. Invoice projections reported at the beginning of the month can vary greatly as the month progresses.

Now to the question....

Are you able to set an automation trigger that would prompt requiring an update when the "Projected Invoice Date" moves out of the current month?

Example: Invoice projections pulled on 01/01/2022 = $100,000. If a project's "Projected Invoice Date" moves out of the current month, then Invoice projections would decrease by the project's dollar amount. We try identify factors as to why a project's completion (invoicing) is delayed and how many $$ are impacting what is anticipated to invoice in that month. If a project's "Projected Invoice Date" changes to a different month, I need the user to provide a reason as to why (weather delay, customer schedule change, etc.)

Thank you in advance!

Andrée Starå · 01/20/22

Hi @KGetchell

I hope you're well and safe!

Yes, we could use a so-called helper column with a formula to check if it's the current month or not.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KGetchell · 01/20/22

Thank you, Andree!

Here is a snapshot from one of our sheets. We utilize the "Projected Invoice Date" and "Order Total $" columns to calculate our weekly and monthly invoice projections. Projects will often shift due to a number of factors, which is okay, BUT we need to be able to track when revenue (invoiced $) pushes out of the current month.

This second screenshot is a data sheet that is used to post to our Dashboard so our weekly projections are visible and updated in real time. Data is linked to each of the source sheets.

We report our projected invoicing $ to the company and when we miss our invoice projection goal, we need to be able to provide context as to why. Being able to easily identify which projects shifted is key.

Ideally, I'd like to have a trigger that if a Projected Invoice Date moves out of the current month, the user will need to select an option from a single-select drop down that would have a list of typical reasons for a shift.

Hope this is clear! Thank you!

Jeff Reisman · 01/21/22

https://community.smartsheet.com/discussion/comment/316762#Comment_316762

As Andree pointed out, a helper column (maybe "Projected Invoice Date Extended") would be the key here. Use the MONTH function to compare to today's month, and set a value using an IF statement. You could then add an automation rule that would send an update request to the user to add an explanation.

=IF(MONTH([Projected Invoice Date]@row) > MONTH(TODAY()), "Yes", "")

In your automation rule, when a row changes and "Projected Invoice Date Extended" changes to Yes, send an update request to whomever needs to fill it out.

KGetchell · 01/22/22

@Jeff Reisman Thank you! I tried your suggestion and what I'm running into is the formula is resulting in "Yes" being in all rows where the Projected Invoice Date is not in the current month.

It is typical that we have projects that are not scheduled to invoice in the current month, like the example below.

The goal would be to create a notification for the user to select from the "Invoice Extended Reason" column IF the Projected Invoice Date moves out of the current month IN the current month.

For example, Project A1 is currently showing 01/06/2022 as the current Projected Invoice Date. If the user changed the Projected Invoice Date to 02/05/2022 (out of the current month), then they would be required to provide a reason as to why it moved out of the current month.

Jeff Reisman · 01/24/22

https://community.smartsheet.com/discussion/comment/317079#Comment_317079

What you're asking for is a formula that takes into account what value a cell USED TO HAVE; unless you're recording that previous value someplace else, you can't base a formula on what the cell value used to be.

In your image above, it looks like all the projected invoice dates are in the same month as the install dates. Does it always start that way?

Or is there some basic standard you have for setting the projected invoice date based on the install date? From your example it looks like 5 days. If that's the case, you could base your formula on the projected invoice date moving out more than 5 days past the install date:

=IF([Projected Invoice Date]@row > ([Install Date]@row +5), "Yes", "")

Automation to create trigger when date moves out of current month

Answers

Categories