Flagging dates overlap

Elena U

Hi Smartsheet stars,

I try to flag jobs overlaps using COUNTIFS function as per screenshots below (tried two different options), however, it doesn't show me 3 overlaps I currently have in my Smart sheet (I created a new A and AAA projects with conflicting dates but it has not been flagged for some reason).

Could you please advise what I do wrong and how to fix it?

Thank you in advance.

Jennifer Kurtz

@Elena U - Good morning!

Your formula syntax looks right! Your formula is written to count the number of times the "Project Name" is an exact match when the additional criteria for start & end dates is met. In your screenshots, most of the Project Name cells are blank. (Is that info you removed for the purpose of posting a screenshot?) It seems like this is why your formula would not be counting anything — there aren't any matching Project names.

If the name of the project doesn't matter, and you want to flag all the things with overlapping dates, you can remove the Project Name range & criterion from your formula.

does this help?

Elena U

Hi Jennifer,

Thanks a lot for your help! I deleted project names from counting as per your advice and formula works now in the way I wanted.

The names were not visible due to confidential reasons but I understood the context correctly and sorted out my problem. I greatly appreciate it.

Elena U

Hi Jennifer,

May I ask you for assistance one more time?

Now I have an issue with a workflow: simple notification on projects overlap. I set up the following parameters for notification: when row added or changed, Overlap flagged, alert someone - send to specific people (myself) - customised message (as per attached screenshots).

I got notification on all projects (both flagged and non-flagged). Moreover, when I change alert someone to send to PM without changing anything else (PM column is empty in the screenshot for confidential reasons, I have real names in Smartsheet), it doesn't send any notifications at all but the workflow still runs successfully.

What can it be? I am new to Smart sheet so can't identify what is wrong.

Many thanks for your assistance.

Georgie

Hi @Elena U,

Are there any other alert workflows on the sheet that don’t require the Overlap column to be checked? Alternatively, did you run the workflow manually (using the “Run now” button)? Running the workflow manually will ignore triggers, so if you had the option “trigger workflow on the entire sheet” selected when you manually ran it, you’d receive notifications for all rows in the sheet whether they were flagged or not. More on this can be found here: Manually run a workflow.

To ensure that alerts are sent out and are only sent for the flagged rows, you could try changing the trigger block to “When any field changes” and then adding a condition block for when Overlap is checked, as seen below:

For more on condition blocks, take a look here: Condition blocks: Filter what your automated workflows send. 

Does that help?

Georgie