Convert Date & hour to decimal

If I have a column of values with a format like 9/2/2005 4:30, how
do I convert that to decimal hours after (say) 1/1/2005 0:0?
Also, is 4:30 routinely treated as HH:MM or might it be interpreted as
MM:SS?
Thanks,
E-Jo

Hi
Take the earlier date/time from the later one and format the result as [hh]

Andy.

"Everett Joline" <ejoline@optonline.net> wrote in message
news:emvmG3yVFHA.4056@TK2MSFTNGP15.phx.gbl...
> If I have a column of values with a format like 9/2/2005 4:30, how
> do I convert that to decimal hours after (say) 1/1/2005 0:0?
> Also, is 4:30 routinely treated as HH:MM or might it be interpreted as
> MM:SS?
> Thanks,
> E-Jo
>

XL stores dates as integral offsets from a base date, and times as
fractional days, so you can do simple math (multiplying by 24 to convert
days and fractional days to hours):


A1: 9/2/2005
B1: =(A1-Date(2005,1,1))*24

An entry of 4:30 will be interpreted as 4 hours, 30 minutes, and
converted and stored as 0.1875 (regardless of how it's displayed).


In article <emvmG3yVFHA.4056@TK2MSFTNGP15.phx.gbl>,
"Everett Joline" <ejoline@optonline.net> wrote:

> If I have a column of values with a format like 9/2/2005 4:30, how
> do I convert that to decimal hours after (say) 1/1/2005 0:0?
> Also, is 4:30 routinely treated as HH:MM or might it be interpreted as
> MM:SS?

Hi Everett,
A1: =NOW()
B1: =MOD(A1,1)
C1: =B1 * 24 format as 0.0 to get decimal hours

Yes 4:30 would be 4 hours 30 minutes,
you would have to enter 0:4:30 for 4 minutes 30 seconds
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Everett Joline" <ejoline@optonline.net> wrote in message news:emvmG3yVFHA.4056@TK2MSFTNGP15.phx.gbl...
> If I have a column of values with a format like 9/2/2005 4:30, how
> do I convert that to decimal hours after (say) 1/1/2005 0:0?
> Also, is 4:30 routinely treated as HH:MM or might it be interpreted as
> MM:SS?
> Thanks,
> E-Jo
>
>

OK, I think I'm finally starting to understand. You guys
certainly expedited the process with your instructions
and examples.
So thanks Andy JE and David for your help,
E-Jo

"Everett Joline" <ejoline@optonline.net> wrote in message
news:emvmG3yVFHA.4056@TK2MSFTNGP15.phx.gbl...
> If I have a column of values with a format like 9/2/2005 4:30, how
> do I convert that to decimal hours after (say) 1/1/2005 0:0?
> Also, is 4:30 routinely treated as HH:MM or might it be interpreted as
> MM:SS?
> Thanks,
> E-Jo
>