convert & multiply (number & time)formulas

[targueta]

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a cell
that gives me 0.84 and I need to covert this to hours: minutes (what should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what should be
256:00)
Can any one help? Thanks in advance

 

[MartinW]

Hi Targueta,

0.84 of an hour doesnt make 48 mins.

With 0.84 in A1
In B1 put =A1/24 (and format as custom [h]:mm:ss)
will show as 0:50:24
In C1 put =B1*320 (and format as custom [h]:mm:ss)
will show as 268:48:00

Is that something you can work with?

HTH
Martin

 

[Ashish Mathur]

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[targueta]

thanks I apreciate the help

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what should
be
256:00)
Can any one help? Thanks in advance

 

[targueta]

Thanks I works perfectly

Hi Targueta,

0.84 of an hour doesnt make 48 mins.

With 0.84 in A1
In B1 put =A1/24 (and format as custom [h]:mm:ss)
will show as 0:50:24
In C1 put =B1*320 (and format as custom [h]:mm:ss)
will show as 268:48:00

Is that something you can work with?

HTH
Martin

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a cell
that gives me 0.84 and I need to covert this to hours: minutes (what should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what should be
256:00)
Can any one help? Thanks in advance

 

[David Biddulph]

You will have realised, I hope, that Ashish's formula converts your 0.84
into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24 in
h:mm:ss.
--
David Biddulph

thanks I apreciate the help

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a
cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance

 

[MartinW]

Glad to hear that. Thanks for posting back.

Regards
Martin

Thanks I works perfectly

Hi Targueta,

0.84 of an hour doesnt make 48 mins.

With 0.84 in A1
In B1 put =A1/24 (and format as custom [h]:mm:ss)
will show as 0:50:24
In C1 put =B1*320 (and format as custom [h]:mm:ss)
will show as 268:48:00

Is that something you can work with?

HTH
Martin

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a
cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what
should be
256:00)
Can any one help? Thanks in advance

 

[MartinW]

That's a very strange post David. What does it mean?

Signed
Other User

You will have realised, I hope, that Ashish's formula converts your 0.84
into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24 in
h:mm:ss.
--
David Biddulph

thanks I apreciate the help

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a
cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance

 

[Peo Sjoblom]

What is strange about it? Your interpretation is the same as mine and
David's
that 0.84 hour = 50 minutes and 24 seconds whereas the other formula
incorrectly returns 1 hours and 24 minutes as a text value

--


Regards,


Peo Sjoblom

That's a very strange post David. What does it mean?

Signed
Other User

You will have realised, I hope, that Ashish's formula converts your 0.84
into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24 in
h:mm:ss.
--
David Biddulph

thanks I apreciate the help

:

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have a
cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance

 

[David Biddulph]

To be honest, Peo, I wasn't saying that Ashish's interpretation was
incorrect, merely that it was different from what some of the rest of us had
assumed. The OP's intention wasn't clear, as his example wasn't internally
consistent.

My guess would have agreed with Martin's (and yours), but the OP hasn't
confirmed what was wanted and may not have realised the difference (which
was why I posted what I had *hoped* was clarification).
--
David Biddulph

What is strange about it? Your interpretation is the same as mine and
David's
that 0.84 hour = 50 minutes and 24 seconds whereas the other formula
incorrectly returns 1 hours and 24 minutes as a text value

That's a very strange post David. What does it mean?

You will have realised, I hope, that Ashish's formula converts your 0.84
into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24 in
h:mm:ss.
--
David Biddulph
thanks I apreciate the help

:

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")
I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have
a cell
that gives me 0.84 and I need to covert this to hours: minutes (what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance

 

[Peo Sjoblom]

Well the OP responded back to Martin that it worked perfectly so I assume
he got it right. I don't see how you can get 1:24 from 0.84 in a logical
way. The formula
is just replacing the period with a colon.

=TEXT(SUBSTITUTE(A1,".",":"),"[h]:mm:ss")

will return the same and I fail to see any logic in that, that's all.

--


Regards,


Peo Sjoblom

To be honest, Peo, I wasn't saying that Ashish's interpretation was
incorrect, merely that it was different from what some of the rest of us
had assumed. The OP's intention wasn't clear, as his example wasn't
internally consistent.

My guess would have agreed with Martin's (and yours), but the OP hasn't
confirmed what was wanted and may not have realised the difference (which
was why I posted what I had *hoped* was clarification).
--
David Biddulph

What is strange about it? Your interpretation is the same as mine and
David's
that 0.84 hour = 50 minutes and 24 seconds whereas the other formula
incorrectly returns 1 hours and 24 minutes as a text value

That's a very strange post David. What does it mean?
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
You will have realised, I hope, that Ashish's formula converts your
0.84 into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24
in h:mm:ss.
--
David Biddulph
thanks I apreciate the help

:

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")
I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I have
a cell
that gives me 0.84 and I need to covert this to hours: minutes
(what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by
a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance

 

[MartinW]

OK I'm still confused, but it is getting clearer.
I can't see Peo's first response, either in Outlook Express or on the
microsoft
web interface, I was intrigued why you would refer to me as other users
when,
as far as I could tell, mine was the only other response. I still can't see
Peo's
first response although I can see his second and third. (Hopefully there
isn't any more)

Obviously, what I percieved as a rather rude post, was nothing of the kind,
my apologies for that David.

I still can't work out how Ashish came to his interpretation of the OP's
question, or your explanation of it, but I do know how easy it is to
go off on the wrong tangent, especially when the original question
is ambiguous, unclear, or, as in this instance, slightly flawed.
I've done that many times in the past and will no doubt do
it again in the future.


Regards
Martin

Well the OP responded back to Martin that it worked perfectly so I assume
he got it right. I don't see how you can get 1:24 from 0.84 in a logical
way. The formula
is just replacing the period with a colon.

=TEXT(SUBSTITUTE(A1,".",":"),"[h]:mm:ss")

will return the same and I fail to see any logic in that, that's all.

--


Regards,


Peo Sjoblom

To be honest, Peo, I wasn't saying that Ashish's interpretation was
incorrect, merely that it was different from what some of the rest of us
had assumed. The OP's intention wasn't clear, as his example wasn't
internally consistent.

My guess would have agreed with Martin's (and yours), but the OP hasn't
confirmed what was wanted and may not have realised the difference (which
was why I posted what I had *hoped* was clarification).
--
David Biddulph

What is strange about it? Your interpretation is the same as mine and
David's
that 0.84 hour = 50 minutes and 24 seconds whereas the other formula
incorrectly returns 1 hours and 24 minutes as a text value

That's a very strange post David. What does it mean?

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
You will have realised, I hope, that Ashish's formula converts your
0.84 into 0:84:00, in other words 1:24:00 in h:mm:ss.

Other users have assumed that by 0.84 you mean 0.84 hours, or 0:50:24
in h:mm:ss.

thanks I apreciate the help

:

Hi,

Try this as the answer to the first question

TEXT(MID(A1,1,FIND(".",A1,1)-1)&":"&MID(A1,FIND(".",A1,1)+1,10),"[hh]:mm")

I need help with a couple of issues in the same table.
-the first one is: how to convert numbers to time; right now I
have a cell
that gives me 0.84 and I need to covert this to hours: minutes
(what
should
be 0:48)
-the second one is: I need to multiply the previous time (0:48) by
a
Quantity (example: 320) and get the result in hours: minutes (what
should
be
256:00)
Can any one help? Thanks in advance