Decimal to 24 hour clock please.

[Max]

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Steved]

Thanks Harlan.

"Harlan Grove" wrote:

> "Steved" <[email protected]> wrote...
> >Hello from Steved
> >
> >1.43 to 1343
> >9.52 to 2152
> >3.17 to 1517
> >I have the above in decimal please a formula to 24hour clock as above
> >
> >ie 3.17 to become 1517 not 15.17
>
> Far & away the shortest and fastest way would be
>
> =(x+12)*100
>
>
>

[Max]

"Biff" <[email protected]> wrote
> Don't feel bad.

Not at all <g>. But of course, I must be accountable for the earlier goofy,
flawed attempts to help the OP. Harlan was spot on with his suggestion to
the OP and his descripts/comments on my earlier efforts. All are accepted
in good spirit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Don't feel bad.

I once created an uber formula (that did work) and Daniel M. did the same
thing to me!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "Biff" <[email protected]> wrote
> ...
>> >Your approach is so flawed it's breathtaking.
>> Nice one! <vbg>
> I'll second that ! <vbg>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

"Biff" <[email protected]> wrote
....
> >Your approach is so flawed it's breathtaking.
> Nice one! <vbg>
I'll second that ! <vbg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g>)
Pl see responses given in the other branches.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

>Your approach is so flawed it's breathtaking.

Nice one! <vbg>

Biff

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Max" <[email protected]> wrote...
>>Sorry, further testing reveals that the earlier revised formula is still
> not
>>robust enough. Try this 2nd revision below:
> ...
>>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>>/(24*60),"h:mm"))+0.5))
> ...
>
> You're completely missing the point. You're fixated on returning Excel
> time
> values when the OP needs either integers or numeric strings that look like
> integers. And he needs them as cell *VALUES*, not what's displayed.
>
> Even if the OP needed time values, you've still completely missed the
> point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")
>
> Your approach is so flawed it's breathtaking.
>
>

[Max]

"Harlan Grove" <[email protected]> wrote
....
> Your approach is so flawed it's breathtaking.

Yes, I know. Thanks, Harlan.
You probably just missed my post to trash it all ..

> Even if the OP needed time values, you've still completely missed the
point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")

Point noted, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

"Max" <[email protected]> wrote...
>Sorry, further testing reveals that the earlier revised formula is still
not
>robust enough. Try this 2nd revision below:
....
>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>/(24*60),"h:mm"))+0.5))
....

You're completely missing the point. You're fixated on returning Excel time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

[Max]

urgh .. trash* all earlier suggestions, please.
(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)

See Harlan's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* it all, please.

See Harlan's offering ..

(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

2nd revision ..

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Sorry, further testing reveals that the earlier revised formula is still not
robust enough. Try this 2nd revision below:

Assuming the decimals are in A1 down

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Max from Steved
>
> I need 6.30 to be 1830
>
> Your Formula gives me 0.252083333
>
> The reason for 1830 is that oracle understands 1830 is 6:30 pm
>
> Hopefully you can work this out for me and thankyou.

[Harlan Grove]

"Steved" <[email protected]> wrote...
>Hello from Steved
>
>1.43 to 1343
>9.52 to 2152
>3.17 to 1517
>I have the above in decimal please a formula to 24hour clock as above
>
>ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100

[Biff]

Hi!

Maybe something like this:

=(INT(A1)+12&MOD(A1,INT(A1))*100)*1

Format as GENERAL

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Hi Max!

Not working for me.

The formula is returning the decimal equivalents and when formatted as TIME
13:30 displays as TIME AM

1:43
9:52
3:17

If I add 12 hrs it works!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
>> Hello from Steved
>>
>> 1.43 to 1343
>> 9.52 to 2152
>> 3.17 to 1517
>> I have the above in decimal please a formula to 24hour clock as above
>>
>> ie 3.17 to become 1517 not 15.17
>>
>> Thankyou
>
>

[Max]

I've re-posted the revised formula in the other response, re:

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down

That should do it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

ugh, sorry, scratch that suggestion ..
mis-read your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Steved]

Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

"Max" wrote:

> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > 1.43 to 1343
> > 9.52 to 2152
> > 3.17 to 1517
> > I have the above in decimal please a formula to 24hour clock as above
> >
> > ie 3.17 to become 1517 not 15.17
> >
> > Thankyou
>
>
>

[Max]

This seems to do it ..

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Max]

Sorry, further testing reveals that the earlier revised formula is still not
robust enough. Try this 2nd revision below:

Assuming the decimals are in A1 down

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Max from Steved
>
> I need 6.30 to be 1830
>
> Your Formula gives me 0.252083333
>
> The reason for 1830 is that oracle understands 1830 is 6:30 pm
>
> Hopefully you can work this out for me and thankyou.

[Steved]

Thanks Harlan.

"Harlan Grove" wrote:

> "Steved" <[email protected]> wrote...
> >Hello from Steved
> >
> >1.43 to 1343
> >9.52 to 2152
> >3.17 to 1517
> >I have the above in decimal please a formula to 24hour clock as above
> >
> >ie 3.17 to become 1517 not 15.17
>
> Far & away the shortest and fastest way would be
>
> =(x+12)*100
>
>
>

[Max]

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Max]

ugh, sorry, scratch that suggestion ..
mis-read your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

This seems to do it ..

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Steved]

Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

"Max" wrote:

> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > 1.43 to 1343
> > 9.52 to 2152
> > 3.17 to 1517
> > I have the above in decimal please a formula to 24hour clock as above
> >
> > ie 3.17 to become 1517 not 15.17
> >
> > Thankyou
>
>
>

[Max]

I've re-posted the revised formula in the other response, re:

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down

That should do it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Hi Max!

Not working for me.

The formula is returning the decimal equivalents and when formatted as TIME
13:30 displays as TIME AM

1:43
9:52
3:17

If I add 12 hrs it works!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
>> Hello from Steved
>>
>> 1.43 to 1343
>> 9.52 to 2152
>> 3.17 to 1517
>> I have the above in decimal please a formula to 24hour clock as above
>>
>> ie 3.17 to become 1517 not 15.17
>>
>> Thankyou
>
>

[Biff]

Hi!

Maybe something like this:

=(INT(A1)+12&MOD(A1,INT(A1))*100)*1

Format as GENERAL

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Harlan Grove]

"Steved" <[email protected]> wrote...
>Hello from Steved
>
>1.43 to 1343
>9.52 to 2152
>3.17 to 1517
>I have the above in decimal please a formula to 24hour clock as above
>
>ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100

[Max]

2nd revision ..

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* it all, please.

See Harlan's offering ..

(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* all earlier suggestions, please.
(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)

See Harlan's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

"Max" <[email protected]> wrote...
>Sorry, further testing reveals that the earlier revised formula is still
not
>robust enough. Try this 2nd revision below:
....
>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>/(24*60),"h:mm"))+0.5))
....

You're completely missing the point. You're fixated on returning Excel time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

[Max]

"Harlan Grove" <[email protected]> wrote
....
> Your approach is so flawed it's breathtaking.

Yes, I know. Thanks, Harlan.
You probably just missed my post to trash it all ..

> Even if the OP needed time values, you've still completely missed the
point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")

Point noted, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

>Your approach is so flawed it's breathtaking.

Nice one! <vbg>

Biff

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Max" <[email protected]> wrote...
>>Sorry, further testing reveals that the earlier revised formula is still
> not
>>robust enough. Try this 2nd revision below:
> ...
>>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>>/(24*60),"h:mm"))+0.5))
> ...
>
> You're completely missing the point. You're fixated on returning Excel
> time
> values when the OP needs either integers or numeric strings that look like
> integers. And he needs them as cell *VALUES*, not what's displayed.
>
> Even if the OP needed time values, you've still completely missed the
> point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")
>
> Your approach is so flawed it's breathtaking.
>
>

[Max]

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g>)
Pl see responses given in the other branches.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Biff" <[email protected]> wrote
....
> >Your approach is so flawed it's breathtaking.
> Nice one! <vbg>
I'll second that ! <vbg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Don't feel bad.

I once created an uber formula (that did work) and Daniel M. did the same
thing to me!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "Biff" <[email protected]> wrote
> ...
>> >Your approach is so flawed it's breathtaking.
>> Nice one! <vbg>
> I'll second that ! <vbg>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

"Biff" <[email protected]> wrote
> Don't feel bad.

Not at all <g>. But of course, I must be accountable for the earlier goofy,
flawed attempts to help the OP. Harlan was spot on with his suggestion to
the OP and his descripts/comments on my earlier efforts. All are accepted
in good spirit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Hi Max!

Not working for me.

The formula is returning the decimal equivalents and when formatted as TIME
13:30 displays as TIME AM

1:43
9:52
3:17

If I add 12 hrs it works!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
>> Hello from Steved
>>
>> 1.43 to 1343
>> 9.52 to 2152
>> 3.17 to 1517
>> I have the above in decimal please a formula to 24hour clock as above
>>
>> ie 3.17 to become 1517 not 15.17
>>
>> Thankyou
>
>

[Max]

urgh .. trash* all earlier suggestions, please.
(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)

See Harlan's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* it all, please.

See Harlan's offering ..

(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

2nd revision ..

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Harlan Grove" <[email protected]> wrote
....
> Your approach is so flawed it's breathtaking.

Yes, I know. Thanks, Harlan.
You probably just missed my post to trash it all ..

> Even if the OP needed time values, you've still completely missed the
point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")

Point noted, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Sorry, further testing reveals that the earlier revised formula is still not
robust enough. Try this 2nd revision below:

Assuming the decimals are in A1 down

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Max from Steved
>
> I need 6.30 to be 1830
>
> Your Formula gives me 0.252083333
>
> The reason for 1830 is that oracle understands 1830 is 6:30 pm
>
> Hopefully you can work this out for me and thankyou.

[Harlan Grove]

"Steved" <[email protected]> wrote...
>Hello from Steved
>
>1.43 to 1343
>9.52 to 2152
>3.17 to 1517
>I have the above in decimal please a formula to 24hour clock as above
>
>ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100

[Biff]

>Your approach is so flawed it's breathtaking.

Nice one! <vbg>

Biff

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Max" <[email protected]> wrote...
>>Sorry, further testing reveals that the earlier revised formula is still
> not
>>robust enough. Try this 2nd revision below:
> ...
>>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>>/(24*60),"h:mm"))+0.5))
> ...
>
> You're completely missing the point. You're fixated on returning Excel
> time
> values when the OP needs either integers or numeric strings that look like
> integers. And he needs them as cell *VALUES*, not what's displayed.
>
> Even if the OP needed time values, you've still completely missed the
> point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")
>
> Your approach is so flawed it's breathtaking.
>
>

[Biff]

Hi!

Maybe something like this:

=(INT(A1)+12&MOD(A1,INT(A1))*100)*1

Format as GENERAL

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Max]

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g>)
Pl see responses given in the other branches.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Steved]

Thanks Harlan.

"Harlan Grove" wrote:

> "Steved" <[email protected]> wrote...
> >Hello from Steved
> >
> >1.43 to 1343
> >9.52 to 2152
> >3.17 to 1517
> >I have the above in decimal please a formula to 24hour clock as above
> >
> >ie 3.17 to become 1517 not 15.17
>
> Far & away the shortest and fastest way would be
>
> =(x+12)*100
>
>
>

[Max]

I've re-posted the revised formula in the other response, re:

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down

That should do it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Biff" <[email protected]> wrote
....
> >Your approach is so flawed it's breathtaking.
> Nice one! <vbg>
I'll second that ! <vbg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Steved]

Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

"Max" wrote:

> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > 1.43 to 1343
> > 9.52 to 2152
> > 3.17 to 1517
> > I have the above in decimal please a formula to 24hour clock as above
> >
> > ie 3.17 to become 1517 not 15.17
> >
> > Thankyou
>
>
>

[Biff]

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Max]

This seems to do it ..

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Don't feel bad.

I once created an uber formula (that did work) and Daniel M. did the same
thing to me!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "Biff" <[email protected]> wrote
> ...
>> >Your approach is so flawed it's breathtaking.
>> Nice one! <vbg>
> I'll second that ! <vbg>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

ugh, sorry, scratch that suggestion ..
mis-read your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

"Max" <[email protected]> wrote...
>Sorry, further testing reveals that the earlier revised formula is still
not
>robust enough. Try this 2nd revision below:
....
>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>/(24*60),"h:mm"))+0.5))
....

You're completely missing the point. You're fixated on returning Excel time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

[Max]

"Biff" <[email protected]> wrote
> Don't feel bad.

Not at all <g>. But of course, I must be accountable for the earlier goofy,
flawed attempts to help the OP. Harlan was spot on with his suggestion to
the OP and his descripts/comments on my earlier efforts. All are accepted
in good spirit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Sorry, further testing reveals that the earlier revised formula is still not
robust enough. Try this 2nd revision below:

Assuming the decimals are in A1 down

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Max from Steved
>
> I need 6.30 to be 1830
>
> Your Formula gives me 0.252083333
>
> The reason for 1830 is that oracle understands 1830 is 6:30 pm
>
> Hopefully you can work this out for me and thankyou.

[Steved]

Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

[Max]

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Max]

ugh, sorry, scratch that suggestion ..
mis-read your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

This seems to do it ..

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Biff]

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Steved]

Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

"Max" wrote:

> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > 1.43 to 1343
> > 9.52 to 2152
> > 3.17 to 1517
> > I have the above in decimal please a formula to 24hour clock as above
> >
> > ie 3.17 to become 1517 not 15.17
> >
> > Thankyou
>
>
>

[Max]

I've re-posted the revised formula in the other response, re:

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down

That should do it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Hi Max!

Not working for me.

The formula is returning the decimal equivalents and when formatted as TIME
13:30 displays as TIME AM

1:43
9:52
3:17

If I add 12 hrs it works!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> Assuming the decimals are in A1 down
>
> Put in B1:
>
> =IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
> (24*60))
>
> Format B1 as Time, Type:"13:30", and copy down
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
>> Hello from Steved
>>
>> 1.43 to 1343
>> 9.52 to 2152
>> 3.17 to 1517
>> I have the above in decimal please a formula to 24hour clock as above
>>
>> ie 3.17 to become 1517 not 15.17
>>
>> Thankyou
>
>

[Biff]

Hi!

Maybe something like this:

=(INT(A1)+12&MOD(A1,INT(A1))*100)*1

Format as GENERAL

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> 1.43 to 1343
> 9.52 to 2152
> 3.17 to 1517
> I have the above in decimal please a formula to 24hour clock as above
>
> ie 3.17 to become 1517 not 15.17
>
> Thankyou

[Harlan Grove]

"Steved" <[email protected]> wrote...
>Hello from Steved
>
>1.43 to 1343
>9.52 to 2152
>3.17 to 1517
>I have the above in decimal please a formula to 24hour clock as above
>
>ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100

[Steved]

Thanks Harlan.

"Harlan Grove" wrote:

> "Steved" <[email protected]> wrote...
> >Hello from Steved
> >
> >1.43 to 1343
> >9.52 to 2152
> >3.17 to 1517
> >I have the above in decimal please a formula to 24hour clock as above
> >
> >ie 3.17 to become 1517 not 15.17
>
> Far & away the shortest and fastest way would be
>
> =(x+12)*100
>
>
>

[Max]

2nd revision ..

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* it all, please.

See Harlan's offering ..

(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

urgh .. trash* all earlier suggestions, please.
(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg>)

See Harlan's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

"Max" <[email protected]> wrote...
>Sorry, further testing reveals that the earlier revised formula is still
not
>robust enough. Try this 2nd revision below:
....
>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>/(24*60),"h:mm"))+0.5))
....

You're completely missing the point. You're fixated on returning Excel time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

[Max]

"Harlan Grove" <[email protected]> wrote
....
> Your approach is so flawed it's breathtaking.

Yes, I know. Thanks, Harlan.
You probably just missed my post to trash it all ..

> Even if the OP needed time values, you've still completely missed the
point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")

Point noted, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

>Your approach is so flawed it's breathtaking.

Nice one! <vbg>

Biff

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Max" <[email protected]> wrote...
>>Sorry, further testing reveals that the earlier revised formula is still
> not
>>robust enough. Try this 2nd revision below:
> ...
>>=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1,2))=1,
>>(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
>>SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
>>SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
>>/(24*60),"h:mm"))+0.5))
> ...
>
> You're completely missing the point. You're fixated on returning Excel
> time
> values when the OP needs either integers or numeric strings that look like
> integers. And he needs them as cell *VALUES*, not what's displayed.
>
> Even if the OP needed time values, you've still completely missed the
> point.
> If hours are separated from minutes by a period, all that's needed to
> convert to time in PM is
>
> =--SUBSTITUTE(x+12,".",":")
>
> Your approach is so flawed it's breathtaking.
>
>

[Max]

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g>)
Pl see responses given in the other branches.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Biff" <[email protected]> wrote
....
> >Your approach is so flawed it's breathtaking.
> Nice one! <vbg>
I'll second that ! <vbg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Don't feel bad.

I once created an uber formula (that did work) and Daniel M. did the same
thing to me!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "Biff" <[email protected]> wrote
> ...
>> >Your approach is so flawed it's breathtaking.
>> Nice one! <vbg>
> I'll second that ! <vbg>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

"Biff" <[email protected]> wrote
> Don't feel bad.

Not at all <g>. But of course, I must be accountable for the earlier goofy,
flawed attempts to help the OP. Harlan was spot on with his suggestion to
the OP and his descripts/comments on my earlier efforts. All are accepted
in good spirit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----