Formals with IF and Then

[Cyrus]

I need help with a formula. I'm creating a spreadsheet where data entry
would indicate the time a patient arrives and than leaves. I need a formula
that calculates this, but displays nothing if no time is entered (for example
down the worksheet).

=IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")

This is what I have...I keep getting value error.

i want the time to display if the time is greater than zero, but if less
than zero, display nothing.

Thanks,

Cyrus

[Bob Phillips]

Maybe

=IF(OR(M2="",L2=""),"",IF(M2-L2>0,M2-L2,""))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" <Cyrus@discussions.microsoft.com> wrote in message
news:32F8E5D1-0BE5-42EB-B3AA-21DE9E6FA37F@microsoft.com...
> I need help with a formula. I'm creating a spreadsheet where data entry
> would indicate the time a patient arrives and than leaves. I need a
formula
> that calculates this, but displays nothing if no time is entered (for
example
> down the worksheet).
>
> =IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")
>
> This is what I have...I keep getting value error.
>
> i want the time to display if the time is greater than zero, but if less
> than zero, display nothing.
>
> Thanks,
>
> Cyrus

[CLR]

=TEXT(IF(M2-L2>0,M2-L2,""),"h:mm")

Vaya con Dios,
Chuck, CABGx3



"Cyrus" wrote:

> I need help with a formula. I'm creating a spreadsheet where data entry
> would indicate the time a patient arrives and than leaves. I need a formula
> that calculates this, but displays nothing if no time is entered (for example
> down the worksheet).
>
> =IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")
>
> This is what I have...I keep getting value error.
>
> i want the time to display if the time is greater than zero, but if less
> than zero, display nothing.
>
> Thanks,
>
> Cyrus

[Sloth]

=TEXT(MAX(M2-L2,0),"h:mm;;")


"Cyrus" wrote:

> I need help with a formula. I'm creating a spreadsheet where data entry
> would indicate the time a patient arrives and than leaves. I need a formula
> that calculates this, but displays nothing if no time is entered (for example
> down the worksheet).
>
> =IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")
>
> This is what I have...I keep getting value error.
>
> i want the time to display if the time is greater than zero, but if less
> than zero, display nothing.
>
> Thanks,
>
> Cyrus

[Tim C]

Cyrus,

Try:

=IF(M2>L2,M2-L2,"")

With that formula, you will get a value if you have a departure time but no
arrival time. If that is a potential problem, use:

=IF((M2-L2)*L2>0,M2-L2,"")


In both cases, format the cell as time. If elapsed time may exceed 12
hours, use the custom number format [h]:mm


Tim C


"Cyrus" <Cyrus@discussions.microsoft.com> wrote in message
news:32F8E5D1-0BE5-42EB-B3AA-21DE9E6FA37F@microsoft.com...
>I need help with a formula. I'm creating a spreadsheet where data entry
> would indicate the time a patient arrives and than leaves. I need a
> formula
> that calculates this, but displays nothing if no time is entered (for
> example
> down the worksheet).
>
> =IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")
>
> This is what I have...I keep getting value error.
>
> i want the time to display if the time is greater than zero, but if less
> than zero, display nothing.
>
> Thanks,
>
> Cyrus

[Cyrus]

It worked!!! Thanks so much!!!

Cyrus

"CLR" wrote:

> =TEXT(IF(M2-L2>0,M2-L2,""),"h:mm")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Cyrus" wrote:
>
> > I need help with a formula. I'm creating a spreadsheet where data entry
> > would indicate the time a patient arrives and than leaves. I need a formula
> > that calculates this, but displays nothing if no time is entered (for example
> > down the worksheet).
> >
> > =IF((M2-L2)=0,""),IF((M2-L2)>0,"h:mm")
> >
> > This is what I have...I keep getting value error.
> >
> > i want the time to display if the time is greater than zero, but if less
> > than zero, display nothing.
> >
> > Thanks,
> >
> > Cyrus