formula to populate due dates

dues99

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!

Ramzi K

@dues99

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 K

@dues99

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

dues99

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.

Ramzi K

@dues99

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

dues99

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?

dues99

We were very close on this, is there anything that I can do to isolate modified by to a single column?