Number of NetDays since last event

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

Try something like this: =NETDAYS(MAX(COLLECT([Date of Incident]:[Date of Incident], Classification:Classification, ="Lost Time Injury")), TODAY())

See Screen shot to view results.

• ✭✭✭✭✭✭
Options

Hi Steve, did this solution work for you?

• Options

Hi there,

No not exactly.

I am trying to reference on a dashboard the number of days since an event took place. The register is closed data so I don't want to add anything there.

Basically will need to use Cross Sheet References to calculate the number of days since an event took place from today.

In a data sheet for the dashboard will just have 2 columns

Col 1 - Net Days Since

Col 2 - Classification

In Col 2 -  I will have i.e.

Row 1 - Lost Time Injury

Row 2 - Medical Treatment Injury

Row 3 - First Aid Injury

In Col 1 against each row will be the result of the calculation of the number of Net Days since an event of  the Classification i.e. "Lost Time Injury" took place.

I only need I feel those 2 Columns of Data in the Master Sheet to work this out. i.e. [Date of Incident], and [Classification].

This figure will load onto a Dashboard

Kind Regards

Steve

• ✭✭✭✭✭✭
Options

I think we can still get this to work for you as you'd like it too... try this...

Same Sheet Formula

=NETDAYS(MAX(COLLECT([Date of Incident]:[Date of Incident], Classification:Classification, ="Lost Time Injury")), TODAY())

Cross Reference Formula | Add this to column1 of the rollup sheet. You're going the bold part references the same sheet and whatever classification is located next to the formula. Delete the parts in the {brackects} one at at time and recreate the cross-sheet reference.

=NETDAYS(MAX(COLLECT({Cross sheet Date of Incident Reference}, {Cross Sheet reference of Classification}, =Classification@row)), Today())

You just need to create those cross-references... watch my screencast to find out how!

https://www.screencast.com/t/Z6rNCuguNXaF

• Options

Thanks Mike,

That work perfectly.

Kind Regards

Steve

• ✭✭✭✭✭✭
Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!