I have a spread sheet and in this sheet i have a set time that it takes to do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to have
a problem with this, any ideas what i am doing wrong ?

=MOD(A2-A1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stan Halls" wrote in message
...
I have a spread sheet and in this sheet i have a set time that it takes to
do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the
time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to
have
a problem with this, any ideas what i am doing wrong ?

Try:

=MOD(A2-A1,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Stan Halls" wrote in message
...
I have a spread sheet and in this sheet i have a set time that it takes to
do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the
time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to
have
a problem with this, any ideas what i am doing wrong ?

Hi!

Try this:

=IF(A20,A2-A1+(A2<A1),"")

Format the cell as TIME 13:30 or CUSTOM h:mm

One thing of note, you're testing cell A2 against 0. What if A2 = 12:00 AM
which is equal to zero? Maybe you want this instead:

=IF(ISNUMBER(A2),A2-A1+(A2<A1),"")

Biff

"Stan Halls" wrote in message
...
I have a spread sheet and in this sheet i have a set time that it takes to
do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the
time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to
have
a problem with this, any ideas what i am doing wrong ?

Bob
I am not very good at this excel , is the =mod function able to be used
within an if function so that if there is no time in cell A2 then i get a
blank cell


"Bob Phillips" wrote:

=MOD(A2-A1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stan Halls" wrote in message
...
I have a spread sheet and in this sheet i have a set time that it takes to
do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the
time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to
have
a problem with this, any ideas what i am doing wrong ?

Bob
have sorted it out, thanks for the help to all of you that posted,
=IF(ISNUMBER(a2),MOD(a2-a1,1),"") this worked for me

"Bob Phillips" wrote:

=MOD(A2-A1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stan Halls" wrote in message
...
I have a spread sheet and in this sheet i have a set time that it takes to
do
a run ie 01:30 1 hour 30 mins (A1)
then i have another cell that i input a time (A2) and a 3rd cell that has
an if function that says =if( a20, a2-a1,"") this works fine untill the
time
difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get
###### instead of 23:30, in lotus 123 it worked fine but excel seems to
have
a problem with this, any ideas what i am doing wrong ?