I have downloaded data that has 7.5 in cell G2. I used G2*100 to get 0750 in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right, I2,2) to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It seems
like it's ignoring the zero in front of the 7 in I2, and therefore using the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct (representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

=A1/24 and format as hhmm
--
David Biddulph

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get 0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right, I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct (representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

Well I was hoping for something simple, but you surely can't get much
simplier than that. Thanks.
Is there a way to get that result 07:30 without the colon = 0730 ?

Thank you very much


"David Biddulph" wrote:

=A1/24 and format as hhmm
--
David Biddulph

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get 0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right, I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct (representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

You can just use a custom format of hhmm on the original time

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve" wrote in message
...
Well I was hoping for something simple, but you surely can't get much
simplier than that. Thanks.
Is there a way to get that result 07:30 without the colon = 0730 ?

Thank you very much


"David Biddulph" wrote:

=A1/24 and format as hhmm
--
David Biddulph

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get
0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right,
I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore
using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct
(representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically
I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

That's why I said format as hhmm (not hh:mm).
--
David Biddulph

"Steve" wrote in message
...
Well I was hoping for something simple, but you surely can't get much
simplier than that. Thanks.
Is there a way to get that result 07:30 without the colon = 0730 ?

Thank you very much

"David Biddulph" wrote:

=A1/24 and format as hhmm

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get
0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right,
I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore
using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct
(representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically
I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

Thanks all. All solutions worked like a charm.
However, I'm now running into another problem regarding the data I'm trying
to work with is from 3 separate databases. On 2 of them, the 0730 number is a
general format, and the other is in hhmm.
I'm trying to identify where the descrepencies are with a simple
=if(g5=h5,"ok","alert") and with the other, but because of the different
formats, it's not working. And as you all know,copying one format to the
other produes incorrect numbers. Is there a way to get the ok or alert to
work with the different formats ?

Much thanks,

Steve

"David Biddulph" wrote:

That's why I said format as hhmm (not hh:mm).
--
David Biddulph

"Steve" wrote in message
...
Well I was hoping for something simple, but you surely can't get much
simplier than that. Thanks.
Is there a way to get that result 07:30 without the colon = 0730 ?

Thank you very much

"David Biddulph" wrote:

=A1/24 and format as hhmm

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get
0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right,
I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore
using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM)
21.5 2150 50 30 2130 <- correct
(representing
9:30 PM)

Is there another simplier way, or a way to fix this problem. Basically
I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

If the one which isn't hhmm is text saying 0730, then use
=IF(TEXT(G$5,"hhmm")=H5,"ok","alert")
If it is a number formatted 0000, then use
=IF(--TEXT(G$5,"hhmm")=H5,"ok","alert")
--
David Biddulph

"Steve" wrote in message
...
Thanks all. All solutions worked like a charm.
However, I'm now running into another problem regarding the data I'm
trying
to work with is from 3 separate databases. On 2 of them, the 0730 number
is a
general format, and the other is in hhmm.
I'm trying to identify where the descrepencies are with a simple
=if(g5=h5,"ok","alert") and with the other, but because of the different
formats, it's not working. And as you all know,copying one format to the
other produes incorrect numbers. Is there a way to get the ok or alert to
work with the different formats ?

Much thanks,

Steve

"David Biddulph" wrote:

That's why I said format as hhmm (not hh:mm).
--
David Biddulph

"Steve" wrote in message
...
Well I was hoping for something simple, but you surely can't get much
simplier than that. Thanks.
Is there a way to get that result 07:30 without the colon = 0730 ?

Thank you very much

"David Biddulph" wrote:

=A1/24 and format as hhmm

"Steve" wrote in message
...
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get
0750
in
cell I2, formatted as 0000.
To convert the .5 hour (50) to 30 as minutes, I used in J2 (right,
I2,2)
to
pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give
me
the
30 I need (or the 00 for exact hour times), and then in L2 I have
=LEFT(I2,2)&K2 which gives me the correct result in cases where the
number
is or = 10. Single digits, such as 7 produce 7530 instead of 0730.
It
seems
like it's ignoring the zero in front of the 7 in I2, and therefore
using
the
2 left non-zero #'s, the 75, and the result is 7530.
G2 I2 J2 K2 L2
7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30
AM)
21.5 2150 50 30 2130 <- correct
(representing
9:30 PM)

Is there another simplier way, or a way to fix this problem.
Basically
I
need to convert #'s such as 7.5 to 0730.

Thanks,

Steve

In article ,
Steve wrote:

Is there another simplier way, or a way to fix this problem. Basically I
need to convert #'s such as 7.5 to 0730.

XL stores times as fractional days, 1 = 24 hours. So one way:

Put 24 in an empty cell. Copy the cell. Select your times. Choose
Edit/Paste Special, selecting the Values and Divide radio buttons. Click
OK.

WIth the cells still selected, choose Format/Cells/Number/Custom and
enter [hh]mm in the input box. Click Ok.