Formula (need help!) - when a date column within x days of that date, change a formula or status column

Platform discussions
1

Hi,
I could really use help with this formula. When a date (in a date column) is within 14 days (before the date), I want to mark either a status or the formula column as yes. If it is not within 14 days, it should be marked as no. Then after the date, I want to mark the column as completed. Anyone know how I can do this?

2

@tara

Try this:

IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) < -14,
   "No",
   IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) > 0,
      "Complete",
      "Yes"
   )
)

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating monday subitems using Make/Integromat The Monday Man Way
Contact me directly here: Contact – The Monday Man

3

thank you very much! I tried this but I think there is an issue that I could use your help with. If you look at June 29, it is more than 2 weeks in advance, so it should not be “complete”, it should be no until it is 2 weeks in advance. Also I saw that if there is no date entered into the column, that the column is marked “yes”. Could it be blank if there is no date?

image
image765Ă—319 11.9 KB

4

@tara

If I am now understanding what you want…

IF({Date} = "", "",
   IF(ABS(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY()))) > 14,
      "No",
      IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) > 0,
         "Complete",
         "Yes"
      )
   )
)
1 Like
5

@tara

Did that work for you?

6

Hi, I think it needs some tweaks but I am waiting to hear back from the client…I will let you know - thanks!

7

Hi Jim,
Would you be able to help me with this again? The formula should be if the (date) is filled in and it is a date in the future, then the formula column should say “yes” and if there is not date then it should remain blank. I guess once the date passed it should turn to completed (is that possible?)thanks again for your help.

8

@tara

IF({Date} = "", "",
   IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) < 0,
      "Completed",
      IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) > 14,
         "No",
         IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) > 0,
            "Complete",
            "Yes"
         )
      )
   )
)

Jim - The Monday Man (YouTube Channel)
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session

9

Hi Jim,
So I tried to update the formula a bit and here is what I have so far

IF({Ship Date} = “”, “”,
IF(DAYS(FORMAT_DATE({Ship Date}), FORMAT_DATE(TODAY())) < 0,
“Completed”,
IF(DAYS(FORMAT_DATE({Ship Date}), FORMAT_DATE(TODAY())) > 14,
“No”,
IF(DAYS(FORMAT_DATE({Ship Date}), FORMAT_DATE(TODAY())) > 0,

        "Yes"
     )
  )

)
)

My issue is that the “no” does not show up for anything. I want the no to show up if there is no date entered (the 14 days is not relevant any more). Could you help me out again? thanks!

10

@tara

…based on our conversation:

IF({Date} = "", "Not Set",
   IF(DAYS(FORMAT_DATE({Date}), FORMAT_DATE(TODAY())) < 0, "Completed", "Yes")
)
11

Thank you very much! This worked exactly how I need it to. I really appreciate you taking the time to help me.

1 Like
12

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.