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

Paul Newcome

Try this one...

 

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

Steve Moss

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å

Hi Steve,

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

Best,

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

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.

Steve Moss

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

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.

Steve Moss

Hi Paul,

Actually it's worked just fine as it is.

Thanks a great deal

 

Steve

Paul Newcome

Excellent! Happy to help.

Steve Moss

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

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.

Andrée Starå

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

Steve Moss

Hi Paul,

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

Steve Moss

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å

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

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