Working with dates and time
 

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

Working with dates and time
 

=IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+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)

"Ben" wrote in message
...
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

Working with dates and time
 

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


"Bob Phillips" wrote:

=IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+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)

"Ben" wrote in message
...
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

Working with dates and time
 

You would do that with conditional formatting, FormatConditional
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)

"Ben" wrote in message
...
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


"Bob Phillips" wrote:

=IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+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)

"Ben" wrote in message
...
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

Working with dates and time
 

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)

"Ben" wrote in message
...
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


"Bob Phillips" wrote:

=IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+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)

"Ben" wrote in message
...
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