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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <nicolaus@xs4all.nl> wrote in message
news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> =text(A1,"h:mm") or, if you require a numeric result,
> =value(text(A1,"h:mm"))
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> 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" <nicolaus@xs4all.nl> wrote in message
news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> =text(A1,"h:mm") or, if you require a numeric result,
> =value(text(A1,"h:mm"))
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> Hi Niek,
>
> Wouldn't your second formula yeald the value numberman37 is trying to
> avoid 0.1125 ? :-D
>
> Regards,
> KL
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> =text(A1,"h:mm") or, if you require a numeric result,
>> =value(text(A1,"h:mm"))
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> Hi Niek,
>
> Wouldn't your second formula yeald the value numberman37 is trying to
> avoid 0.1125 ? :-D
>
> Regards,
> KL
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> =text(A1,"h:mm") or, if you require a numeric result,
>> =value(text(A1,"h:mm"))
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> Hi Niek,
>>
>> Wouldn't your second formula yeald the value numberman37 is trying to
>> avoid 0.1125 ? :-D
>>
>> Regards,
>> KL
>>
>>
>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>> =text(A1,"h:mm") or, if you require a numeric result,
>>> =value(text(A1,"h:mm"))
>>>
>>> --
>>> Kind regards,
>>>
>>> Niek Otten
>>>
>>> Microsoft MVP - Excel
>>>
>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> Hi Niek,
>>
>> Wouldn't your second formula yeald the value numberman37 is trying to
>> avoid 0.1125 ? :-D
>>
>> Regards,
>> KL
>>
>>
>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>> =text(A1,"h:mm") or, if you require a numeric result,
>>> =value(text(A1,"h:mm"))
>>>
>>> --
>>> Kind regards,
>>>
>>> Niek Otten
>>>
>>> Microsoft MVP - Excel
>>>
>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:ekeOpg8hFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>>> Hi Niek,
>>>
>>> Wouldn't your second formula yeald the value numberman37 is trying to
>>> avoid 0.1125 ? :-D
>>>
>>> Regards,
>>> KL
>>>
>>>
>>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>>> =text(A1,"h:mm") or, if you require a numeric result,
>>>> =value(text(A1,"h:mm"))
>>>>
>>>> --
>>>> Kind regards,
>>>>
>>>> Niek Otten
>>>>
>>>> Microsoft MVP - Excel
>>>>
>>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:ekeOpg8hFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>>> Hi Niek,
>>>
>>> Wouldn't your second formula yeald the value numberman37 is trying to
>>> avoid 0.1125 ? :-D
>>>
>>> Regards,
>>> KL
>>>
>>>
>>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>>> =text(A1,"h:mm") or, if you require a numeric result,
>>>> =value(text(A1,"h:mm"))
>>>>
>>>> --
>>>> Kind regards,
>>>>
>>>> Niek Otten
>>>>
>>>> Microsoft MVP - Excel
>>>>
>>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:ekeOpg8hFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>>> Hi Niek,
>>>
>>> Wouldn't your second formula yeald the value numberman37 is trying to
>>> avoid 0.1125 ? :-D
>>>
>>> Regards,
>>> KL
>>>
>>>
>>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>>> =text(A1,"h:mm") or, if you require a numeric result,
>>>> =value(text(A1,"h:mm"))
>>>>
>>>> --
>>>> Kind regards,
>>>>
>>>> Niek Otten
>>>>
>>>> Microsoft MVP - Excel
>>>>
>>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> > Hi Niek,
> >
> > Wouldn't your second formula yeald the value numberman37 is trying to
> > avoid 0.1125 ? :-D
> >
> > Regards,
> > KL
> >
> >
> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> >> =text(A1,"h:mm") or, if you require a numeric result,
> >> =value(text(A1,"h:mm"))
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> Microsoft MVP - Excel
> >>
> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> >>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> > Hi Niek,
> >
> > Wouldn't your second formula yeald the value numberman37 is trying to
> > avoid 0.1125 ? :-D
> >
> > Regards,
> > KL
> >
> >
> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> >> =text(A1,"h:mm") or, if you require a numeric result,
> >> =value(text(A1,"h:mm"))
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> Microsoft MVP - Excel
> >>
> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> >>> 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" <phillips@tiscali.co.uk> wrote in message
news:OUrDxi8hFHA.1244@TK2MSFTNGP14.phx.gbl...
> Why do you say no Niek, it looks that way to me.
>
> --
> HTH
>
> Bob Phillips
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> > Hi Niek,
>> >
>> > Wouldn't your second formula yeald the value numberman37 is trying to
>> > avoid 0.1125 ? :-D
>> >
>> > Regards,
>> > KL
>> >
>> >
>> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> >> =text(A1,"h:mm") or, if you require a numeric result,
>> >> =value(text(A1,"h:mm"))
>> >>
>> >> --
>> >> Kind regards,
>> >>
>> >> Niek Otten
>> >>
>> >> Microsoft MVP - Excel
>> >>
>> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> >>> 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" <phillips@tiscali.co.uk> wrote in message
news:OUrDxi8hFHA.1244@TK2MSFTNGP14.phx.gbl...
> Why do you say no Niek, it looks that way to me.
>
> --
> HTH
>
> Bob Phillips
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> > Hi Niek,
>> >
>> > Wouldn't your second formula yeald the value numberman37 is trying to
>> > avoid 0.1125 ? :-D
>> >
>> > Regards,
>> > KL
>> >
>> >
>> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> >> =text(A1,"h:mm") or, if you require a numeric result,
>> >> =value(text(A1,"h:mm"))
>> >>
>> >> --
>> >> Kind regards,
>> >>
>> >> Niek Otten
>> >>
>> >> Microsoft MVP - Excel
>> >>
>> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> >>> 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" <phillips@tiscali.co.uk> wrote in message
news:OUrDxi8hFHA.1244@TK2MSFTNGP14.phx.gbl...
> Why do you say no Niek, it looks that way to me.
>
> --
> HTH
>
> Bob Phillips
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
>> No
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
>> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> > Hi Niek,
>> >
>> > Wouldn't your second formula yeald the value numberman37 is trying to
>> > avoid 0.1125 ? :-D
>> >
>> > Regards,
>> > KL
>> >
>> >
>> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> >> =text(A1,"h:mm") or, if you require a numeric result,
>> >> =value(text(A1,"h:mm"))
>> >>
>> >> --
>> >> Kind regards,
>> >>
>> >> Niek Otten
>> >>
>> >> Microsoft MVP - Excel
>> >>
>> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> >>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
>> Hi Niek,
>>
>> Wouldn't your second formula yeald the value numberman37 is trying to
>> avoid 0.1125 ? :-D
>>
>> Regards,
>> KL
>>
>>
>> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
>> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>>> =text(A1,"h:mm") or, if you require a numeric result,
>>> =value(text(A1,"h:mm"))
>>>
>>> --
>>> Kind regards,
>>>
>>> Niek Otten
>>>
>>> Microsoft MVP - Excel
>>>
>>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>>> 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" <nicolaus@xs4all.nl> wrote in message
news:ObX3QW8hFHA.1204@TK2MSFTNGP12.phx.gbl...
> No
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> > Hi Niek,
> >
> > Wouldn't your second formula yeald the value numberman37 is trying to
> > avoid 0.1125 ? :-D
> >
> > Regards,
> > KL
> >
> >
> > "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> > news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> >> =text(A1,"h:mm") or, if you require a numeric result,
> >> =value(text(A1,"h:mm"))
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> Microsoft MVP - Excel
> >>
> >> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> >> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> >>> 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" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OeeiyG8hFHA.1464@TK2MSFTNGP14.phx.gbl...
> Hi Niek,
>
> Wouldn't your second formula yeald the value numberman37 is trying to
> avoid 0.1125 ? :-D
>
> Regards,
> KL
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
>> =text(A1,"h:mm") or, if you require a numeric result,
>> =value(text(A1,"h:mm"))
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
>> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>>> 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" <nicolaus@xs4all.nl> wrote in message
news:eU9qPA8hFHA.2156@TK2MSFTNGP14.phx.gbl...
> =text(A1,"h:mm") or, if you require a numeric result,
> =value(text(A1,"h:mm"))
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "numberman37" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
>> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> > 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" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> > 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" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> > 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" <numberman37@discussions.microsoft.com> wrote in message
> news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> > 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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" <numberman37@discussions.microsoft.com> wrote in message
news:9A25BC70-B5F4-4DCC-848C-E5AAB9EABF25@microsoft.com...
> 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?