Number of NetDays since last event

Steve Moss

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

Find more posts tagged with

Formulas

Reports

Account and User Management

Comments

Mike Wilday

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.

Mike Wilday

Hi Steve, did this solution work for you?

Steve Moss

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

Mike Wilday

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

Steve Moss

Thanks Mike,

That work perfectly.

Kind Regards

Steve

Mike Wilday

Awesome. Glad I could help you out.

Sign in to join the conversation!

Quick Links

Trending discussions

Compare Previous Week
Hello, I am trying to be able to say whether hours went up or down as compared to the previous week but am having trouble referencing the previous week. I have tried to add a helper column but am still getting an error. Any help would be greatly appreciated!
SUMIFS Formula with Date Column
Hello All, I am using this formula " =SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, ">=" + TODAY(), {RCX- Kit Forecast - 2025 Range 2}, "<=" + (TODAY() + 5)) " Range 1 is the number column Range 2 is a date column But the value is coming as zero
Workflow to trigger comms when number exceeded but not in between.
Hi, trying to work out how I can get a workflow to trigger only when certain milestone numbers are met. For example, I'd like an email comm to be sent once the sum of a column reaches 50, 100, 150, 200 etc. The number will be entered via a form, very basic, so for example 4 entries could be sent via 4 form inputs (15, 11,…