Smartsheet Formula

Brian Lee

Good morning all,

I am seeking some help in building a formula on Smartsheet that will calculate whether a specific individual has worked on their current ship for more than 182 days as the date range. I am referencing data from another sheet.

I wrote this formula but Smartsheet states it is unpareseable.

See below:

=IF(DATEDIF(INDEX({Disposition Plan - SHIP START}, MATCH({Disposition Plan - ASSIGNED TO}, Individual@row, 0)), INDEX({Disposition Plan - Ship End}, MATCH({Disposition Plan - ASSIGNED TO}, Individual@row, 0)), "d" ) >180, "Yes", "No")

I appreciate any help and thank you for your time.

Brian Lee

I tried this formula below but also no updates:

=IF((INDEX({Disposition Plan - Ship End})), MATCH({Disposition Plan - ASSIGNED TO} Individual@row, 0))- (INDEX({Disposition Plan - SHIP START}, MATCH({Disposition Plan - ASSIGNED TO} Individual@row, 0))) > 180, "Yes", "No")

KPH

Hi @Brian Lee

Can you explain or show your data (redacted if necessary)? Specifically, I'd like to know:

1. Are Ship End and Ship Start date columns?
2. Assigned To holds the individual's name, is this a text column?
3. Are these three columns all in the same sheet?
4. But not the sheet you want to put the formula in?
5. Is it correct that in the second sheet you have a list of names and want to know if the difference between start and end date in the first sheet is >180.

If the answers to all of these are "yes" then I have two solutions that could help. But if any of those things are "no" then we can rethink!

Solution 1 - Simple Using a Helper Column

In the first sheet, you can add a duration column (that you can hide) using the formula

=[Ship End]@row - [Ship Start]@row

In your main sheet your INDEX MATCH can be

=IF(INDEX({Disposition Plan - Duration}, MATCH(Individual@row, {Disposition Plan - ASSIGNED TO}, 0)) > 180, "Yes")

Solution 2 - Probably what you want - A more complicated formula - but no helper column

Replace this

=IF((INDEX({Disposition Plan - Ship End})), MATCH({Disposition Plan - ASSIGNED TO} Individual@row, 0))- (INDEX({Disposition Plan - SHIP START}, MATCH({Disposition Plan - ASSIGNED TO} Individual@row, 0))) > 180, "Yes", "No")

with

=IF(INDEX({Disposition Plan - Ship End}, MATCH(Individual@row, {Disposition Plan - ASSIGNED TO}, 0)) - INDEX({Disposition Plan - SHIP START}, MATCH(Individual@row,{Disposition Plan - ASSIGNED TO}, 0)) > 180, "Yes", "No")

I have highlighted the differences in bold. Commas, and parentheses, and the search for item and the search in range were reversed.

Brian Lee

Hi @KPH

Here are my answers to your questions:

1. I have a column that actually counts the number of days already in the sheet.
2. Assigned To Column is a drop down column with the individual's names
3. All columns are located in the same sheet
4. That is correct, I have another sheet already but would like the data to be pulled from a different sheet.
5. The second sheet only has a column of the individual names.

I have more criterias that might be helpful in building this formula.

The goal is to determine how long has an individual done their contract onboard their current ship and if it is more than 2 years (365 days).

Brian Lee

@KPH

I tried the formula, see below:

=IF(INDEX({Disposition Plan - Ship End}, MATCH(Individual@row), {Disposition Plan - ASSIGNED TO}, 0)) - INDEX({Disposition Plan - ASSIGNED TO}, MATCH(Individual@row, {Disposition Plan - ASSIGNED TO}, 0)) > 180, "Yes", "No")

It is giving me a Unparseable error.

Brian Lee

@KPH Here is a sample of the data attached.

The goal is to create a formula that will be embedded in another sheet to determine if an individual has worked on their current ship for more than the period of time 365 days or more (working days) over the past 2 years.

Practice.xlsx

KPH

Hi

I think it might be an issue with the cross sheet references. I will start over and walk you through it.

As you have the duration in your data sheet we might as well use that and reduce the number of cross references we need. To use your exact data in this example I:

• imported your data sheet and called it Brian Lee Practise.
• set the two date columns as date type.

Then in a new sheet I can build the formula. Like this:

Start with an INDEX and MATCH to pull in the contract length for each sailor by matching the Individual column on the new sheet with the Primary Column on the data sheet.

The formula is

=INDEX(({Brian Lee Practise Contract Length}), MATCH(Individual@row, {Brian Lee Practise Assigned}, 0))

Where {Brian Lee Practise Contract Length} is a cross sheet reference created like this

(you can name yours something else, just make sure you select the same column)

And {Brian Lee Practise Assigned} is a cross sheet reference created like this (but smaller - I have no idea what is going on with these screen shots today!)

(again name it whatever you want, just make sure you select the same column and use it in the right place)

In the data sheet, you need a column called Individual and then a column with the formula, like this:

Now we can add the IF to the formula to change the numbers into Yes or No

=IF(INDEX(({Brian Lee Practise Contract Length}), MATCH(Individual@row, {Brian Lee Practise Assigned}, 0)) > 180, "Yes", "No")

You mention 180 days, 182 days, and also 365 days or more (working days). You can adjust that in the formula by changing "180" to another number. Or for working days, adjust the Duration column in the original sheet.

Does that help?

Brian Lee

@KPH are you available to meet via zoom for a quick walkthrough of this?

I would greatly appreciate it.

Brian Lee

The formula worked how can I incorporate another criteria to include a specific type of ship in the formula?

Brian Lee

@KPH

This formula is being read in Smartsheet but I need to include the criteria for specific ship.

=IF(INDEX(({Disposition Plan - DAYS WORKED}), MATCH(Individual@row, {Disposition Plan - ASSIGNED TO}, 0)) > 180, "Yes", "No")

KPH

Hi

Glad you got that formula working.

I think the next requirement is to be able to select the ship name as well as the sailor name and see if a sailor has worked on that specific ship. To include the ship as well we can use an INDEX COLLECT formula instead of INDEX MATCH. This is the syntax you need:

=INDEX(COLLECT({range to collect - the duration}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

You will need one more cross reference like this:

And your formula will look like this

=IF(INDEX(COLLECT({Brian Lee Practise Contract Length}, {Brian Lee Practise Assigned}, Individual@row, {Brian Lee Practise Ship}, Ship@row), 1) > 180, "Yes", "No")

This lets you choose the sailor name and ship name to see if they have or have not worked on the ship of that name for more than 180 days.

I suggest enclosing all of this in an IFERROR so that if the sailor has not worked on the ship at all, rather than returning an error, the formula returns "No".

=IFERROR(IF(INDEX(COLLECT({Brian Lee Practise Contract Length}, {Brian Lee Practise Assigned}, Individual@row, {Brian Lee Practise Ship}, Ship@row), 1) > 180, "Yes", "No"), "No")

This will result in something like this:

Brian Lee

@KPH thank you for the help the formula is working, But in the formula how would you reference a specific ship rather than the Ship@row?

=IFERROR(IF(INDEX(COLLECT({Disposition Plan - DAYS WORKED}, {Disposition Plan - ASSIGNED TO}, Individual@row, {Disposition Plan - SHIP CODE}, Ships@row), 1) > 180, "Yes", "No"), "No")

KPH

Hi @Brian Lee

You can change Ship@row to the value (in quotation marks). So rather than looking at the cell in the Ship column in the current row, the formula will always use whatever ship id is entered in the formula. This is just like using cell references in a formula in excel - you can replace the reference with a static value.

In your case, change

=IFERROR(IF(INDEX(COLLECT({Brian Lee Practise Contract Length}, {Brian Lee Practise Assigned}, Individual@row, {Brian Lee Practise Ship}, Ship@row), 1) > 180, "Yes", "No"), "No")

to

=IFERROR(IF(INDEX(COLLECT({Brian Lee Practise Contract Length}, {Brian Lee Practise Assigned}, Individual@row, {Brian Lee Practise Ship}, "AD"), 1) > 180, "Yes", "No"), "No")

Brian Lee

@KPH

So the formula is functioning however, it is not returning the correct information. So I did a SUMIFS formula to calculate for example one individual who worked on this ship and it is more than 180 days, however when I reference the information with the formula written it is outputting it as a "No" rather than a "Yes".

The individual worked a total of 363 days based on the data I have on AN ship but the formula referenced below is giving an answer of No.

=IFERROR(IF(INDEX(COLLECT({Disposition Plan - DAYS WORKED}, {Disposition Plan - ASSIGNED TO}, Individual@row, {Disposition Plan - SHIP CODE}, "AN"), 1) > 180, "Yes", "No"), "No")

Would you be able to jump on a quick call and I can show you what I mean.

KPH

Hi @Brian Lee

I can't join a call but if you can add screen shots to explain the issue I can have a look later, or maybe someone else can pick this up. It sounds like the data in your real sheet might not be the same as in the practice one. Your formula is simply finding the days worked in your first sheet for the individual named in the second sheet where they are on the ship that you specify. It returns a "Yes" if this value is >180 and a "No" if less or they are not listed. There is no SUM.