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

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.

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

OK, thanks, however, I may have misspoke in my previous message.

I have a cell formatted as 0000 to show as 0700 (d1).
In e1, the results of a Vlookup that originally takes a general formatted
7, divides by 24, and then is formatted as hhmm: 7/24 = 0700 (e1)
So basically I have d1=0700 ( formatted as 0000)
e1=0700 (formatted as hhmm)
I need something to the effect, such as, =if( d1=e1,'"",d1-e1)
I need to see that if those two numbers/times are different, the amount of
time difference, and if they're the same, I don't need anything ("")
When I do the above, I'm getting an incorrect result of -700. I think I
realize the problem is subtracting time and numbers/apples and orange.

Is this possible ?

Thanks for your patience.

"David Biddulph" wrote:

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

=IF(TEXT(D1,"0000")=TEXT(E1,"hhmm"),"",TEXT(TIME(I NT(D1/100),MOD(D1,100),0)-E1,"hhmm"))
if you want to format the result in the same way, as hhmm.
You'll need to be in 1904 date system if you want to deal with negative time
differences.
--
David Biddulph

"Steve" wrote in message
...
OK, thanks, however, I may have misspoke in my previous message.

I have a cell formatted as 0000 to show as 0700 (d1).
In e1, the results of a Vlookup that originally takes a general formatted
7, divides by 24, and then is formatted as hhmm: 7/24 = 0700 (e1)
So basically I have d1=0700 ( formatted as 0000)
e1=0700 (formatted as hhmm)
I need something to the effect, such as, =if( d1=e1,'"",d1-e1)
I need to see that if those two numbers/times are different, the amount of
time difference, and if they're the same, I don't need anything ("")
When I do the above, I'm getting an incorrect result of -700. I think I
realize the problem is subtracting time and numbers/apples and orange.

Is this possible ?

Thanks for your patience.

"David Biddulph" wrote:

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

Brilliant... worked like a charm.
Last question, hopefully.
I set the tools/options to 1904 date system, but can I use that option only
for this workbook ? Changing tools/options is global, isn't it ?

Thanks again,

Steve

"David Biddulph" wrote:

=IF(TEXT(D1,"0000")=TEXT(E1,"hhmm"),"",TEXT(TIME(I NT(D1/100),MOD(D1,100),0)-E1,"hhmm"))
if you want to format the result in the same way, as hhmm.
You'll need to be in 1904 date system if you want to deal with negative time
differences.
--
David Biddulph

"Steve" wrote in message
...
OK, thanks, however, I may have misspoke in my previous message.

I have a cell formatted as 0000 to show as 0700 (d1).
In e1, the results of a Vlookup that originally takes a general formatted
7, divides by 24, and then is formatted as hhmm: 7/24 = 0700 (e1)
So basically I have d1=0700 ( formatted as 0000)
e1=0700 (formatted as hhmm)
I need something to the effect, such as, =if( d1=e1,'"",d1-e1)
I need to see that if those two numbers/times are different, the amount of
time difference, and if they're the same, I don't need anything ("")
When I do the above, I'm getting an incorrect result of -700. I think I
realize the problem is subtracting time and numbers/apples and orange.

Is this possible ?

Thanks for your patience.

"David Biddulph" wrote:

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

Some ToolsOptions are global.....others are Workbook or Active Window only.

1904 date system is workbook only setting.


Gord Dibben MS Excel MVP


On Wed, 26 Sep 2007 12:29:00 -0700, Steve
wrote:

I set the tools/options to 1904 date system, but can I use that option only
for this workbook ? Changing tools/options is global, isn't it ?

And it even states it right there that it's workbook options.
Thanks all again for all your patience.

Steve

"Gord Dibben" wrote:

Some ToolsOptions are global.....others are Workbook or Active Window only.

1904 date system is workbook only setting.


Gord Dibben MS Excel MVP


On Wed, 26 Sep 2007 12:29:00 -0700, Steve
wrote:

I set the tools/options to 1904 date system, but can I use that option only
for this workbook ? Changing tools/options is global, isn't it ?