Convert date/time value to decimal hours

I'm trying to convert a column af date-time values, e.g.,09/02/2005 16:31
to decimal hours.
But I can't seem to get rid of the dd/mm/year compoent.

What I want to get is 232.517 all by itself.

Can someone tell me how to do that?

Thanks,
E-Jo

What's the logic in getting 232.517 from that value? If your date is Sep 9
2005 the only way you would get that is if you subtracted it from another
date like Sep 1 2005 00:00 so if you always want to get the days from the
first of the month and add the hours you can use

=DAY(A1)*24+MOD(A1,1)*24

where the date value is in A1, note that the cell has to be formatted as
General or number or else you will get a pseudo time

--
Regards,

Peo Sjoblom


"Everett Joline" <ejoline@optonline.net> wrote in message
news:%237Tb4$IZFHA.228@TK2MSFTNGP12.phx.gbl...
> I'm trying to convert a column af date-time values, e.g.,09/02/2005 16:31
> to decimal hours.
> But I can't seem to get rid of the dd/mm/year compoent.
>
> What I want to get is 232.517 all by itself.
>
> Can someone tell me how to do that?
>
> Thanks,
> E-Jo
>

Thanks Peo. Your method works fine. In particular, I think it
was your note that made the difference. The formatting of the
receiving cell must be numeric. This is very important.

E-Jo

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:uy4P1IJZFHA.3864@TK2MSFTNGP10.phx.gbl...
> What's the logic in getting 232.517 from that value? If your date is Sep 9
> 2005 the only way you would get that is if you subtracted it from another
> date like Sep 1 2005 00:00 so if you always want to get the days from the
> first of the month and add the hours you can use
>
> =DAY(A1)*24+MOD(A1,1)*24
>
> where the date value is in A1, note that the cell has to be formatted as
> General or number or else you will get a pseudo time
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "Everett Joline" <ejoline@optonline.net> wrote in message
> news:%237Tb4$IZFHA.228@TK2MSFTNGP12.phx.gbl...
>> I'm trying to convert a column af date-time values, e.g.,09/02/2005 16:31
>> to decimal hours.
>> But I can't seem to get rid of the dd/mm/year compoent.
>>
>> What I want to get is 232.517 all by itself.
>>
>> Can someone tell me how to do that?
>>
>> Thanks,
>> E-Jo
>>
>

Or, maybe it was the use of MOD(A1, 1) rather than INT(A1).
I can't say I really understand MOD(A1,1) but it works and INT(A1) does not.
E-Jo

"Everett Joline" <ejoline@optonline.net> wrote in message
news:eg8U3lKZFHA.2128@TK2MSFTNGP14.phx.gbl...
> Thanks Peo. Your method works fine. In particular, I think it
> was your note that made the difference. The formatting of the
> receiving cell must be numeric. This is very important.
>
> E-Jo
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:uy4P1IJZFHA.3864@TK2MSFTNGP10.phx.gbl...
>> What's the logic in getting 232.517 from that value? If your date is Sep
>> 9 2005 the only way you would get that is if you subtracted it from
>> another date like Sep 1 2005 00:00 so if you always want to get the days
>> from the first of the month and add the hours you can use
>>
>> =DAY(A1)*24+MOD(A1,1)*24
>>
>> where the date value is in A1, note that the cell has to be formatted as
>> General or number or else you will get a pseudo time
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "Everett Joline" <ejoline@optonline.net> wrote in message
>> news:%237Tb4$IZFHA.228@TK2MSFTNGP12.phx.gbl...
>>> I'm trying to convert a column af date-time values, e.g.,09/02/2005
>>> 16:31
>>> to decimal hours.
>>> But I can't seem to get rid of the dd/mm/year compoent.
>>>
>>> What I want to get is 232.517 all by itself.
>>>
>>> Can someone tell me how to do that?
>>>
>>> Thanks,
>>> E-Jo
>>>
>>
>
>

Put 1.2345 in A1.
Then put =int(a1) in B1
put =mod(a1,1) in c1

You'll see the difference.

Everett Joline wrote:
>
> Or, maybe it was the use of MOD(A1, 1) rather than INT(A1).
> I can't say I really understand MOD(A1,1) but it works and INT(A1) does not.
> E-Jo
>
> "Everett Joline" <ejoline@optonline.net> wrote in message
> news:eg8U3lKZFHA.2128@TK2MSFTNGP14.phx.gbl...
> > Thanks Peo. Your method works fine. In particular, I think it
> > was your note that made the difference. The formatting of the
> > receiving cell must be numeric. This is very important.
> >
> > E-Jo
> >
> > "Peo Sjoblom" <terre08@mvps.org> wrote in message
> > news:uy4P1IJZFHA.3864@TK2MSFTNGP10.phx.gbl...
> >> What's the logic in getting 232.517 from that value? If your date is Sep
> >> 9 2005 the only way you would get that is if you subtracted it from
> >> another date like Sep 1 2005 00:00 so if you always want to get the days
> >> from the first of the month and add the hours you can use
> >>
> >> =DAY(A1)*24+MOD(A1,1)*24
> >>
> >> where the date value is in A1, note that the cell has to be formatted as
> >> General or number or else you will get a pseudo time
> >>
> >> --
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >> "Everett Joline" <ejoline@optonline.net> wrote in message
> >> news:%237Tb4$IZFHA.228@TK2MSFTNGP12.phx.gbl...
> >>> I'm trying to convert a column af date-time values, e.g.,09/02/2005
> >>> 16:31
> >>> to decimal hours.
> >>> But I can't seem to get rid of the dd/mm/year compoent.
> >>>
> >>> What I want to get is 232.517 all by itself.
> >>>
> >>> Can someone tell me how to do that?
> >>>
> >>> Thanks,
> >>> E-Jo
> >>>
> >>
> >
> >

--

Dave Peterson

Yep ... same as
=A1-INT(A1)
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:429AF73F.AC5AD966@netscapeXSPAM.com...
> Put 1.2345 in A1.
> Then put =int(a1) in B1
> put =mod(a1,1) in c1
>
> You'll see the difference.
>
> Everett Joline wrote:
> >
> > Or, maybe it was the use of MOD(A1, 1) rather than INT(A1).
> > I can't say I really understand MOD(A1,1) but it works and INT(A1) does
not.
> > E-Jo
> >
> > "Everett Joline" <ejoline@optonline.net> wrote in message
> > news:eg8U3lKZFHA.2128@TK2MSFTNGP14.phx.gbl...
> > > Thanks Peo. Your method works fine. In particular, I think it
> > > was your note that made the difference. The formatting of the
> > > receiving cell must be numeric. This is very important.
> > >
> > > E-Jo
> > >
> > > "Peo Sjoblom" <terre08@mvps.org> wrote in message
> > > news:uy4P1IJZFHA.3864@TK2MSFTNGP10.phx.gbl...
> > >> What's the logic in getting 232.517 from that value? If your date is
Sep
> > >> 9 2005 the only way you would get that is if you subtracted it from
> > >> another date like Sep 1 2005 00:00 so if you always want to get the
days
> > >> from the first of the month and add the hours you can use
> > >>
> > >> =DAY(A1)*24+MOD(A1,1)*24
> > >>
> > >> where the date value is in A1, note that the cell has to be formatted
as
> > >> General or number or else you will get a pseudo time
> > >>
> > >> --
> > >> Regards,
> > >>
> > >> Peo Sjoblom
> > >>
> > >>
> > >> "Everett Joline" <ejoline@optonline.net> wrote in message
> > >> news:%237Tb4$IZFHA.228@TK2MSFTNGP12.phx.gbl...
> > >>> I'm trying to convert a column af date-time values, e.g.,09/02/2005
> > >>> 16:31
> > >>> to decimal hours.
> > >>> But I can't seem to get rid of the dd/mm/year compoent.
> > >>>
> > >>> What I want to get is 232.517 all by itself.
> > >>>
> > >>> Can someone tell me how to do that?
> > >>>
> > >>> Thanks,
> > >>> E-Jo
> > >>>
> > >>
> > >
> > >
>
> --
>
> Dave Peterson