Check everyday before date automation

Platform discussions Special workflows & use cases
1

Hello Everyone,
I am looking to set a status based on a two separate dates.
Example:
Set Status to Late if…
If there is no date in Due Date A column and the date in Due Date B column is two weeks from today’s date or later OR if the date in Due Date A column is two weeks or more later than the date in Due Date B column.

The only answer I see is having a separate “Today’s Date” column. And then a number counting everyday after that date which would stop counting based on the status.

Any thoughts or maybe some good app suggestions appreciated.

1 Like
2

Do you have AI turned on in your account?

3

I do but it seems I may not need to do this now. Thanks though

1 Like
4

Hi @Charlie_F - if you’re open to using apps, Smart Timeline + Duration might help with part of this.

You can track the number of days between Due Date A and Due Date B using a Smart Timeline, then use this automation:

When the duration is more than 14 days,
change status to ‘Late’.

It doesn’t support complex multi-condition logic (like checking if a date is empty + comparing two dates), but if your main need is to trigger based on time gaps between dates, it could be a helpful part of the setup.

Let me know if you’d like help exploring it!

5

@Clover_Gal , looks like I actually do need to get this resolved. As of now, I think my only hurdle is getting a “Today’s date” column which populates every day. I can only get this to work with make.com or zapier it seems

1 Like
6

@Avo_FluidApps , thanks but I don’t think this resolves my issue as I need to have a “Today’s Date” column. A column which populates today’s date every day.

7

What about using AI?

image
image1306×448 70.3 KB

Desiree - www.thecleverclovers.com

8

@Charlie_F

Here’s another idea. Use a formula column to return the results:

IF(
  OR(
    AND(
      {Due Date A} = "",
      {Due Date B} >= FORMAT_DATE(ADD_DAYS(TODAY(), 14),"YYYY-MM-DD")
    ),
    {Due Date A} >= FORMAT_DATE(ADD_DAYS({Due Date B}, 14),"YYYY-MM-DD")
  ),
  "Late",
  "Good"
)

If you need this to be in a status column, you can use the Column Magic app to copy the formula value to the status column:

image
image579×487 18.6 KB

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

9

Thanks @JCorrell ! I forgot about your app. I’ll see if I need it in some other spots and grab it if I need it in more places.

1 Like
10

@Clover_Gal , Thanks! I actually figured out a 3rd way to get just the “Today’s date” column. I didn’t realize AI could determine today’s date based of an arbitrary date. I like your idea as well but I would prefer it to be in a status column which I think i can get with this now.

image
image1102×478 50.9 KB

1 Like
11

@JCorrell , do you know if your app can mimic the AI prompt I ended up using? I ask because the monday AI credit packages are a bit pricy relative to the number of monthly automations you offer.

image
image1446×688 70.2 KB

image
image1446×688 70.2 KB

12

I would attempt to do it like this using a formula and automation:

Formula:

IF(AND(ISBLANK({Due Date A}), DAYS({Due Date B}, TODAY()) >= 14), "Late", 
   IF(DAYS({Due Date A}, {Due Date B}) >= 14, "Late", "On Track"))

Automation “When formula column changes to ‘Late’, set status to Late”

13

@Charlie_F

Yes, you can use a formula to give you the same logic and then update the status column using Column Magic. I believe this formula should do the trick.

IF(
  AND(
    {Date B} <= {Date A},
    {Date B} <> ""
  ),
  "On Time",
  IF(
    AND(
      {Date B} = "",
      {Date A} >= TODAY(),
      DAYS({Date A}, TODAY()) <= 14
    ),
    "At Risk",
    IF(
      AND(
        {Date B} = "",
        {Date A} < TODAY()
      ),
      "Late",
      IF(
        AND(
          {Date B} > {Date A},
          {Date B} <> ""
        ),
        "Late",
        "No match"
      )
    )
  )
)

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

1 Like
14

Hello Kate, are you suggesting this would be the solution if I used the “Column Magic” app? Because monday.com does not offer automations with formula columns.

15

Nope, just using a formula column and then an automation.

16

@ProjectMastermind I think I’m missing something…we can’t create an automation that says When “formula column” changes, then set status to X

17

You’re right. But I did do something with formulas and changing statuses/dates… now I just need to find it to see what I did!

1 Like
18

If formulas feel limiting or you’d like to take the logic further:

  • You could use a workflow in custom apps (through Monday Apps or the App Framework), or try third-party tools like Integromat (Make) or Zapier.

  • Just trigger the logic daily or when dates change, and set the status accordingly.