Time Between Dates, dependant on status of another column

RSanders

Hi, 

Hopefully I can get this to make sense. 

We have a spreadsheet that has a Date Reported Column and classification column (classifications being - near miss, incident, injury) with a formula column. 

Is there a way to create a formula in the formula column that has the number of days between today and the latest date in the Date Reported Column, with the classification column being near miss or incident. 

I am trying to show the number of days since the last near miss / incident. Then i would do another formula to show the number of days since an injury occurred

Thanks so much!

ricki

near miss based on what? is there a staff id? a client id? for example, is this a list of all incidents and you are looking for the most recent date that a specific person was involved in the near miss where that person can have many rows under his id? or are you looking for one overall number for the entire spreadsheet - ie the last near miss across the entire company?

RSanders

hi, 

looking for one overall number for the entire company. 

We basically have an accident / incident / injury report that any employee completes as required. We then need to report on the number on days without incident, injury or accident. so it doesn't matter who reported it, just the last date one was reported and the days between that and the current day. 

thanks

ricki

I used the due date field in my sheet but you should be able to do the following with any date field

=TODAY() - DATEONLY(MAX([Due Date]:[Due Date]))

 

 

RSanders

Hi that works, but just pulls the latest date from the date column, without taking into consideration what the classification is in the classification column. 

I was thinking something like =if(classification:classification, "Accident", MAX(date:date)) to get the latest date for an accident. 

Then using your formula to reference the answer from the above formula to give me the days without an accident. 

However, the formula with the if either comes up with invalid or unparsable. 

is there anyway to do this? 

 

Thanks

Mehmet Zengin

Hi,

This may give you an idea?

=MAX(COLLECT([date reported]1:[date reported]3, [classification column]1:[classification column]3, ="Incident"))

Mike Wilday

To get the number of days, using the Max(Collect formula that Mehmet gave you just add the following, 

=TODAY() - MAX(COLLECT([Column3]1:[Column3]3, [Primary Column]1:[Primary Column]3, ="Incident"))

The result will be the number of days difference if you want to add text to the answer you could Modify as such:

=TODAY() - MAX(COLLECT([Column3]1:[Column3]3, [Primary Column]1:[Primary Column]3, ="Incident")) + " Day(s) without Incident"

ricki

You could add a new date column with a formula like

 

=IF([Classification]2 = "Accident", [Due Date]2)

This new column would just fill in for the relevant classifications. You could then use the max formula mentioned above on this new column to get the max of the relevant classifications