% Time Elapsed Formula - Help breaking down and understanding long nested formula

AndyM

Hi,

I'd like help understanding the following formula that is being used in a sheet I inherited. I'd like to understand how to break down the formula to understand what exactly it is doing and how it is doing it.

The formula is calculating "% Time Elapsed" based on Duration, Start Date and End Date.

=IFERROR(IF(AND(Duration@row = 0, [End Date]@row <= TODAY(), ISDATE([End Date]@row)), 1, IF(AND(Duration@row = 0, [Start Date]@row > TODAY()), 0, IF(OR(AND([End Date]@row <= TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) > 1), 1, IF(OR(AND([End Date]@row > TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) < 0), 0, (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)))))), "")

I tried breaking it down by IF statements but I'm still not able to figure it out ;P

Thanks!

Andy

Genevieve P.

Hi @AndyM

Breaking it down by IF statements is exactly what I would do, too!

Lets space them out.

=IFERROR(

IF(AND(Duration@row = 0, [End Date]@row <= TODAY(), ISDATE([End Date]@row)), 1,

IF(AND(Duration@row = 0, [Start Date]@row > TODAY()), 0,

IF(OR(AND([End Date]@row <= TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) > 1), 1,

IF(OR(AND([End Date]@row > TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) < 0), 0, (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row))))))

, "")

First Statement

IF(AND(Duration@row = 0, [End Date]@row <= TODAY(), ISDATE([End Date]@row)), 1,

If:

• the Duration in this row is 0
• AND if the End Date is today or in the past,
• AND if the End Date is actually a date (so it's not blank),

then return 1 (100%).

Otherwise, if that combination isn't met (duration is greater than 0 or the end date is in the future) then:

Second Statement

IF(AND(Duration@row = 0, [Start Date]@row > TODAY()), 0,

If:

• Duration in this row is 0
• AND Start Date is in the future

then return 0 (it hasn't been started yet).

Otherwise, if neither of those above statements are met (duration is greater than 0, or the end date is in the future, or the start date is in the past) then:

Third Statement

IF(OR(

• AND([End Date]@row <= TODAY(), Duration@row = 0),
• (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) > 1),

1,

If EITHER:

• Duration is 0
• AND End Date is in the past (including if it's blank)

OR:

• The number of working days between the task Start Date and Today divided by the number of days (including non-working days) of the task is greater than 1

(For example, If the task was Friday and Today it's Tuesday, the working days would be 3. Then if the End Date is tomorrow, the actual days of the task would be 6 days. This would not meet the criteria. However if the task is in the past and has already been completed, then the workdays between the Start Date and Today would be much greater than the total task days, so this would meet the criteria).

Then return 1 (100%).

OTHERWISE....Now that you've eliminated the milestone tasks and tasks that are completely in the past, we get to the final IF statement.

Final Statement

IF(OR(

• AND([End Date]@row > TODAY(), Duration@row = 0),
• (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) < 0),

true: 0,

false: (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row))

If EITHER:

• The End Date is in the future
• AND the Duration is 0

OR:

• The number of working days between the task Start Date and Today divided by the number of days (including non-working days) of the task is less than 0

Return 0%

OTHERWISE!!!! If none of the above statements are correct, then here is the very final formula that will run:

NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)

Take the working dates between the Start of the Task and Today, and divide that by the total days of the task to get the percent.

Question:

Is this formula working for you how you'd like? I am intrigued to see that the math part of the formula is taking the Working Days until today, but not the Working Days of the task itself.

([End Date]@row - [Start Date]@row) will simply calculate the days between these two dates without taking working days into consideration.

Cheers,

Genevieve

Genevieve P.

Hi @AndyM

Breaking it down by IF statements is exactly what I would do, too!

Lets space them out.

=IFERROR(

IF(AND(Duration@row = 0, [End Date]@row <= TODAY(), ISDATE([End Date]@row)), 1,

IF(AND(Duration@row = 0, [Start Date]@row > TODAY()), 0,

IF(OR(AND([End Date]@row <= TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) > 1), 1,

IF(OR(AND([End Date]@row > TODAY(), Duration@row = 0), (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) < 0), 0, (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row))))))

, "")

First Statement

IF(AND(Duration@row = 0, [End Date]@row <= TODAY(), ISDATE([End Date]@row)), 1,

If:

• the Duration in this row is 0
• AND if the End Date is today or in the past,
• AND if the End Date is actually a date (so it's not blank),

then return 1 (100%).

Otherwise, if that combination isn't met (duration is greater than 0 or the end date is in the future) then:

Second Statement

IF(AND(Duration@row = 0, [Start Date]@row > TODAY()), 0,

If:

• Duration in this row is 0
• AND Start Date is in the future

then return 0 (it hasn't been started yet).

Otherwise, if neither of those above statements are met (duration is greater than 0, or the end date is in the future, or the start date is in the past) then:

Third Statement

IF(OR(

• AND([End Date]@row <= TODAY(), Duration@row = 0),
• (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) > 1),

1,

If EITHER:

• Duration is 0
• AND End Date is in the past (including if it's blank)

OR:

• The number of working days between the task Start Date and Today divided by the number of days (including non-working days) of the task is greater than 1

(For example, If the task was Friday and Today it's Tuesday, the working days would be 3. Then if the End Date is tomorrow, the actual days of the task would be 6 days. This would not meet the criteria. However if the task is in the past and has already been completed, then the workdays between the Start Date and Today would be much greater than the total task days, so this would meet the criteria).

Then return 1 (100%).

OTHERWISE....Now that you've eliminated the milestone tasks and tasks that are completely in the past, we get to the final IF statement.

Final Statement

IF(OR(

• AND([End Date]@row > TODAY(), Duration@row = 0),
• (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)) < 0),

true: 0,

false: (NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row))

If EITHER:

• The End Date is in the future
• AND the Duration is 0

OR:

• The number of working days between the task Start Date and Today divided by the number of days (including non-working days) of the task is less than 0

Return 0%

OTHERWISE!!!! If none of the above statements are correct, then here is the very final formula that will run:

NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row)

Take the working dates between the Start of the Task and Today, and divide that by the total days of the task to get the percent.

Question:

Is this formula working for you how you'd like? I am intrigued to see that the math part of the formula is taking the Working Days until today, but not the Working Days of the task itself.

([End Date]@row - [Start Date]@row) will simply calculate the days between these two dates without taking working days into consideration.

Cheers,

Genevieve

AndyM

Hi Genevieve,

Wow, thank you so much! It is quite an extensive formula and you did an amazing job breaking it down and explaining each piece! This will definitely help me be able to break down other formula and figure out what they are doing, and also create my own more complicated formula!

Thanks again for you help! 😃

Andy

AndyM

To your question, it's not working perfectly.

One issue is that if it's a 1d task (start and end dates are the same), when that day arrives the % Time Elapsed is set to 100% at the beginning of the day, which based on the health status formula, then puts the task in 'behind schedule' status. Here's the health status formula (I understand most of this one but the end is a little confusing for why it would be Gray or Green):

=IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), OR([% Complete]@row = 0, [% Complete]@row = "")), "Gray", IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), [% Complete]@row > 0), "Green", IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([% Time Elapsed]@row <> "", [% Complete]@row <> ""), IF([% Time Elapsed]@row - [% Complete]@row > 0.1, "Yellow"), IF([% Complete]@row = 0, "Gray", "Green")))))

Above formula is assuming there is (or should be) a close correlation between % Elapsed Time and % Completed such that if % Completed is not keeping up with % Elapsed Time it will turn the task Yellow (which is currently labelled as 'Behind Schedule'). I agree there is a relationship between the two but not to the extent it is marking a task yellow and considering it behind schedule. It also required very vigorous daily task updating, or even more than once/day to make task % Completed updates.

AndyM

@Genevieve P.

Regarding your statement about it not taking into account working days, such as in this section of the formula:

(NETWORKDAYS([Start Date]@row, TODAY()) / ([End Date]@row - [Start Date]@row))

I was trying to test this section of the formula by itself to see what values are returned, and verify if it's counting non-working days, but I can't seem to get it to work - maybe because it needs to be part of the larger formula? This section of the formula still doesn't make total sense to me in how it's being evaluated, and how it works by doing the division calculation where it's being done. When I try and walk through the full formula in Smartsheet by clicking on the various parts of the formula it doesn't show the part after the 'divide by' symbol to be part of the NETWORKDAYS, which is why I'm assuming you are saying it is not calculating working days but total actual days?

If it isn't calculating working days for both sides of the equation, how would I enable it to calculate working days for both?

AndyM

@Genevieve P.

PS I did manage to get the formula working. The version I had pasted in the last post was not the one I was testing, as I know that wouldn't work, but whatever I had been testing wasn't working at that time.

The below section works and I've adjusted it (I believe ;-) ) to use working days for the second part of the calculation.

=NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)

Genevieve P.

Hi @AndyM

I'm glad you were able to adjust it as needed!

Yes, exactly. I was wondering if you were looking to divide by the total working days of the task, instead of the total days.

([End Date]@row - [Start Date]@row)

isn't within a function at all, so the formula helper window can't guide you through this. You're simply subtracting one date from another to see the number of days between the two dates.

Wrapping that portion in the NETWORKDAYS, as done with the Start & Today, is exactly what I was going to suggest. Now that you've done this, does the Status Ball symbol formula work as it should?

AndyM

Hi @Genevieve P. ,

Thanks for the reply. I heard from someone in the team that they had chosen to 'not' use working days when it was first setup (with Smartsheets prof services) but they couldn't remember why they did it that way!-) lol Without working days, it means the result is smaller, which will give you more wiggle room before the task turns yellow.

For the other related formula for Health Status, I added another couple of edits so that all 100% completed tasks are marked Green, and anything that calculates up to 0.1 (when the status turns yellow) will be marked as Green. There's probably a way to combine all the options for Green into one statement but for now I just added a couple of additional conditions separately.

Here's the new formula:

=IFERROR(IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), OR([% Complete]@row = 0, [% Complete]@row = "")), "Gray", IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), [% Complete]@row > 0), "Green", IF(OR([% Complete]@row = 1, [% Time Elapsed]@row - [% Complete]@row < 0.1), "Green", IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([% Time Elapsed]@row <> "", [% Complete]@row <> ""), IF([% Time Elapsed]@row - [% Complete]@row > 0.1, "Yellow"), IF([% Complete]@row = 0, "Gray", "Green")))))), "Formula Error")

Prior to adding in the following the update, the status was being marked as blank under certain conditions

IF(OR([% Complete]@row = 1, [% Time Elapsed]@row - [% Complete]@row < 0.1), "Green"

I'm still not sure why it was being marked blank, as there is nothing in the equation that will apply and mark it as blank. It seems to happen when the above calculation result is less than 0.1, and so now with the link above added it turns green instead of blank.

Genevieve P.

Hi @AndyM

"Blank" is the default false statement of an IF statement - this means that if you're seeing blank, that specific row doesn't meet any of your defined conditions.

It sounds like you've sorted this out, now! 🙂

AndyM

Hi @Genevieve P. ,

Oh ok, I thought the way the formula works is that if none of the conditions are met then the final value for 'if false' would apply to any other condition that wasn't met and therefore would evaluate to 'Green'? The final If statement is:

 IF([% Complete]@row = 0, "Gray", "Green")

It was my understanding that Green would apply if not just this one condition was not met but if none of the previous conditions were also not met? From what you are saying, the final statement above would only be Green if none of the other formula conditions were true, AND the % Complete = 0 ?

And if none of the conditions are met then it will be evaluate to blank. Hmmm, ok, that wasn't how I was reading it before so good to know!-)

Is there a way to set a default value that is not blank if none of the conditions you define are true? So in my example let's say if none of the conditions are met then the result should be 'Gray'?

Genevieve P.

Hi @AndyM

You are correct in your understanding - that the "Green" should apply not only if this one IF statement is false but also if it doesn't find a match with any of the previous IF statements. However, the way that you have closed off a couple of your other IF statements allows room for a blank result:

=IFERROR(

IF(AND(OR(), OR(")), true - "Gray",

false - IF(AND(OR(), ), true - "Green",

false - IF(OR(), true - "Green",

false - IF(AND(), true - "Red",

false - IF(AND(),

TRUE: IF([% Time Elapsed]@row - [% Complete]@row > 0.1, true - "Yellow"), < here is where it will turn blank, because you have closed off this specific IF statement before going on to the next IF. This means that IF it's less than 0.1, it will return blank, as you have no "False" statement specified for this specific IF.

FALSE: IF([% Complete]@row = 0, true - "Gray", false - "Green")

))))), "Formula Error")

Does that make sense?

AndyM

Hi @Genevieve P.

Thanks for the extra info! I didn't realize the last bracket after the 'Yellow' condition check meant that it had no false statement. I thought it was just continuing on and evaluating with the next check.

It was confusing me why I was at times seeing the result as blank, and ended up solving it by adding another statement earlier on in the formula to catch the conditions I thought were causing it to be blank (namely catching anything that calculated less that 0.1). For the Yellow logical expression check I also added an equals sign and made it '>=' instead of just '>'.

Here's the full formula as I have it now for anyone insterested ;)

=IFERROR(IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), OR([% Complete]@row = 0, [% Complete]@row = "")), "Gray", IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), [% Complete]@row > 0), "Green", IF(OR([% Complete]@row = 1, [% Time Elapsed]@row - [% Complete]@row < 0.1), "Green", IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([% Time Elapsed]@row <> "", [% Complete]@row <> ""), IF([% Time Elapsed]@row - [% Complete]@row >= 0.1, "Yellow", IF([% Complete]@row = 0, "Gray", "Green"))))))), "Formula Error")

k.platt

Looking to modify this a bit but need guidance.

In addition to what is happening in @AndyM formula above, I would like the following to occur:

If complete, then it should be blank. No status dot.

If not started, but it should be, the status dot should be red.

I have tried to tinker a bit but I have not had luck.

AndyM

@k.platt

Hi there,

Great to see others interested in this solution!-)

Well I 'thought' I had this formula thing dialed in, but when trying to add in your changes I keep running into various problems. I am seeing a message that the formula 'isn't quite right', so maybe a bracket in the wrong place or missing, and now i see #INCORRECT ARGUMENT SET. So getting the new changes to work is proving more challenging than when I made the original changes. ;-)

So for now I'll just add in the statements themselves that I think will solve what you need:

## If complete, then it should be blank. No status dot.

IF([% Complete]@row = 1, "",

## If not started, but it should be, the status dot should be red.

If whether is 'should' be started is purely based on Start Date, a way you could do it is (you should be able to copy/paste to test the formula below by itself to see how it works and play around with it):

=IF(AND(ISDATE([Start Date]@row), [Start Date]@row <= TODAY(), [% Complete]@row > 0), "Red", "")

* this will check if the start date is a date, AND if start date is less than or equal to Today, AND if % completed is still zero. If it doesn't match for all Red conditions, it sets it to blank. If I left out the false condition then it will set to blank by default so I didn't 'have' to add it in, just makes it more explicit and visible that is happening.

One caveat, if it's a 1d task, then really this is saying it's Past Due at this point. So yes it is checking that it should have been started, but if it's a 1d task then it's overdue as well as not started if start date is less than today.

For 1d tasks, if the start date and today are the same, then it's not yet overdue and so it's fair to flag it, as % Completed has not been updated to something greater than 1%. But in this case I would use yellow instead of red, as it may be started and finished as expected same day so not quite a Red alarm yet.

It may be easier to add a column with a checkbox that is checked (manually) if the task is started.

I just thought of another way to do this, looking at it from another angle. So if the statement evaluates to false it is set to Red, otherwise set to blank.

=IF(AND(ISDATE([Start Date]@row), [Start Date]@row <= TODAY(), [% Complete]@row > 0), "", "Red")

Any other better suggestions are welcome! I'm still learning ;-)

AndyM

@k.platt

oops, in my first formula for 'if not started', I didn't revert it back correctly when I was playing around with it to create the second version. It should be:

=IF(AND(ISDATE([Start Date]@row), [Start Date]@row <= TODAY(), [% Complete]@row < 0.01), "Red", "")

I was thinking I needed to add a check in to see if it's % Completed is blank and set it to red in that is true and start date is today or in the past. But it seems to still set it to red if it's zero or blank but sets to blank if it's 1% or more.

AndyM

@k.platt

Ok, I think I just need to change my editing process to make changes to the formula. What seems to work is using a text editor, putting each IF statement on it's own line, then making the changes making sure to add/remove braces at the end of the formula when adding/removing IF statements. Then when I copy/paste it into the column formula, I go to the end of each IF statement and press delete once to pull the next IF statement in the right place. Once I've done that for all of them I click outside the formula, and save the sheet.

Here's one I 'think' has the changes you want:

=IFERROR(IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), OR([% Complete]@row = 0, [% Complete]@row = "")), "Gray", IF(AND(OR([% Time Elapsed]@row = 0, [% Time Elapsed]@row = ""), [% Complete]@row > 0), "Green", IF([% Time Elapsed]@row - [% Complete]@row < 0.1, "Green", IF([% Complete]@row = 1, "", IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(), [% Complete]@row < 1), "VAR1", IF(AND(ISDATE([Start Date]@row), [Start Date]@row <= TODAY(), [% Complete]@row < 0.01), "VAR2", IF(AND([% Time Elapsed]@row <> "", [% Complete]@row <> "", ([% Time Elapsed]@row - [% Complete]@row >= 0.1)), "Yellow", IF([% Complete]@row = 0, "Gray", "Green")))))))), "Formula Error")

I haven't done a lot of testing but seems like it's working as expected.