Changing a number into time

In my sheets for work i have basic formulaes to work out a basic time such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many packs
per minute (7) plus how much downtime (h140) plus the change over product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would not
need someone to look at it a go "oh thats so many hours and minutes", so that
it does it by use of a formulae.

Cheers James.

Hello James,

I don't really understand why you divide the number of packs per hour by the
number of packs per minute, shouldn't that always give a result of 60?

However, if your result of 13.85 is supposed to represent 13.85 minutes then
to convert to a time format multiply by 1440 [the number of mnutes in a day].
Given your formula that would be

=SUM(F140/7+H140+J140-60)*1440

make sure you format the result cell as [h]:mm to correctly show any totals
over 24 hours

"James" wrote:

In my sheets for work i have basic formulaes to work out a basic time such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many packs
per minute (7) plus how much downtime (h140) plus the change over product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would not
need someone to look at it a go "oh thats so many hours and minutes", so that
it does it by use of a formulae.

Cheers James.

Apologies....

of course I meant to say DIVIDE by 1440 so formula would be

=SUM(F140/7+H140+J140-60)/1440

"daddylonglegs" wrote:

Hello James,

I don't really understand why you divide the number of packs per hour by the
number of packs per minute, shouldn't that always give a result of 60?

However, if your result of 13.85 is supposed to represent 13.85 minutes then
to convert to a time format multiply by 1440 [the number of mnutes in a day].
Given your formula that would be

=SUM(F140/7+H140+J140-60)*1440

make sure you format the result cell as [h]:mm to correctly show any totals
over 24 hours

"James" wrote:

In my sheets for work i have basic formulaes to work out a basic time such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many packs
per minute (7) plus how much downtime (h140) plus the change over product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would not
need someone to look at it a go "oh thats so many hours and minutes", so that
it does it by use of a formulae.

Cheers James.

Hi and thaks for the reply.
I'm trying to work ou th unaccounted for downtime.
=SUM(F140/7+H140+J140-60)
This is (f140) is how many packs they have packed per hour, lets say 150
divided by 7 which how many packs the machine can do Plus (H140) which is
downtime, lets say in this case is 10minutes plus how long it took to change
over products, lets say 2minutes, then take away the 60minutes for the hour.
So we have this 150/7+10+2-60 = -26.57.
Which is (or should be) how long the machine was down due to unaccounted
downtime e.g. stopping the machine for 30secs to do this etc etc.
I need a formulae that would turn the end figure into a time figure of hours
minutes and seconds.

Thank again.

"daddylonglegs" wrote:

Hello James,

I don't really understand why you divide the number of packs per hour by the
number of packs per minute, shouldn't that always give a result of 60?

However, if your result of 13.85 is supposed to represent 13.85 minutes then
to convert to a time format multiply by 1440 [the number of mnutes in a day].
Given your formula that would be

=SUM(F140/7+H140+J140-60)*1440

make sure you format the result cell as [h]:mm to correctly show any totals
over 24 hours

"James" wrote:

In my sheets for work i have basic formulaes to work out a basic time such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many packs
per minute (7) plus how much downtime (h140) plus the change over product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would not
need someone to look at it a go "oh thats so many hours and minutes", so that
it does it by use of a formulae.

Cheers James.

And of course you don't need the SUM function.

=(F140/7+H140+J140-60)/1440 is enough.
--
David Biddulph

"daddylonglegs" wrote in message
...
Apologies....

of course I meant to say DIVIDE by 1440 so formula would be

=SUM(F140/7+H140+J140-60)/1440

"daddylonglegs" wrote:

Hello James,

I don't really understand why you divide the number of packs per hour by
the
number of packs per minute, shouldn't that always give a result of 60?

However, if your result of 13.85 is supposed to represent 13.85 minutes
then
to convert to a time format multiply by 1440 [the number of mnutes in a
day].
Given your formula that would be

=SUM(F140/7+H140+J140-60)*1440

make sure you format the result cell as [h]:mm to correctly show any
totals
over 24 hours

"James" wrote:

In my sheets for work i have basic formulaes to work out a basic time
such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many
packs
per minute (7) plus how much downtime (h140) plus the change over
product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would
not
need someone to look at it a go "oh thats so many hours and minutes",
so that
it does it by use of a formulae.

Cheers James.

Hi guy's

I think i have found a work around by just changing it to 0 decimal places.
This makes sense in the hourly figures. As it's easy to see that -3 would be
3minutes and -23 would be 23minutes.
At the end of the day it becomes a bit more complicated, it could be
107minutes. This is easy to understand in it's self. Yet I need a formulae
that would convert the 107 (which is a general number with no decimal places)
into a time which should be 1 Hour 47 minutes. Purely just for more people
to be able to just glance at it and understand it! I cant just divide it by
the 60 minutes (107/60) as it gives a return of 1.78.

Any help is very much apprecciated.

Cheers,

James

"James" wrote:

Hi and thaks for the reply.
I'm trying to work ou th unaccounted for downtime.
=SUM(F140/7+H140+J140-60)
This is (f140) is how many packs they have packed per hour, lets say 150
divided by 7 which how many packs the machine can do Plus (H140) which is
downtime, lets say in this case is 10minutes plus how long it took to change
over products, lets say 2minutes, then take away the 60minutes for the hour.
So we have this 150/7+10+2-60 = -26.57.
Which is (or should be) how long the machine was down due to unaccounted
downtime e.g. stopping the machine for 30secs to do this etc etc.
I need a formulae that would turn the end figure into a time figure of hours
minutes and seconds.

Thank again.

"daddylonglegs" wrote:

Hello James,

I don't really understand why you divide the number of packs per hour by the
number of packs per minute, shouldn't that always give a result of 60?

However, if your result of 13.85 is supposed to represent 13.85 minutes then
to convert to a time format multiply by 1440 [the number of mnutes in a day].
Given your formula that would be

=SUM(F140/7+H140+J140-60)*1440

make sure you format the result cell as [h]:mm to correctly show any totals
over 24 hours

"James" wrote:

In my sheets for work i have basic formulaes to work out a basic time such as

=SUM(F140/7+H140+J140-60)
Which is this the amount of packs per hour (f140) divided by how many packs
per minute (7) plus how much downtime (h140) plus the change over product
time (j140) take away 60.

Which gives a result of something like 13.85
Obvioulsy this is not in a time formatt.
At the end of the week it could look like 1363.99
How can i set the formulae to give me an actuall time so each one would not
need someone to look at it a go "oh thats so many hours and minutes", so that
it does it by use of a formulae.

Cheers James.

At the end of the day it becomes a bit more complicated, it could be
107minutes. This is easy to understand in it's self. Yet I need a
formulae
that would convert the 107 (which is a general number with no decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but it still dunna
work!!!
Grrrrr pulling my hair out here!


"Rick Rothstein (MVP - VB)" wrote:

At the end of the day it becomes a bit more complicated, it could be
107minutes. This is easy to understand in it's self. Yet I need a
formulae
that would convert the 107 (which is a general number with no decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

It sounds as if A1 doesn't contain a number, but instead contains some form
of text. What does =ISNUMBER(A1) show?
--
David Biddulph

"James" wrote in message
...
Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but it still dunna
work!!!
Grrrrr pulling my hair out here!


"Rick Rothstein (MVP - VB)" wrote:

At the end of the day it becomes a bit more complicated, it could be
107minutes. This is easy to understand in it's self. Yet I need a
formulae
that would convert the 107 (which is a general number with no decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

Wouldn't the A1/1440 part of my formula convert a "text number" into a real
number in order to perform the division?

Rick


"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
It sounds as if A1 doesn't contain a number, but instead contains some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

"James" wrote in message
...
Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but it still
dunna
work!!!
Grrrrr pulling my hair out here!


"Rick Rothstein (MVP - VB)" wrote:

At the end of the day it becomes a bit more complicated, it could be
107minutes. This is easy to understand in it's self. Yet I need a
formulae
that would convert the 107 (which is a general number with no decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick