Days Since Formula

[MJS]

I'm looking for a formula to calculate the number of days (from today’s date)
since an accident has occurred. The setup i have:

Column A Column AD
April Accident
01/04/09 0
02/04/09 0
03/04/09 1
04/04/09 0
Etc....

Therefore if the last accident happened on the 03/04/09 (represented by the
1 above), and today’s date is 13/04/09, the answer would be 10 days.

Hopefully this is clear enough and someone can help.

Thanks,

 

[Bob Greenblatt]

I'm looking for a formula to calculate the number of days (from today¹s date)
since an accident has occurred. The setup i have:

Column A Column AD
April Accident
01/04/09 0
02/04/09 0
03/04/09 1
04/04/09 0
Etc....

Therefore if the last accident happened on the 03/04/09 (represented by the
1 above), and today¹s date is 13/04/09, the answer would be 10 days.

Hopefully this is clear enough and someone can help.

Thanks,

Just subtract the 2 values 13/04/09 - 03/04/09 will give results of 10, just
what you want.

 

[Laroche J]

Bob Greenblatt wrote on 2009-04-13 14:39:

Just subtract the 2 values 13/04/09 - 03/04/09 will give results of 10, just
what you want.

I sense that MJS wanted a formula that would automatically find how many
days passed since the last accident. Use
=TODAY()-MAX(A2:A32*SIGN(AD2:AD32))
with your own ranges replacing A2-A32 and AD2:AD32. After typing the
formula, finish up with command-enter to make the formula an array formula
around which curly braces will appear. Each time you edit the formula you
have to finish with command-enter.

You should probably add an IF statement somewhere to take care of the case
when an accident has never occurred in the date range.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[MJS]

Works perfectly. Thanks for your help.

Bob Greenblatt wrote on 2009-04-13 14:39:


I sense that MJS wanted a formula that would automatically find how many
days passed since the last accident. Use
=TODAY()-MAX(A2:A32*SIGN(AD2:AD32))
with your own ranges replacing A2-A32 and AD2:AD32. After typing the
formula, finish up with command-enter to make the formula an array formula
around which curly braces will appear. Each time you edit the formula you
have to finish with command-enter.

You should probably add an IF statement somewhere to take care of the case
when an accident has never occurred in the date range.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial