Number of NetDays since last event

Steve Moss
edited 12/09/19 in Formulas and Functions

Hi,

Looking to calculate the number of netdays since an event was recorded.

I have a {Date of Incident} column and a {Classification} column

Looking for the number of days since a specific classification was reported i.e. {Classification}, ="Lost Time Injury".

Want to be able to report this as a Key Indicator of No. Days since Last LTI =  ?

Regards

Steve

Tags:
«1

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this one...

     

    =IF(Classification@row = "Lost Time Injury", TODAY() - [Date of Incident]@row, "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I get #uparseable.

     

    Also, what do you mean by @row.

     

    I should have mentioned that This is a cross sheet reference I am looking for.

    I am looking to put the result on a dashboard.

     

    Regards

     

    Steve

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Steve,

    How did it go? Did you manage to get something set up?

    Best,

    Andrée Starå - Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. X-sheet referencing changes it up a bit. The sheet you are pulling from, I will call "Source Sheet", and the sheet you are putting the formula in , I will call "Master Sheet". You will need to use x-sheet referencing appropriately for this to work.

     

    Source sheet columns used:

    Classification

    Date of Incident

     

    Master Sheet columns used:

    Classification

    Net Days Since

     

    Use the following formula in the [Net Days Since] column on your master sheet.

     

    =NETDAYS(INDEX({Source Sheet Range 1}, MATCH(Classification@row, {Source Sheet Range 2}, 0)), TODAY())

     

    Here's the breakdown and how to tweak it so it works for you...

     

    When you get to the part where you need to enter {Source Sheet Range 1}, use the link in the formula helper box the "reference another sheet". Select your source sheet, then select the [Date of Incident] column.

     

    On your master sheet, I assume you will have a column that has listed out the different classifications. That is the Classification column listed under the Master sheet columns used. Classification@row is specifying for the formula to look for whatever data is in the Classification column in whatever row the formula is on. So if you use the exact same formula in multiple rows, each with different classifications, the formula will automatically update itself to reflect whichever classification is on that row without you having to update the formula with each classification.

     

    To get {Source Sheet Range 2}, follow the same steps to reference your source sheet, then select the Classification column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks for that.

    What I need is for the number of days since the last event to calculate on the Source Sheet.

    I don't want the Net Days Since  Column in the Master Sheet as it's a Register. The calculataion needs to sit on another page i.e. "Source Page"

    Basically I will have the Source Sheet 2 columns [Net Days Since] and [Classification]. The Classification column on the Source Sheet will have for example:

    Row 1 - Lost Time Injury

    Row 2 - Medical Treatment Injury

    Row 3 - First Aid Injury

    The Net Days Since Column will have the days since e.g. a Lost Time Injury event took place from today.

     

    Or,

    Could I put another column in [Net Days Since] and hide it from view?

    Kind regards

     

    Steve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure exactly what you're looking for at this point. Net days or no? Cross sheet or no? By cross sheet I mean formula sits on sheet A and pulls info from sheet B. Pulling data for a dashboard isn't necessarily a cross sheet reference. Where exactly is the data being pulled from? Where exactly is the data being displayed?

     

    If you could provide some screenshots of how you want this laid out, that would be great. Just enter some examples and indicate where you want what to go.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul,

    Actually it's worked just fine as it is.

    Thanks a great deal

     

    Steve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Happy to help.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I have another problem now.

    It won't update unless I open the sheet with the formula in it.

    And strangely enough.

    The last row only. FAI's.

    The top two rows, LTI's and MTI's with the same formula works just fine.

    Any idea's?

    Regards

     

    Steve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When using the TODAY() function in a formula, it will only update when the sheet is opened. Unfortunately there is no way around it that I have found. I wish there was because I have more than a few sheets that I open every single day for ONLY that reason. Everything else is automated.

     

    As far as working on some and not on others... I'd have to see the sheet(s) to see if maybe there is some slight difference somewhere whether it be in format or whatnot.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 11/05/18

    Hi Paul,

    A tip.

    Link from the sheet or sheets that you use the most to those that should update.

    I hope this helps you!

    Have a fantastic week!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Paul,

    Yes I find the same is a bit of an annoyance indeed.

  • The developers need to do a bit of work on the application to do what excel can do if this is to be better than excel.

    Excel has a setting to automatically recalculate the sheet even if it contains TODAY()

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Steve,

    I'm confident that the team is working on improving it.

    Please submit an Enhancement Request when you have a moment.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you mean to put the TODAY() function on one of my regularly used sheets and simply create a cell link to the not so regularly used sheet, or...?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!