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

N

numberman37

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?
 
N

Niek Otten

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

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
K

KL

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
 
K

KL

Hi Niek,

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

Regards,
KL
 
K

KL

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
 
N

Niek Otten

<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
 
N

Niek Otten

You're right Bob, see my answer to KL

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
N

numberman37

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
 
Top