Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Time Between Dates, dependant on status of another column
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!
Comments
-
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?
-
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
-
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]))
-
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
-
Hi,
This may give you an idea?
=MAX(COLLECT([date reported]1:[date reported]3, [classification column]1:[classification column]3, ="Incident"))
-
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"
-
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
