How can I use a time difference in a subsequent formula in Excel?
 

When subtracting two dates/times from each other, Excel stores the result as
a number different from the displayed result (e.g., displayed result = 2.42
hours but stored number = 0.1125.) How can I effectively use the displayed
result in a subsequent formula rather than the stored number?

=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the result
as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

=TEXT(A1,"hh:mm")

--
HTH

Bob Phillips

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the result
as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

Hi numberman37,

I am not sure I understand you. For Excel 2:42=0.1125, pretty much like
11%=0.11. Why would you need to use the look of the value and not the value
itself. I think it just takes to understand that Excel uses integers for
dates (number of days since 1-1-1900) and decimals between 0 and 1 for time
where 0&1=24:00, 0.0416666666666667=01:00, 0.5=12:00, etc. If you want a
decimal representation of time in hours you could multiply the time value by
24, e.g. 0.1125*24=2.7=2 h 42 min.

Hope this helps.
KL


"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the result
as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to avoid
0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the result
as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

No

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to
avoid 0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the
result as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

Hi Niek,

I am sure you are right. It must be my ignorance, but I just struggle trying
to understand how come if cell A1's "...displayed result = 2.42 hours but
stored number = 0.1125..." the formula =value(text(A1,"h:mm")) returns
anything either than 0.1125

Regards,
KL



"Niek Otten" wrote in message
...
No

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to
avoid 0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the
result as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

Why do you say no Niek, it looks that way to me.

--
HTH

Bob Phillips

"Niek Otten" wrote in message
...
No

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to
avoid 0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the
result as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

<I am sure you are right

You're wrong! I was wrong indeed, the formatting causes Excel to interpret
the value as time and indeed re-generates the 0.1125, as you already
expected.
Sorry for the confusion!

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

I am sure you are right. It must be my ignorance, but I just struggle
trying to understand how come if cell A1's "...displayed result = 2.42
hours but stored number = 0.1125..." the formula =value(text(A1,"h:mm"))
returns anything either than 0.1125

Regards,
KL



"Niek Otten" wrote in message
...
No

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to
avoid 0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the
result as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

You're right Bob, see my answer to KL

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
Why do you say no Niek, it looks that way to me.

--
HTH

Bob Phillips

"Niek Otten" wrote in message
...
No

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KL" wrote in message
...
Hi Niek,

Wouldn't your second formula yeald the value numberman37 is trying to
avoid 0.1125 ? :-D

Regards,
KL


"Niek Otten" wrote in message
...
=text(A1,"h:mm") or, if you require a numeric result,
=value(text(A1,"h:mm"))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the
result as
a number different from the displayed result (e.g., displayed result
=
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?

Thanks to all who responded - I have been out on vacation and actually
figured this out before I left. KL is absolutely correct in that I just
needed to multiply the time value by 24 to receive the correct result. You
were all very kind to respond.
Kind Regards,
numberman37

"KL" wrote:

Hi numberman37,

I am not sure I understand you. For Excel 2:42=0.1125, pretty much like
11%=0.11. Why would you need to use the look of the value and not the value
itself. I think it just takes to understand that Excel uses integers for
dates (number of days since 1-1-1900) and decimals between 0 and 1 for time
where 0&1=24:00, 0.0416666666666667=01:00, 0.5=12:00, etc. If you want a
decimal representation of time in hours you could multiply the time value by
24, e.g. 0.1125*24=2.7=2 h 42 min.

Hope this helps.
KL


"numberman37" wrote in message
...
When subtracting two dates/times from each other, Excel stores the result
as
a number different from the displayed result (e.g., displayed result =
2.42
hours but stored number = 0.1125.) How can I effectively use the
displayed
result in a subsequent formula rather than the stored number?