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

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?

@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

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?

@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

@tara

Did that work for you?

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

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.

@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"
)
)
)
)
``````

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!

@tara

â€¦based on our conversation:

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

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

1 Like

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