Formula for Actual duration excluding duplication

2»

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • 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
    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
    edited 11/27/19

    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
    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.

  • 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
    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.

  • 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!

  • Hi Paul,

     

    hope you are well.

     

    Have you found my amendments to the sheet I shared?

    Were the columns created correctly?

     

    Thanks

  • Paul Newcome
    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.

  • 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

  • Hi Paul,

    do you have any update on the formula?


    Thanks!

  • Cathy Byrne
    Cathy Byrne ✭✭✭

    Hi Paul

    Did you find an answer to this one?

    Thanks

    Cathy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!