How can I change a status based on the relative timeframe of a date column? For example: Date Column: 03/09/24 is in the past so the status column with status of “Past Due” would be set. Date is 04/21/24 is within the next two weeks, so the status column with status of “Next 2 Weeks” would be set. Thank you.
1 Like
Hi Jeff,
I’m Fitri from NewAge. Welcome to the Monday community! 
It’s possible to utilize Monday automations, as shown in the screenshot, to address your first use case: setting the status to “Past Due” when a date has passed.
However, I haven’t been able to find a solution for the second use case using Monday’s existing automation recipes.
At NewAge, we aim to create apps that enhance your Monday.com experience. One of our ideas is AI-Powered Automations, where AI prompts can be integrated into your automations. We hope that our future apps can meet your needs effectively.
For instance, you would be able to create the following automation:
When an item is created and the date is set
Run the prompt: “If the date is >= 14 days from today, set the status column to ‘Next 2 Weeks’.”
We’re eager to hear your thoughts on this idea or any other suggestions you may have.
Also, feel free to check out this post where we’ve shared other app ideas and vote for the ones you find most useful for your workflow: [Poll] New Monday App Ideas!
Thank you.
Fitri
Hi Jeff,
Thank you for your question.
I can confirm that your requirement can be solved by the app Advanced Automations for Monday.com which will allow you to create automations as code to help solve business requirements.
Using Advanced automations you would be able to set a Scheduled Trigger which would run your script on the schedule you define.
Your script would then used the Monday.com API to fetch the value from the column and would get the date into a variable in the script.
Your script would then be able to check if the value was in the past compared to todays date and if it was it could set the status column with the status of Past due.
Your script would also then check if the value was within 14 days from the current date and if it was it would set the status column with the status of Next 2 weeks.
We have an example template called Set Status depending on permsion of the user which shows the logic required to update the status of an item and you will be able to use this a guide to help write the script that you require.
You can see more detailed information on how the app works in its documentation page here.
I hope this information helps.
Regards,
Kristian
Thx, Kristian. Unfortunately, my company restricts 3rd party apps. Hopefully there’s a way to accomplish this within native Monday.com.
1 Like
Hi @jland ,
Would this automation work for the second use case you described?
1 Like
Hi Kayla, thanks for the suggestion. It pseudo can work if the original date is far enough out and I wait for the automations to kick in. But I want the checks to be continuous. Basically, I want the automations to work like this:
When date is today, change status to “Today”. When date is before today, change status to “Past Due”. When date is tomorrow, set status to “Tomorrow”. When date is between tomorrow and 14 days from now, set status to “Next Two Weeks”. When date is 15 days out or more, set status to “Future”.
You could use @kaylak 's suggestion and a few others to set this up. So for today for example, “When due date arrives at 12:01am, set status to Today.” Also, “When due date has passed, set status to Overdue.” You’d need an automation for each time period and status but it could be done. I have something similar set up with due dates so it sends emails to clients and certain time intervals. It just takes one automation for each status.
1 Like
Hi Anika
Thx for looking at this and those ideas. It seems like the automations depend on the date to arrive or pass. I need the date to simply “be”. For example if i have a status of “next two weeks”, and I create a new item for 3 days from now, the automation seems to need to wait until a certain date passes, so it doesn’t set the status. In other tools like Trello I can set an automation which says when a date is x days from today, change the status to y. I need something like that. Hoping you have another idea for this. Much appreciated.
I would not use a status column to represent the relative timeframe. Do you have access to the Formula column?
You can to do what you want to with that column. Check out Formula Use Cases. There’s a formula you can copy… "convert a date into a label that says, “This week”, “Next week”, “Past due”, etc. Hope this helps!
Hey Jeff,
I know this is not the most intuitive but look for *Every time period":
Then select Daily
Your automation will run daily
Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.
- Create formulas without using the Formula column (and avoid its limitations)
- Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
- In one formula, update multiple columns from multiple items.
Check our blog for real use cases.
Thanks, Amy. I tried that and may have to go with that, but my ultimate goal is use the Status for the Kanban View, which is the best way to view this data succinctly.