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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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" <[email protected]> wrote in message
> > >> news:%[email protected]...
> > >>> 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

OK, thanks guys. It's finally starting to get through.
E-Jo

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Yep ... same as
> =A1-INT(A1)
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > >> 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" <[email protected]> wrote in message
>> > >> news:%[email protected]...
>> > >>> 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
>