Formula for Actual duration excluding duplication

Paul Newcome

Did you pull the updated MIN and MAX formulas mentioned above that I posted in reference to your initial duplication concern?

Alessandro Terranova

Yes, sure thing, I replicated and validated the entire formula structure, including the new MIN and MAX. For the purpose of testing in further detail I have deployed the formula model across 9 different backlogs (carefully copying and pasting from original sheet), I am experiencing duplication issues on two backlogs only, the other seven are returning correct dates and correct network days output. The two backlogs that produce incorrect output are the most complex ones in terms of connected phases, although the template is identical in all its components. 

Paul Newcome

Hmm...

 

There must be an overlap scenario we haven't thought of yet. If you are able to identify the rows that are not only being duplicated but are LINKED to the duplicated rows through some sort of overlap, try copying them to another sheet (just the dates and helper columns for this). Once you have that done, maybe share me to the new sheet so I can get in and take a look. I am unable to reproduce duplicates, so I am not sure where to start on figuring out a solution.

Alessandro Terranova

Hi Paul,

I would be happy to share a selected portion of the sheet, would you recommend the "publish" function? To preserve formulas I might use the "Edit by anyone" option.

Many thanks

Paul Newcome

HERE is some information on Publishing Sheets.

 

For the testing sheets, we would only need the Primary column which can contain any data you want to put into it (I usually just enter the row number or some random letter), then the date columns and the helper columns. If you want to "Save as new" one of the sheets you are having issues with, then delete the extra data/remove sensitive data so that it is strictly what we need to figure out this particular solution, that would work just fine.

 

The edit by anyone option would allow me to edit the formulas themselves so that I can do some testing and whatnot, so that would be preferable.

Alessandro Terranova

Hi Paul,

Thank you for your confirmation, I have published the sheet selecting the edit by anyone option.

Please find it Here

I filtered the sheet (Production AS is not blank) to keep only relevant rows but the publish function seems not to preserve filter settings.

I have highlighted in different colours where I verified a conflict, I also highlighted some cells in the Max column when the Max seems not to be pulling the correct date. 

Please let me know if any question or something is not clear.

Many thanks

 

Paul Newcome

I have found a few things I would like to test. Would you please add in a few blank columns? 4 text/number and 2 date types should be enough. That way I can compare things side by side without overwriting anything already on there.

Alessandro Terranova

All columns have been set up as requested, Paul. Please let me know if any further action on my end may facilitate your testing.

I am really grateful for your kind help. Thank you and Happy Friday!

Alessandro Terranova

Hi Paul,

 

hope you are well.

 

Have you found my amendments to the sheet I shared?

Were the columns created correctly?

 

Thanks

Paul Newcome

I haven't had a chance to work on it the past few days. Did you see my note I left in a blue cell in your published sheet?

 

I ended up creating my own copy duplicating your dates because I wanted to try a few things with additional date type columns and whatnot.

 

I am still working on it when I have time, but unfortunately I can't quite get it just right. I keep getting very close, but not quite solved yet.

Alessandro Terranova

Just found your note in blue and your comment. Smartsheet didn't notify your kind response on my inbox for some reason.

Many thanks for looking into this, hope the solution will come your way soon.

I will check again the community once back from Christmas leave.

I wish you a marry and joyful holiday time.

Thank you gain for your kind help.

Best,

Alessandro

Alessandro Terranova

Hi Paul,

do you have any update on the formula?

Thanks!

Cathy Byrne

Hi Paul

Did you find an answer to this one?

Thanks

Cathy