I have a column that has times written as numbers, so, I have 1:30 written as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it would
go from 0:00 to 24:00 (military hours). I have so many records to go manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it will
take forever.
thanks
Al

On Sun, 23 Aug 2009 16:33:01 -0700, Al wrote:

I have a column that has times written as numbers, so, I have 1:30 written as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it would
go from 0:00 to 24:00 (military hours). I have so many records to go manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it will
take forever.
thanks
Al

Excel stores time as fractions of a day (1 hr = 1/24).

So first convert the value into something that excel recognizes as a time.
e.g., with your value in H1:

=DOLLARDE(H1,60)/24

or if you do not have the ATP installed:

=(INT(H1)+MOD(H1,1)/0.6)/24

Then format the result as time (e.g. [h]:mm )
--ron

On Sun, 23 Aug 2009 16:33:01 -0700, Al wrote:

I have a column that has times written as numbers, so, I have 1:30 written as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it would
go from 0:00 to 24:00 (military hours). I have so many records to go manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it will
take forever.
thanks
Al

One change:

For my previous post, after you convert using the formula I posted, to get the
output as you want, format (Format/Cells/Number/Custom Type: hh:mm )
--ron

I have a column that has times written as numbers, so, I have 1:30 written
as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it
would
go from 0:00 to 24:00 (military hours). I have so many records to go
manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it
will
take forever.
thanks
Al

Excel stores time as fractions of a day (1 hr = 1/24).

So first convert the value into something that excel recognizes as a time.
e.g., with your value in H1:

=DOLLARDE(H1,60)/24

or if you do not have the ATP installed:

=(INT(H1)+MOD(H1,1)/0.6)/24

Then format the result as time (e.g. [h]:mm )

Here is another non ATP formula as well...

=--SUBSTITUTE(TEXT(H1,"0.00"),".",":")

Rick

Worked fine thanks

"Ron Rosenfeld" wrote:

On Sun, 23 Aug 2009 16:33:01 -0700, Al wrote:

I have a column that has times written as numbers, so, I have 1:30 written as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it would
go from 0:00 to 24:00 (military hours). I have so many records to go manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it will
take forever.
thanks
Al

One change:

For my previous post, after you convert using the formula I posted, to get the
output as you want, format (Format/Cells/Number/Custom Type: hh:mm )
--ron

this also worked, thanks

"Rick Rothstein" wrote:

I have a column that has times written as numbers, so, I have 1:30 written
as
1.3 and 24:00 written as 24. and so on. When I try to format the column as
Time, I get very strange results. I need to format the time so that it
would
go from 0:00 to 24:00 (military hours). I have so many records to go
manually
to make these changes. When I make the change manually it works fine. Can
someone help me please. I am trying to meet a deadline and this way, it
will
take forever.
thanks
Al

Excel stores time as fractions of a day (1 hr = 1/24).

So first convert the value into something that excel recognizes as a time.
e.g., with your value in H1:

=DOLLARDE(H1,60)/24

or if you do not have the ATP installed:

=(INT(H1)+MOD(H1,1)/0.6)/24

Then format the result as time (e.g. [h]:mm )

Here is another non ATP formula as well...

=--SUBSTITUTE(TEXT(H1,"0.00"),".",":")

Rick

On Sun, 23 Aug 2009 19:29:01 -0700, Al wrote:

Worked fine thanks

You're welcome. Thanks for the feedback.
--ron

On Sun, 23 Aug 2009 22:18:58 -0400, "Rick Rothstein"
wrote:

Here is another non ATP formula as well...

=--SUBSTITUTE(TEXT(H1,"0.00"),".",":")

I believe this method requires that one be using the colon as a time separator
in the Regional and Language Windows settings. I don't know of any countries
that don't use it, but someone might have customized it.
--ron