stacked if formula based on DATE and Drop down

kristyf · 07/11/19

Good afternoon all

I am fairly new to smartsheet but an intermediate user of Excel. For some reason, my multiple stacked IF OR formula for Dates is not working - i know it must be simple but ive been staring at it and really need some help

Test Frequency is a drop down list:

Daily/Weekly/Monthly/Quarterly/Yearly

In a column labelled - TASK NOW DUE - i would like a formula that, should a value in the drop down be selected, a new TASK NOW DUE date is calculated from the Task Last Completed PLUS ......

Daily - DAY + 1

Weekly - DAY + 7

Monthly - MONTH + 1

Quarterly - MONTH + 3

Yearly - YEAR + 1

etc

This is what i have so far:

=IF([Test Frequency]1 = "daily", DATE(YEAR([Task Last Completed]1), MONTH([Task Last Completed]1), DAY([Task Last Completed]1 + 1)), OR([Test Frequency]1 = "monthly", [Test Frequency]3 = "weekly", [Test Frequency]6 = "Quarterly", [Test Frequency]8 = "yearly"), DATE(YEAR([Task Last Completed]1), MONTH([Task Last Completed]1 + 3), DAY([Task Last Completed]1), DATE(YEAR([Task Last Completed]1 + 1), MONTH([Task Last Completed]1), DAY([Task Last Completed]1))))

Task Last Completed is a date only column that will be updated using the automated workflow/edit grid function.

From here, i am then wanting to set up CARD/KANBAN view for tasks due based on RYG and timeline criteria

Can someone please help me finish this off? i am losing my mind -i feel like i am close but have stuffed up my IF OR somewhere??

thanks

Example grid - Preventative Maintenance workflow

Andrée Starå · 07/11/19

Hi Kristy,

You wouldn't use an OR statement for this. You only need to use a Multiple IF structure.

Regarding the Card View structure.

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Try something like this.

=IF([Test Frequency]@row = "Daily"; DATE(YEAR([Last Completed]@row); MONTH([Last Completed]@row); DAY([Last Completed]@row) + 1); IF([Test Frequency]@row = "Weekly"; DATE(YEAR([Last Completed]@row); MONTH([Last Completed]@row); DAY([Last Completed]@row) + 7); IF([Test Frequency]@row = "Monthly"; DATE(YEAR([Last Completed]@row); MONTH([Last Completed]@row) + 1; DAY([Last Completed]@row)); IF([Test Frequency]@row = "Quarterly"; DATE(YEAR([Last Completed]@row); MONTH([Last Completed]@row) + 3; DAY([Last Completed]@row)); IF([Test Frequency]@row = "Yearly"; DATE(YEAR([Last Completed]@row) + 1; MONTH([Last Completed]@row); DAY([Last Completed]@row)))))))

The same version but with the below changes for your and others convenience.

=IF([Test Frequency]@row = "Daily", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 1), IF([Test Frequency]@row = "Weekly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 7), IF([Test Frequency]@row = "Monthly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), IF([Test Frequency]@row = "Quarterly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)), IF([Test Frequency]@row = "Yearly", DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row), DAY([Last Completed]@row)))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

kristyf · 07/15/19

Thankyou, thankyou, thankyou!!

It worked!!! Yay!!!!!

I was probably trying to overthink it!

kristyf · 07/15/19

re using the Card view - i would like to have RYGB for items showing what is becoming urgent (ie a weekly task where the task was last completed 10/7 and today is 16/7 - therefore only today and tomorrow to complete the task - so it should be a RED CARD.

Effectively, using the card system to priorities the tasks based on remaining time left from last completed to next due date and how close that is to today.

For example - if there are 5 weekly tasks - all due this week but on different days, and they are NOT COMPLETE, then the ones that are closest to being due (todays date) should be red, those due in a couple of days, yellow and those due in 6-7 days green.

i have attached a screen shot of my simple grid. I have not yet got the formula right for the Priority

My hope is that when i go to CARD VIEW - the Priority column view, the cards are coloured according to the RYG balls and that these cards move based has the 'last complete date' updated.

hoping you can help (again).

i would like for this card view, using the automated workflow, to then drive the tasks lists based on reminders and the end user updating the date the task was completed.

HELP!

thanks

Kristy

smartsheet task screen shot.jpg

Andrée Starå · 07/16/19

Excellent!

Happy to help!

Andrée Starå · 07/24/19

Kristy,

Is this solved?

stacked if formula based on DATE and Drop down

Comments

Help Article Resources

Categories