> 24:00

[an]

I have a formulae to sum hours conditionaly:

=SUMPRODUCT(--(colorindex(A1:A3;TRUE)=3);--(A1:A3>TIME
(24;0;0));A1:A3)


If result will be greater that 24:00, for example 25:00,
the formulae return 1:00.

Is possible to get 1 day and 1:00 hour, please?

Thanks in advance.
an

 

[Frank Kabel]

Hi
format the output cell with
d hh:mm

 

[an]

Thanks for your reply.

Oops!

25:00
Return 2 01:00
When it would have to be 1 01:00

Thanks.
an

 

[Myrna Larson]

Modify your formula to

=IF(B1-A1>1,B1-A1-1,B1-A1)

Then format as Frank suggests. But note that this modifies your data, so you
won't get the correct result if you add such numbers.

Most of the time you leave the original formula alone and instead format the
cell to shows hours > 24 (pick the format that does that from the list of time
formats).

 

[an]

Sorry.
I don't understood.
What I put in B column?

Thanks.
an

 

[Myrna Larson]

The formula assumes the starting time is in A1 and the ending time in B1. But
this won't work, as I said. You'll always see 1 as the day.

You need to use a format that shows hours > 24. For me, the list shows
37:30:55. You can modify that to remove the seconds; select Custom as the
format, and in the Type box, enter [hh]:mm

 

[Guest]

Yhanks for your reply.

I think understand your reasoning. It seems logical.
But in fact, when applied to a block, and conjugated with
SUMPRODUCT and ColorIndex...
Ex:
A B
1 5:00
2 10:00
3 10:00
4

-----Original Message-----
The formula assumes the starting time is in A1 and the ending time in B1. But
this won't work, as I said. You'll always see 1 as the day.

You need to use a format that shows hours > 24. For me, the list shows
37:30:55. You can modify that to remove the seconds; select Custom as the
format, and in the Type box, enter [hh]:mm

Sorry.
I don't understood.
What I put in B column?

Thanks.
an
and
instead format the that
from the list of time

.

 

[an]

(Sorry for my last and incomplete msg)

Yhanks for your reply.

I think understand your reasoning. It seems logical.
But in fact, when applied to a block, and conjugated with
SUMPRODUCT and ColorIndex, such as in the beginning of the
question.

Ex:
A B
1 5:00 25:00
2 10:00
3 10:00
4 15:00

In block (A1:A4), if (A1:A3) they will be RED, then the
next formulae:

=SUMPRODUCT(--(colorindex(A1:A4;TRUE)=3);--(A1:A4>TIME
(24;0;0));A1:A4)

it would have to return us
1 01:00 (One day more One hour)

Much knowledge is necessary, that I do not have...

Many thanks.
an

-----Original Message-----
The formula assumes the starting time is in A1 and the ending time in B1. But
this won't work, as I said. You'll always see 1 as the day.

You need to use a format that shows hours > 24. For me, the list shows
37:30:55. You can modify that to remove the seconds; select Custom as the
format, and in the Type box, enter [hh]:mm

Sorry.
I don't understood.
What I put in B column?

Thanks.
an
and
instead format the that
from the list of time

.

 

[Myrna Larson]

My previous responses were incorrect. Frank's format WILL return a day of 0.
If you saw 2 with a 25-hour span, there's some error in your formula.

(Sorry for my last and incomplete msg)

Yhanks for your reply.

I think understand your reasoning. It seems logical.
But in fact, when applied to a block, and conjugated with
SUMPRODUCT and ColorIndex, such as in the beginning of the
question.

Ex:
A B
1 5:00 25:00
2 10:00
3 10:00
4 15:00

In block (A1:A4), if (A1:A3) they will be RED, then the
next formulae:

=SUMPRODUCT(--(colorindex(A1:A4;TRUE)=3);--(A1:A4>TIME
(24;0;0));A1:A4)

it would have to return us
1 01:00 (One day more One hour)

Much knowledge is necessary, that I do not have...

Many thanks.
an

-----Original Message-----
The formula assumes the starting time is in A1 and the ending time in B1. But
this won't work, as I said. You'll always see 1 as the day.

You need to use a format that shows hours > 24. For me, the list shows
37:30:55. You can modify that to remove the seconds; select Custom as the
format, and in the Type box, enter [hh]:mm

Sorry.
I don't understood.
What I put in B column?

Thanks.
an

-----Original Message-----
Modify your formula to

=IF(B1-A1>1,B1-A1-1,B1-A1)

Then format as Frank suggests. But note that this
modifies your data, so you
won't get the correct result if you add such numbers.

Most of the time you leave the original formula alone and
instead format the
cell to shows hours > 24 (pick the format that does that
from the list of time
formats).

On Mon, 27 Sep 2004 03:59:07 -0700, "an"
<[email protected]>
wrote:

Thanks for your reply.

Oops!

25:00
Return 2 01:00
When it would have to be 1 01:00

Thanks.
an

-----Original Message-----
Hi
format the output cell with
d hh:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
I have a formulae to sum hours conditionaly:

=SUMPRODUCT(--(colorindex(A1:A3;TRUE)=3);-- (A1:A3>TIME
(24;0;0));A1:A3)


If result will be greater that 24:00, for example
25:00,
the formulae return 1:00.

Is possible to get 1 day and 1:00 hour, please?

Thanks in advance.
an

.


.

.

 

[an]

Ok.
Thanks for your help.
Exactly!

I go to repair all sheet because now work fine but I don't
see any difference between formulaes...

Many thanks for all.
Good luck.
an

-----Original Message-----
My previous responses were incorrect. Frank's format WILL return a day of 0.
If you saw 2 with a 25-hour span, there's some error in your formula.

(Sorry for my last and incomplete msg)

Yhanks for your reply.

I think understand your reasoning. It seems logical.
But in fact, when applied to a block, and conjugated with
SUMPRODUCT and ColorIndex, such as in the beginning of the
question.

Ex:
A B
1 5:00 25:00
2 10:00
3 10:00
4 15:00

In block (A1:A4), if (A1:A3) they will be RED, then the
next formulae:

=SUMPRODUCT(--(colorindex(A1:A4;TRUE)=3);--(A1:A4>TIME
(24;0;0));A1:A4)

it would have to return us
1 01:00 (One day more One hour)

Much knowledge is necessary, that I do not have...

Many thanks.
an

-----Original Message-----
The formula assumes the starting time is in A1 and the ending time in B1. But
this won't work, as I said. You'll always see 1 as the day.

You need to use a format that shows hours > 24. For me, the list shows
37:30:55. You can modify that to remove the seconds; select Custom as the
format, and in the Type box, enter [hh]:mm

wrote:

Sorry.
I don't understood.
What I put in B column?

Thanks.
an

-----Original Message-----
Modify your formula to

=IF(B1-A1>1,B1-A1-1,B1-A1)

Then format as Frank suggests. But note that this
modifies your data, so you
won't get the correct result if you add such numbers.

Most of the time you leave the original formula alone and
instead format the
cell to shows hours > 24 (pick the format that does that
from the list of time
formats).

On Mon, 27 Sep 2004 03:59:07 -0700, "an"
<[email protected]>
wrote:

Thanks for your reply.

Oops!

25:00
Return 2 01:00
When it would have to be 1 01:00

Thanks.
an

-----Original Message-----
Hi
format the output cell with
d hh:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
I have a formulae to sum hours conditionaly:

=SUMPRODUCT(--(colorindex(A1:A3;TRUE)=3);-- (A1:A3>TIME
(24;0;0));A1:A3)


If result will be greater that 24:00, for example
25:00,
the formulae return 1:00.

Is possible to get 1 day and 1:00 hour, please?

Thanks in advance.
an

.


.


.

.