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

=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)

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:7E5A6359-6981-47AC-8F1F-E35081BD442F@microsoft.com...
> 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
>

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+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)
>
> "Ben" <Ben@discussions.microsoft.com> wrote in message
> news:7E5A6359-6981-47AC-8F1F-E35081BD442F@microsoft.com...
> > 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
> >
>
>
>

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)

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:7BFD1CB2-7B09-42FA-B3F1-2DFA6C9E0A6A@microsoft.com...
> 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+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)
> >
> > "Ben" <Ben@discussions.microsoft.com> wrote in message
> > news:7E5A6359-6981-47AC-8F1F-E35081BD442F@microsoft.com...
> > > 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
> > >
> >
> >
> >

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" <Ben@discussions.microsoft.com> wrote in message
news:7BFD1CB2-7B09-42FA-B3F1-2DFA6C9E0A6A@microsoft.com...
> 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+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)
> >
> > "Ben" <Ben@discussions.microsoft.com> wrote in message
> > news:7E5A6359-6981-47AC-8F1F-E35081BD442F@microsoft.com...
> > > 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
> > >
> >
> >
> >