Help With New Forumla

[jladika]

I HAD POSTED THIS PROBLEM

IF I SEND A STUDY ON 4/1/2004 @ 8:00A
AND THEN SEND THE PAPER WORK ON 4/2/2004 @ 700A
AND THEN REC. MY REPORT ON 4/4/04 @ 800P

I AM LOOKING FOR A FORUMLA TO TELL ME WHAT THE TIME DELAY BETWEEN EACH
OF THE ABOVE WOULD BE.

I WAS GIVEN THIS FORUMLA

=INT(B1)-INT(A1)-(MOD(A1,1))&"DAYS"&
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm")

I dont know how to set this up!!
can someone please walk me through this?
what part of this forumla goes in what cell?
thanks joe
(e-mail address removed)

how can i reply back to you.
do i have to post a new thread?

 

[Debra Dalgleish]

The formula will work if you have dates and times in cells A1:C1 --

A1 B1 C1
4/1/2004 8:00:00 AM 4/2/2004 7:00:00 AM 4/4/2004 8:00:00 PM

In cell D1 (or any cell on the worksheet), enter the formula that Frank
posted:

=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1)) & " days " &
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm")

The entire formula goes in one cell. If you copy and paste the formula
from Frank's message, it may appear on two lines, but it should still
work correctly. It will calculate the delay between cells A1 and B1.

 

[Tony Jollans]

Hi jladika,

Given dates and times in the cells, as per Debra's post, the formula
will give you the difference between them. It is, however, rather
complicated and the same result can be achieved with this ..

=INT(B1-A1) & " Days " & TEXT(MOD(B1-A1,1),"hh:mm")

It's worth noting that both formulae give (the same) slightly odd
results if A1 is later than B1 (and the result negative). If that is
likely to happen then an adjustment will be needed.

Enjoy,
Tony