So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I made an
entry in D, is there a way to use the first Formula and leave the column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") , the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over 0:10:59 in
column C.

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I made
an
entry in D, is there a way to use the first Formula and leave the column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over 0:10:59
in
column C.

I tried that and it doesnt seem to work. Sumproduct is counting from E, I
wonder if Sumproduct can count from after I make an entry in D, the actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I made
an
entry in D, is there a way to use the first Formula and leave the column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over 0:10:59
in
column C.

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I tried that and it doesn't seem to work. Sumproduct is counting from E, I
wonder if Sumproduct can count from after I make an entry in D, the actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could
sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a
hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I
made
an
entry in D, is there a way to use the first Formula and leave the
column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over
0:10:59
in
column C.

Thank You again, that works very well. Is there a way to use,

=IF(C3<=D3,"+","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

In LTC column E without it showing -0, before making an entry in D. If
possible I would like E to appear blank until I make an entry in D


"Bob Phillips" wrote:

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I tried that and it doesn't seem to work. Sumproduct is counting from E, I
wonder if Sumproduct can count from after I make an entry in D, the actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could
sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a
hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I
made
an
entry in D, is there a way to use the first Formula and leave the
column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over
0:10:59
in
column C.

It works but, when the negative time is less than -10 (-11,-12) the count is
placed in the over -- 0:10:59. Not sure why, and I tried a couple of things
but I couldn't fix it.

"Bob Phillips" wrote:

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I tried that and it doesn't seem to work. Sumproduct is counting from E, I
wonder if Sumproduct can count from after I make an entry in D, the actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could
sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a
hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I
made
an
entry in D, is there a way to use the first Formula and leave the
column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over
0:10:59
in
column C.

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)*SIGN(LTC!$D$3:$D$19-LTC!$C$3:$C$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
It works but, when the negative time is less than -10 (-11,-12) the count
is
placed in the over -- 0:10:59. Not sure why, and I tried a couple of
things
but I couldn't fix it.

"Bob Phillips" wrote:

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I tried that and it doesn't seem to work. Sumproduct is counting from
E, I
wonder if Sumproduct can count from after I make an entry in D, the
actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could
sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a
hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I
made
an
entry in D, is there a way to use the first Formula and leave the
column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in
E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","-
")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over
0:10:59
in
column C.

Yes, That's Excellent, works very well. Just changed the last part to $D$ and
all is good.
Thank You.


"Bob Phillips" wrote:

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)*SIGN(LTC!$D$3:$D$19-LTC!$C$3:$C$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
It works but, when the negative time is less than -10 (-11,-12) the count
is
placed in the over -- 0:10:59. Not sure why, and I tried a couple of
things
but I couldn't fix it.

"Bob Phillips" wrote:

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)<=--"0:10:59"),--(LTC!$E$3:$E$19<""))

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$19,4)=OTP!A8),--(ABS(LTC!$C$3:$C$19-LTC!$D$3:$D$19)--"0:10:59"),--(LTC!$E$3:$E$19<""))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I tried that and it doesn't seem to work. Sumproduct is counting from
E, I
wonder if Sumproduct can count from after I make an entry in D, the
actual
time.

A B C D E
F

Run Vehicle Time Actual Diff Schedule
800 0 5:49 - 0 510N0545
914 0 6:01 - 0 511N0557

Can sumproduct do the math? If I enter in D, 5:50, 0r 5:48 could
sumproduct
be set to place a count for any time up to 11min.
Maybe I could use it the way I had it, but use a different column, a
hidden
column for sumproduct.




"Peo Sjoblom" wrote:

It's because text is always greater than numbers, try


=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(ISNUMBER(LTC!$E$3:$E$192)),--(LTC!$E$3:$E$192<""))


--


Regards,


Peo Sjoblom

"Dale G" wrote in message
...
So far I found I can use this, in column E.

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss")

To display negative time, whereas before I was using,

=IF(D3="","",D3-C3) to leave the column E blank.

The 2nd one was good because it would leave the column blank until I
made
an
entry in D, is there a way to use the first Formula and leave the
column
blank?

Also I'm using Sumproduct on another sheet to count the entrees in
E.

Here is an example.

In B,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10:59"),--(LTC!$E$3:$E$192<""))

In C,
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192--"0:10:59"),--(LTC!$E$3:$E$192<""))

When I use =IF(C3<=D3,"+ ","-
")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") ,
the
sumproduct formula will count all entrees in C, --"0:10:59"

Is there a way to make these 2 sheets work together?

I need anything negative and up to 0:10:59 in column B, and over
0:10:59
in
column C.