Days since last accident

[uwfenske]

I need some help on calculating days since last accident when our driver is at fault. Here is what I am working with.

Date At Fault Department
1/1/10 Driver Bus
1/2/10 Other Van 2
1/2/10 Driver Van 1
1/4/10 Driver Car
1/4/10 Driver Van 2
1/5/10 Other Bus

I want to be able to show how many days since the accident by "Department" when the "At Fault" column is "Driver".

I thought is would have to be a CSA formula and using some sort of DATEDIF function, but cannot figure it out. Any thoughts?

 

[Jonmo1]

Welcome to the board...

Try this, provided the data is sorted ascending on the dates..

Column A is dates
Column B is Fault
Column C is Department

=TODAY()-LOOKUP(2,1/((B1:B1000="Driver")*(C1:C1000="Department")),A1:A1000)

 

[Matty]

Welcome to the Board,

An alternative to jonmo1's suggestion:

Excel Workbook
	A	B	C	D	E	F
1	Date	At Fault	Department		Department	Days Since Most Recent Driver Accident
2	01/01/2010	Driver	Bus		Bus	45
3	02/01/2010	Other	Van 2		Van 1	44
4	02/01/2010	Driver	Van 1		Van 2	42
5	04/01/2010	Driver	Car		Car	42
6	04/01/2010	Driver	Van 2
7	05/01/2010	Other	Bus
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
The dates on the Worksheet are UK formatted, but the underlying numbers are the same.

Does this help?

Matty

 

[uwfenske]

Helps tremendously. It is working just as I wanted it to.
Thank you both for the quick reply.

 

[stevewhyles]

I have a very similar problem, abeit I have a table with the sites in the columns and the incidents in the rows one of the rows is "Lost Time Accident" they are totalled at the end of that row, I have a formula that will reset the count to zero if an entry is made in the Lost Time accident row but I cannot figure out how to continue the count from the following date without deleting the Lost time incident. The safe days number "204" is worked out by subtracting today from the 1st of January but will zero if an entry is made in B2 if that makes sense, I just need to know either a code or formula that will restart the count again even with the entry in B2 unless another entry is added

A	B	C
Lost Time Accident	0	0
All Incidents	25
Highest Incident Type	Allergies
Site With Highest Incident rate	Ashgate Manor
Safe Days	204	204

 

[Joe4]

I have a very similar problem, abeit I have a table with the sites in the columns and the incidents in the rows one of the rows is "Lost Time Accident" they are totalled at the end of that row, I have a formula that will reset the count to zero if an entry is made in the Lost Time accident row but I cannot figure out how to continue the count from the following date without deleting the Lost time incident. The safe days number "204" is worked out by subtracting today from the 1st of January but will zero if an entry is made in B2 if that makes sense, I just need to know either a code or formula that will restart the count again even with the entry in B2 unless another entry is added

A	B	C
Lost Time Accident	0	0
All Incidents	25
Highest Incident Type	Allergies
Site With Highest Incident rate	Ashgate Manor
Safe Days	204	204

I see that you already posted a version of this question here: Reset cell to count from zero the next day
As per forum rules, please do not post the same question multiple times (rule 12 here: Message Board Rules).
Posts of a duplicate nature will typically be locked or deleted.

Please continue on in your original thread.