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

 

[Myrna Larson]

In thinking about it further, the formula I proposed will always show the
number of days as 1, even when the time is less than 24 hours. That's because
a "d" in the format string means the day of the month. You can't display a 0
because there's no 0th day of the month.

With that formula, if B1-A1>1, you could use the format d hh:mm, but if
B1-A1<1, the format would have to be "0 "hh:mm

But there's no way to set up a conditional number format that looks at B1 and
A1; it looks at the value in the cell that's being formatted.

So I think you are "stuck" with displaying the number of hours as something
greater than 24 here.

 

[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]

I just tried Frank's format (I should have done that before posting what's
below!) and in fact it *will* display 0 for the day for a period less than
24 hours. So Frank's solution is correct, and you don't need to modify the
calculation or use different formats form times more than one day.

I don't know why the OP says that 25 hours displays as 2 1:00. For me it
shows 1 1:00.

 

[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

.


.


.

.

 

[an]

....Because I had the sheet with format "1904 date
system"...

an

 

[Myrna Larson]

Mystery explained! Thanks for the feedback.

As I said, in a format code "d" means "display the day of the month". In the
1904 date system ("Mac" system), Jan 1, 1904 is "Day 0". Since a time less
than 24 hours has a value between 0 and 0.999, it corresponds to a time on Jan
1, 1904. So you see a 1 as the day of the (calendar) month. If the time is 25
hours, it's the number 25/24 = 1.041666. That corresponds to a time on "Day
1", which is Jan 2, 1904, so you see 2 as the day of the month.

OTOH, in the 1900 date system, "Day 1" is Jan 1, 1900. "Day 0" *should be* Dec
31, 1899. And a number between 0 and 0.999 should represent a time on Dec 31,
1899, and a "d" in the format string *should* display 31 rather than 0

Maybe the rationale is that there really is no "Day 0" -- the 1900 date
system won't accept dates earlier than Jan 1, 1900. A "date" that is a number
between 0 and 0.999 is taken to mean a time without a date, and if you display
it with the "day of the month" the 0 is Excel's way of saying "I don't know --
you didn't specify the date".

 

[an]

To know: Is pretty!
an

-----Original Message-----
Mystery explained! Thanks for the feedback.

As I said, in a format code "d" means "display the day of the month". In the
1904 date system ("Mac" system), Jan 1, 1904 is "Day 0". Since a time less
than 24 hours has a value between 0 and 0.999, it corresponds to a time on Jan
1, 1904. So you see a 1 as the day of the (calendar) month. If the time is 25
hours, it's the number 25/24 = 1.041666. That corresponds to a time on "Day
1", which is Jan 2, 1904, so you see 2 as the day of the month.

OTOH, in the 1900 date system, "Day 1" is Jan 1, 1900. "Day 0" *should be* Dec
31, 1899. And a number between 0 and 0.999 should represent a time on Dec 31,
1899, and a "d" in the format string *should* display 31 rather than 0

Maybe the rationale is that there really is no "Day 0" -- the 1900 date
system won't accept dates earlier than Jan 1, 1900. A "date" that is a number
between 0 and 0.999 is taken to mean a time without a date, and if you display
it with the "day of the month" the 0 is Excel's way of saying "I don't know --
you didn't specify the date".



.