convert & multiply (number & time)formulas
 

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

convert & multiply (number & time)formulas
 

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



"targueta" wrote:

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

convert & multiply (number & time)formulas
 

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" wrote in message
...
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

convert & multiply (number & time)formulas
 

thanks I apreciate the help

"Ashish Mathur" wrote:

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" wrote in message
...
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

convert & multiply (number & time)formulas
 

Thanks I works perfectly

"MartinW" wrote:

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



"targueta" wrote:

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

convert & multiply (number & time)formulas
 

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


"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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" wrote in message
...
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

convert & multiply (number & time)formulas
 

Glad to hear that. Thanks for posting back.

Regards
Martin


"targueta" wrote in message
...
Thanks I works perfectly

"MartinW" wrote:

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



"targueta" wrote:

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

convert & multiply (number & time)formulas
 

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

Signed
Other User


"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


"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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" wrote in message
...
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

convert & multiply (number & time)formulas
 

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

"MartinW" wrote in message
...
That's a very strange post David. What does it mean?

Signed
Other User


"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


"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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" wrote in message
...
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

convert & multiply (number & time)formulas
 

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

"Peo Sjoblom" wrote in message
...
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

"MartinW" wrote in message
...
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

"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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")

"targueta" wrote in message
...
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

convert & multiply (number & time)formulas
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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

"Peo Sjoblom" wrote in message
...
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

"MartinW" wrote in message
...
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

"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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")

"targueta" wrote in message
...
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

convert & multiply (number & time)formulas
 

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



"Peo Sjoblom" wrote in message
...
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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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

"Peo Sjoblom" wrote in message
...
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

"MartinW" wrote in message
...
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

"targueta" wrote in message
...
thanks I apreciate the help

"Ashish Mathur" wrote:

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")

"targueta" wrote in message
...
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