Counting Days between Accidents

[aussiegirlone]

Dear T. valko

I am sorry for not responding sooner as it just became too much for my
little mind to cope with changing all my formula’s to get the one I want most
to work HMMMM.
However I hope you are still willing to help me? If you are then let me
continue from where we left off.

These two formulas below are what you gave to me last night
The replacement of my original
=IF(OR(Sheet3!B18="A",Sheet3!B25="A",Sheet3!B32="A",Sheet3!B39="A",Sheet3!B46="A"),1,"")

=IF(COUNT(C3),B3-LOOKUP(1E+100,D$22,B$2:B2))

It certainly helped me quite a lot thus, making you to be a very good stress
reliever however there is still one minor problem with the second formula.
It certainly counts the number of days from the first input date and time to
the last input date and time. However, if there is an accident two days in a
row, it should show (0) instead it counts from the previous day only not
from the whole week, month or year. So I only get one day from the last
accident not 2,3,4,5,6, and if there were no accidents for a whole year it
should calculate last accident was 365 days ago.
I am getting exhausted from trying to get this formula so if you gave up on
me it would be quite forgivable, as my little mind can handle that, and I
would just like to say thank you for all your help and to everyone else that
got involved.
PS: I hope your having a good day?
aussiegirlone

B C D E
Date Time Number of Days Since
Accidents last Accident
1/1/2008 12:30 PM 0
2/1/2008 1:00 AM 1 1
3/1/2008 2:30 PM 0
4/1/2008 3:30 PM 0
5/1/2008 4:30 PM 0
6/1/2008 5:30 PM 0
7/1/2008 6:30 pm 1 1

 

[T. Valko]

Everyhing I've suggested works just fine in my test setup.

At this point I'm out of suggestions. If I could see the actual setup I'm
sure we can solve this. If you want to send me a sample file that is setup
*exactly* like your real file I'll get it working (or, send me a copy of the
real file). If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

 

[aussiegirlone]

I have not seen this sight before do I need to become a member and how do I
send you a copy of my file as I belive it is quite complex at the moment

 

[T. Valko]

Just send the file via email. I posted my "munged" email address. Here it is
again:

xl can help at comcast period net

Remove "can", remove all the spaces, change "at" to the "at" sign, change
period to a dot.

Also, if the file is bigger than 1mb, zip it (compress it).

 

[aussiegirlone]

Just send the file via email. I posted my "munged" email address. Here itis
again:

xl can help at comcast period net

Remove "can", remove all the spaces, change "at" to the "at" sign, change
period to a dot.

Also, if the file is bigger than 1mb, zip it (compress it).

--
Biff
Microsoft Excel MVP







- Show quoted text -

T.Valko
You had sent me an email in which I have replied using my personal
email address. However I do not know if you have recieved my Email
reply as I am waiting for a response from you. My apologies if you
didnt recieve my email, and my answer to your question below is ("Yes,
that is correct! ")

you wrote

"Ok, I have your file open in front of me.

On sheet7,

....If there is no accident to report for a date what result do you
want in the cell?

....If there are no previous accidents reported and one is reported for
"today" what result do you want in the cell? For example, sheet7
starts from Jan 1. If there isn't an accident reported until Jan 3
what result do want for Jan 3?

....If accidents occur on consecutive days and the days between
accidents for those cases is considered to be 0 then you also have use
that same logic for other accidents, right? For example, an accident
occurs Jan 1 and the next one occurs Jan 3 then the number of days
since the last accident is 1, right?

Let me know! "