formula to populate due dates
basically I want my due date to be based of off the status below:
- Bar layout needed – 2 days
- Concept – 5 days
- Design changes – 3 days
- Assembly reviews – 1 day
- Concept – 5 days
so I was trying something like this:
=IF([Status] = “Bar Layout Needed”, =TODAY()+2 workday
I am just trying to get one of them to work first then i can string them all together...I think i'm close just looking for someone to help get me there.
Thanks!
Best Answer
-
You need a row reference for your status column Try this:
=IF([Status]@row = "Bar layout needed", TODAY() + 2)
I would also consider putting the values in a separate lookup sheet (or same sheet in tucked away columns) and then do a lookup based on the Status value. That way you can have unlimited statuses and be able to make adjustments to the number of days without changing your formulas. Would also make the formula much smaller. You can use VLookup (shown in example below) or Index/Match.
Two columns to have your lookup/value pairs (green shading), and then the two columns that have your status selection and the calculated date.
Formula you would use in the Date columns:
=IFERROR(TODAY() + VLOOKUP(Status@row, $Lookup$1:$[Value (days)]$5, 2, false), "")
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers
-
You need a row reference for your status column Try this:
=IF([Status]@row = "Bar layout needed", TODAY() + 2)
I would also consider putting the values in a separate lookup sheet (or same sheet in tucked away columns) and then do a lookup based on the Status value. That way you can have unlimited statuses and be able to make adjustments to the number of days without changing your formulas. Would also make the formula much smaller. You can use VLookup (shown in example below) or Index/Match.
Two columns to have your lookup/value pairs (green shading), and then the two columns that have your status selection and the calculated date.
Formula you would use in the Date columns:
=IFERROR(TODAY() + VLOOKUP(Status@row, $Lookup$1:$[Value (days)]$5, 2, false), "")
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Your first formula works nicely...but will that date change tomorrow? So when I open smart sheet up tomorrow and refresh, will the due date now be 10/10 for the bar layout? I want it to be static and not refresh unless the status changes.
-
Yes, the function Today() will be the current day each day. So if you need a static date, you will need a reference date for change. One way to do it is to add a system date to your sheet and reference that.
Does that make sense?
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
That almost worked. Now whenever I change anything on that line, it updates the modified(date) column....I only want the modified date column to trigger when the "status" is changed, is that possible?
-
We were very close on this, is there anything that I can do to isolate modified by to a single column?
Help Article Resources
Categories
Check out the Formula Handbook template!
