Formula to check days between today's date and purchase date. If <90, then check the status and update a different status column. If >90, then go through series of ranges and return a value based on what range it fits in

Platform discussions Special workflows & use cases
1

Hi, I am trying to figure out how to do the following and I am new to Monday. This would sure make me look great!

Basically we report against how much revenue we recognize based on how many days a project has been open. If the project has been open less than 90 days, then the stage the project is in dictates how much revenue we have recognized. See below:

Status

Questionnaire - 30% complete
Design - 60% complete
Development - 80% complete
Sandbox - 90% complete
Complete - 100% complete

If the project has been open for more than 90 days, the number of days the project has been open then dictates the amount of revenue we have recognized. See below:

Purchase Date

0-90 days - Use % above
90 - 120 days - Automatically 75% complete
120-180 days - Automatically 90% complete

180 days - Automatically 100% complete

We use a project purchased date column for reference.
Screenshot 2022-11-08 at 2.00.46 PM

Then we have a status column which reflects the first group of statuses above.
Screenshot 2022-11-08 at 2.00.39 PM

I want to update a different column’s status, based on how much revenue we have recognized with the above logic.
Screenshot 2022-11-08 at 2.00.33 PM

I am thinking The first thing I need to do is count the days. I have learned to do that using:
ROUND(DAYS({Purchase Date},TODAY()),0)

I would imagine I would then check to see using an IF statement whether or not the returned value is less than 90 and then and ANDIF statement to see what range the number of days past the purchase date falls in by multiple and if statements, but I really just don’t know.

Please help!!!

4

Hi Nachiel! Thank you for this. Thing is Date 2, Date 1 I don’t think will work. I would need something that compares the date with today’s date. Does that make sense? So would that formula work if either Date 1 or Date 2 was changed to TODAY?

5

I am guessing I am doing this wrong:

IF(DAYS(TODAY(),{Purchase Date})<90,
SWITCH({Recognition},"Questionnaire ",30,“Design”,60,“Development”,80,“Sandbox”,90,“Complete”,100),(IF(DAYS(TODAY(),{Purchase Date})<120,75,(IF(DAYS(TODAY(),{Purchase Date})<180,90,100)))))

6

IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>=0, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=90, {Project Status}=“Questionnaire”), “Questionnaire - 30% complete”,

IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>=0, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=90, {Project Status}=“Design”), “Design - 60% complete”,

IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>=0, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=90, {Project Status}=“Development”), “Development - 80% complete”,

IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>=0, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=90, {Project Status}=“Sandbox”), “Sandbox - 90% complete”, IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>=0, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=90, {Project Status}=“Completed”), “Complete - 100% complete”, IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>90, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=120), “75% Recognition complete”,

IF(AND(ROUND(DAYS(TODAY(), {Purchase Date}),0)>120, ROUND(DAYS(TODAY(), {Purchase Date}),0)<=180), “90% Recognition complete”,

IF(ROUND(DAYS(TODAY(), {Purchase Date}),0)>180, “Recognition 100% complete”, “”))))))))