Monster IF formula for date timeline
Link to the sheet:
Trying to make a sheet that auto populates a timeline of dates based on two columns that have multiple options. The formula below in the Decal column works if one option is selected, but I need it to work if Bone@row changes from Sternum to one of the other drop down options and also change if the Species column changes as well as different species and bone combinations require different dates.
=IF(Bone@row = "NA", "NA", IF(AND(Species@row = "Rat", Bone@row = "Sternum"), WORKDAY(Fixation@row, 1, {KAN Holidays Range 1})))
The column that has the variable date is the Trim/Process column which may be 1, 2, or 3 days after the date in the Decal column (excluding weekends and a referenced holiday sheet).
I also need the Trim/Process column to accommodate the possibility for the the Decal column to be NA which means it has to pull it's date from the Fixation column instead of the Decal Column so I don't get the #INVALID DATA error.
Basically, I think I need a long If Then that just contains all the combinations to populate the dates based on the two selected options in Bone and Species columns.
Best Answer
-
To create a sheet that auto-populates a timeline of dates based on two columns with multiple options, you need to adjust your formula to account for changes in both the Bone and Species columns. Your current formula works for a single option but needs to handle various combinations of Bone and Species.
Days to Add Assumptions
I used the following assumptions for the days to add.
- If Species is "Rat" and Bone is "Sternum" add 1 working day
- If Species is "Rat" and Bone is "NA" add 2 working days
- If Species is "Cow" and Bone is "Sternum" add 3 working days.
Handling NA and Errors
If Decal@row is "NA" or an error occurs, the date calculation should fall back to the Fixation date.
IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, ….
The formula for Trim/Process
=IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0))), {KAN Holidays Range 1}), WORKDAY(Decal@row, IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0))), {KAN Holidays Range 1}))
Make it more straightforward with a helper column
The formula becomes much simpler if you add a helper column, like [Days to add].
[Days to add] =IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0)))
[Trim/Process 2] =IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, [Days to Add]@row, {KAN Holidays Range 1}), WORKDAY(Decal@row, [Days to Add]@row, {KAN Holidays Range 1}))
In other words, there are two IFs to determine the date part within the WORKDAY(date, num_days), and three IFs to determine the num_days part. Using a helper column eliminates the three IFs for determining num_days in the main formula.
Answers
-
To create a sheet that auto-populates a timeline of dates based on two columns with multiple options, you need to adjust your formula to account for changes in both the Bone and Species columns. Your current formula works for a single option but needs to handle various combinations of Bone and Species.
Days to Add Assumptions
I used the following assumptions for the days to add.
- If Species is "Rat" and Bone is "Sternum" add 1 working day
- If Species is "Rat" and Bone is "NA" add 2 working days
- If Species is "Cow" and Bone is "Sternum" add 3 working days.
Handling NA and Errors
If Decal@row is "NA" or an error occurs, the date calculation should fall back to the Fixation date.
IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, ….
The formula for Trim/Process
=IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0))), {KAN Holidays Range 1}), WORKDAY(Decal@row, IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0))), {KAN Holidays Range 1}))
Make it more straightforward with a helper column
The formula becomes much simpler if you add a helper column, like [Days to add].
[Days to add] =IF(AND(Species@row = "Rat", Bone@row = "Sternum"), 1, IF(AND(Species@row = "Rat", Bone@row = "NA"), 2, IF(AND(Species@row = "Cow", Bone@row = "Sternum"), 3, 0)))
[Trim/Process 2] =IF(OR(Decal@row = "NA", ISERROR(Decal@row)), WORKDAY(Fixation@row, [Days to Add]@row, {KAN Holidays Range 1}), WORKDAY(Decal@row, [Days to Add]@row, {KAN Holidays Range 1}))
In other words, there are two IFs to determine the date part within the WORKDAY(date, num_days), and three IFs to determine the num_days part. Using a helper column eliminates the three IFs for determining num_days in the main formula.
-
@jmyzk_cloudsmart_jp That is brilliant! I've never been good at combining IF related formulas. This works great and I understand it enough to make my own edits with more options. Thank you!
Two follow up questions:
1)When Bone is NA, I need the [Trim/process] date to be +1 of [Fixation], I can't seem to make the formula do that without a parsing error(Figured it out by adding "+1" ad the end of the first If True string)2) How complicated would the formula be if, using [date to add] as you suggested, I wanted to include an OR statement for the species column.
For example, if I wanted [days to add] to be 5 if Bone@Row = "Sternum" for [Species] Rat or Cow? Is it easier to include an OR statement in there or just have a string of If-And statements like above that just lists all possibilities for all species/bone combinations?
-
I would change the [Days to add] helper column as follows;
[Days to add] = IF(Bone@row = "Sternum", 5, IF(Bone@row ="NA", 1, 0))
- IF(Bone@row = "Sternum", 5: Adds 5 days if Bone is "Sternum", regardless of the species.
- IF(Bone@row ="NA", 1: Adds 5 days if Bone is "NA", regardless of the species.
- 0: Default value if none of the conditions are met.
- If you do not have to consider [Species], the helper column will get simpler.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
