Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Yes, so the text would have to contain more than just the number.
-- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Whatever i try doesn't give me what i want.
"Rick Rothstein (MVP - VB)" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
So what does =A1/1440 give, and exactly what does A1 contain, and (question
from earlier) what does =ISNUMBER(A1) show? -- David Biddulph "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
I think you are going to have to give us some additional information. If
there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
The cell that gives the 107 which is (k109) has the following formulae in it
=SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
I've got no where to post them to have a look but i' happily email them to
some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Hi
=A1/1440 gives me 0.74444 the other one gives me true. "David Biddulph" wrote: So what does =A1/1440 give, and exactly what does A1 contain, and (question from earlier) what does =ISNUMBER(A1) show? -- David Biddulph "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
You can send it to me... just remove the NOSPAM stuff out of my email
address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
That means that A1 is about 107.2, not 107.0, but there doesn't seem any
sensible reason why you should get #VALUE! from the =TEXT(A1/1440,"[h] ""hours"" mm ""minutes""") formula. Are you sure that your formula is referring to the correct cell, on the correct sheet? -- David Biddulph "James" wrote in message ... Hi =A1/1440 gives me 0.74444 the other one gives me true. "David Biddulph" wrote: So what does =A1/1440 give, and exactly what does A1 contain, and (question from earlier) what does =ISNUMBER(A1) show? -- David Biddulph "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Excell 2003
"Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Okay, I just got your spreadsheet.... you didn't tell us there was a minus
sign in front of the number 107... you can't have negative times. Use this formula instead of the one I gave you... =TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") If you want to show a minus sign in front of the formatted output, use this formula instead... =TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""") Rick "James" wrote in message ... Excell 2003 "Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!!
It alwasy turns out to be something so small doesn't it!! THANK YOU "Rick Rothstein (MVP - VB)" wrote: Okay, I just got your spreadsheet.... you didn't tell us there was a minus sign in front of the number 107... you can't have negative times. Use this formula instead of the one I gave you... =TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") If you want to show a minus sign in front of the formatted output, use this formula instead... =TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""") Rick "James" wrote in message ... Excell 2003 "Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
Actually, better still, use this formula and it will automatically adjust
for negative values and display the minus sign when necessary... =IF(K109<0,"-","")&TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, I just got your spreadsheet.... you didn't tell us there was a minus sign in front of the number 107... you can't have negative times. Use this formula instead of the one I gave you... =TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") If you want to show a minus sign in front of the formatted output, use this formula instead... =TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""") Rick "James" wrote in message ... Excell 2003 "Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
You are quite welcome. For future questions you might ask in these
newsgroups... if we ask you what value is display in a cell, and the value is negative, tell us it is negative... don't refer to it as a positive number even if that is how you "think" of it (for example, as a deficit interval). Rick "James" wrote in message ... THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!! It alwasy turns out to be something so small doesn't it!! THANK YOU "Rick Rothstein (MVP - VB)" wrote: Okay, I just got your spreadsheet.... you didn't tell us there was a minus sign in front of the number 107... you can't have negative times. Use this formula instead of the one I gave you... =TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") If you want to show a minus sign in front of the formatted output, use this formula instead... =TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""") Rick "James" wrote in message ... Excell 2003 "Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a number into time
I never even thought of that!
Small things bring us down eh. "Rick Rothstein (MVP - VB)" wrote: You are quite welcome. For future questions you might ask in these newsgroups... if we ask you what value is display in a cell, and the value is negative, tell us it is negative... don't refer to it as a positive number even if that is how you "think" of it (for example, as a deficit interval). Rick "James" wrote in message ... THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!! It alwasy turns out to be something so small doesn't it!! THANK YOU "Rick Rothstein (MVP - VB)" wrote: Okay, I just got your spreadsheet.... you didn't tell us there was a minus sign in front of the number 107... you can't have negative times. Use this formula instead of the one I gave you... =TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""") If you want to show a minus sign in front of the formatted output, use this formula instead... =TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""") Rick "James" wrote in message ... Excell 2003 "Rick Rothstein (MVP - VB)" wrote: You can send it to me... just remove the NOSPAM stuff out of my email address. By the way, I don't remember if you posted this or not... what version of Excel are you using? Rick "James" wrote in message ... I've got no where to post them to have a look but i' happily email them to some-one to have a gander! I can do basic formulaes but i'm stuggling with this one! "James" wrote: The cell that gives the 107 which is (k109) has the following formulae in it =SUM(K99:K108) its a bisic number cell with no decimal places. "Rick Rothstein (MVP - VB)" wrote: I think you are going to have to give us some additional information. If there is a formula in the cell with the 107 in it, copy/paste it into a response to us. Also, tell us exactly what is displayed in the cell (and what side of the cell it is on... left or right side). If there is a formula in the cell, tell us what is in any referenced cells (that is, if your formula uses C5 in it, tell us what is in C5). Right-click the cell and select Format Cells from the popup menu... what item in the Category list (on the Number tab) is selected? If Custom, tell us what custom format string is shown in the Type field. Oh, and is this cell part of a merged set of cells? You could also tell us anything else you might have done "out of the ordinary" to the cell. Better yet would be if you could put the spreadsheet up on a webpage somewhere so that we could download it and look at it directly (of course, if you do this, tell us what cell or cells we should be looking at). Rick "James" wrote in message ... Whatever i try doesn't give me what i want. "Rick Rothstein (MVP - VB)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round number to the thousands without changing underlying number | Excel Discussion (Misc queries) | |||
Changing Time To Different Timezones | New Users to Excel | |||
Changing many references at a time. | Excel Discussion (Misc queries) | |||
Changing format of number without changing the value | Excel Discussion (Misc queries) | |||
stop number font size from changing each time i click on somethin. | Charts and Charting in Excel |