Working with dates and time

B

Ben

Hi,

Is it possible for a formula to calculate the diffence between seperate
cells.For example can A&B & D&E be read as one.The answer i'm looking for in
F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read
EARLY BY and enter time

Expected Actual
Answer
A B D E F
05/03/2006 12:00 06/03/2006 14:00
 
B

Bob Phillips

=IF(A1+B1=C1+D1,"On Time",IF(A1+B1>C1+D1,"Early by "&TEXT(A1+B1-(C1+D1),"d
""days ""hh:mm"),"Late by "&TEXT(C1+D1-(A1+B1),"d ""days ""hh:mm")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
B

Ben

Thanks Bob
the formula works great,is it possible for when an answer is displayed the
text colour can change for example

Late - would be red
Early - would be blue
On-time - would be green

Thanks
Ben
 
B

Bob Phillips

You would do that with conditional formatting, Format>Conditional
Formatting.

Set the condition to equal, and the value to the text, and format using a
colour from the Palette tab.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Just realised, that won't work. You do use CF, but change Condition 1 to
Formula Is, and use a formula of

=LEFT(E1,4)="Early"

assuming that the formulae start in E1.

Then add other conditions with formulae of

=LEFT(E1,5)="Early"

and

=LEFT(E1,7)="On Time"

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top