dividing a number into hours and minutes

My users are entering numbers into column B.  Is it possible to take the 
total of column B (say Row X) and divide it into hours and minutes putting 
the hours in Row X Column D and minuntes in Row X Column E (two seperate 
cells)?  They are entering just numbers in multiples of 5 and usually not 
more than 30 minutes.  Do I need to convert Column B to some form of 'time' 
to get close to the result I'm looking for?  Is this even doable?
0
Utf
11/17/2009 10:59:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1710 Views

Similar Articles

[PageSpeed] 36

Try this...

In D1, enter   =INT(B1/60)
In E1, enter   =MOD(B1,60)

In this example, Row X is row 1.

Hope this helps,

Hutch

"ramona" wrote:

> My users are entering numbers into column B.  Is it possible to take the 
> total of column B (say Row X) and divide it into hours and minutes putting 
> the hours in Row X Column D and minuntes in Row X Column E (two seperate 
> cells)?  They are entering just numbers in multiples of 5 and usually not 
> more than 30 minutes.  Do I need to convert Column B to some form of 'time' 
> to get close to the result I'm looking for?  Is this even doable?
0
Utf
11/18/2009 3:20:01 AM
Reply:

Similar Artilces:

Automatic divided by 100000
Hi all, On a new worksheet, when I type in a number in the cell, right after I hit "Enter", it automatic divide the number by 100,000, why this happened? Please help. Thanks. Gian Tools>options>edit and uncheck fixed decimal places Regards, Peo Sjoblom "Gian" wrote: > Hi all, > > On a new worksheet, when I type in a number in the cell, right after I hit > "Enter", it automatic divide the number by 100,000, why this happened? > Please help. > > Thanks. > > Gian Thanks. "Peo Sjoblom" wrote: > Tools>...

How do I print odd numbered pages
I want to print double sided. The even numbers on one side and the odds on the other. Is there a way to do this in Excel Maryjanet, check your printer settings there may be an option there to do it, if not here is a macro from Gord Dibben that will do it Sub PrintDoubleSided() 'Will print odd or even pages 'By Gord Dibben Dim Totalpages As Long Dim pg As Long Dim oddoreven As Integer On Error GoTo enditt Totalpages = ExecuteExcel4Macro("Get.Document(50)") oddoreven = InputBox("Enter 1 for Odd, 2 for Even") For pg = oddoreven To Totalpages Step 2 ActiveWindow.S...

Revision Number
Hello: When I right click on an excel file in windows explorer and click on properties. I will go to the Summary Tab and be in advanced view. I can see Revision Number. I never see that change. How does that work? Thanks, Mark - Using XP Pro SP2 and Excel 2002 (XP) Microsoft explains all: http://support.microsoft.com/default.aspx?scid=KB;en-us;q214393 Wait! I'll spare you the time: >>CAUSE This behavior occurs because Microsoft Excel does not use either of these statistics. MORE INFORMATION The Properties dialog box is shared by Microsoft Office programs. Some M...

How do I stop a formula from rounding up to the next number
What I am trying to do is take a cell and find 6.2% of it by =(SUM(B19*C19)) then adding the cells to other cells accross without it rounding up to the next higher number. This is for payroll deductions. Change the cell format to NUMBER and however decimal places you want Right click on the cell and FORMAT should be an option -- LB7 ----------------------------------------------------------------------- LB79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1215 View this thread: http://www.excelforum.com/showthread.php?threadid=26723 Try =ROUNDDOWN(B19*c19...

Inventory numbers
I'm trying to create a spreadsheet that will automatically input a new item number when I enter a new item on a list. Easy enough, but the catch is I want the item number to remain assoicated with the item regardless of sorting. In the classic Excel Help file manner I will explain what I want. Current on screen data A\B\C 1\Oranges\52 2\Apples\34 3\Bananas\97 If I sort by column B: A\B\C 2\Apples\34 3\Bananas\97 1\Oranges\52 Now I add another item (cherries\63) to the next available row (B5). I want my output to look like this: (Note the number 4 is automated) A\B\C 2\Apples\34 3\...

Convert Numbers to Text with a Source Code list
I have a column of data with numbers relating to specific industries. Each row corresponds to an individual and their affiliated industries. Some cells contain 1 number and some contain several (separated by commas). I would like to break this column apart into each individual industry for separate columns. The corresponding person would have an x in the column that refers to the industry they relate to. I have a separate sheet for what each code (number) means. HELP! Have you checked out Data -> Text to Columns... To split out your numbers? -- HTH... Jim Thomlinson ...

Avoid user having to enter 00 hours when using [mm]:ss format
I am formatting a game sheet for sports events. Time should be entered as mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a way to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds. It always assumes the user is entering hours and minutes. Thanks for any help SBB 60 seconds = 1 minute 60 minutes = 1 hour They both the same "numeric" value. Since you're locking the minutes to prevent the roll-over to hours, How about *USING* hours and minutes, and lock the hours to prevent the roll-over to days? [hh]:mm You'll get...

Number fields will not accept values in CRM 3.0 09-25-06
I just installed CRM 3.0 and I'm having an issue where I enter a number in a number field such as price (contractdetail) and as soon as I click off it goes blank. Fields which are text seem to work fine. Anyone have any ideas or seen this before? Here are some additional infos: System/Environment Using Windows 2003 with SQL 2005 Does this happen with every number field? Yes, it happens with every number field in contractdetail Does it matter what user you are logged in as? No, it doesn't matter! Do you have custom number fields that this is happening to as well? Yes, it happens ...

IE8 Page numbering
I am using IE8 with XP Home Sp3. When printing out pages off the web, at the bottom of the page, I notice a &u& . How do you tell IE8 to print the page # instead of &u&. Thanks IE File | Page Setup | ... Charles Eaves wrote: > I am using IE8 with XP Home Sp3. > When printing out pages off the web, at the bottom of the page, I notice a > &u& . > How do you tell IE8 to print the page # instead of &u&. ...

Next Cheque Number
Hi, Was wondering if anyone knows a way that the 'Next Cheque Number' in the Chequebook Maintenance can accidently be reset? I have a customer who says that the next number has changed and they are wondering how this could have happened as it would appear that no-one has changed it through the maintenance routine? Thanks -- Jean I tried different tests and couldn't replicate. I haven't had any reports from Customers over the years. -- Mick "JB" wrote: > Hi, > > Was wondering if anyone knows a way that the 'Next Cheque Number' in the >...

Pulling out right ID number
In a criteria form I have 10 comboboxes of which, if filled, its values are used as criteria in corresponding queries (on different linked tables) which all result in an independent set of the same type of ID numbers. Now I only want to pull out those ID numbers of the records that meet all criteria. With every solution I think of I end up with a lot of match queries and I assume there is an easier way. What would be the best way to do this? Thanks, Lars Hi Lars, have a look at this search form http://www.allenbrowne.com/ser-62.html It has a very easy and efficien...

Pivot
Hi, I have a pivot with Sales information. The data has multiple columns by various product category. I have added a count column next to each product column representing the dollar amount. Is there a way in Pivot to get a calculated field of Total amount / Count for each product to arrive at Average value per deal. Excel does not allow me to use Calculated Item to get the average number. Any suggestion is welcome. Regards Sandip. If you are unable to create the formula in the pivot table, have yo considered placing it in a row next to the table? Just a thought... -- SHMUNC ---------...

display leading zeros in telephone number
When I paste a list of telephone numbers, any leading zeros are not displayed. (eg. 0944.. shows as 944...) How can I display them corectly? Thanks Andy "Andy" <anonymous@discussions.microsoft.com> wrote in message news:08a701c3da94$2dd89710$a501280a@phx.gbl... > When I paste a list of telephone numbers, any leading > zeros are not displayed. (eg. 0944.. shows as 944...) How > can I display them corectly? > > Thanks > Andy Format the cells as text before pasting the data. >-----Original Message----- >"Andy" <anonymous@discussions....

Dividing text
I know about the text-to-columns function, but if I have a sentence of text with each word separated by a space, it'll split across as many columns as there are words in the source cell. If I want to split on a specific 3 character sequence (namely " - "), is there any way I can do so by using the various text functions to determine where in the sentence that exact sequence occurs, and splitting only at that point? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Hi Paul As you may have discovered, the 'TextToC...

PO Auto Numbering #2
We have been using auto numbering for our purchase orders. But we have found when we create a PO base on sales for a supplier we get (example PO # 0000431) and when we create a Blank PO we get (example PO # 9999587). When the RMS Manager informs us that the PO number is RMS will create one for you. How can we reset the auto numbering for PO's to a lower number for blank POs? I tried making a dummy PO with a lower number, but the auto numbering just continues with the much higher number. -- Jason Clements Pinch A Penny ===================================================== =========...

Row Number
I need hellp with a formula. In Cell E8 I need to write a formula that will search Column A rows 18 thru 32 and if it finds the date 09/16/2006 it will display the row number. Any help will be greatly appreciated. Thanks Dianne You didn't say what result you want if the date is not present. One way... A1 = some date =IF(COUNTIF(A18:A32,A1),INDEX(ROW(A18:A32),MATCH(A1,A18:A32,0)),"") If the date isn't present the formula returns blank. -- Biff Microsoft Excel MVP "Dianne" <Dianne@discussions.microsoft.com> wrote in message n...

Restart numbering ater a set number of rows
I have an excel spreadsheet with 4 columns. the first two columns contains numbers which will never change the third column starts off with 0 (zero) and is repeated for 255 rows then on the 256 row it needs to change to 1 (so basically increment by 1 every 255 rows) the fourth column starts at 0 (zero) and counts up to 255 but then needs to reset it self back to 0 on the 256 row. Help Please!!!!!:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: I...

Sales by hour
Can somebody share a report with me for an HQ environemnt which would show sales hour by hour? Thanks Phil I've got a report that does just that ... drop me a line at sbinder at venus.com and I'll be happy to share. "Philip Gass" wrote: > Can somebody share a report with me for an HQ environemnt which would show > sales hour by hour? > > Thanks > > Phil > > > Hi Philip, Retail Analytics from Professional ADvantage is designed to help retailers manage their business by delivering analytical reporting on business performance. Easi...

Find/Replace Numbers stored as Text drops zeros
This SEEMS to have changed when Office 2003 SP3 rolled out - as we USED to be able to do this. I'm not ruling out OE (Operator Error) - although we've tried this on different op. sys.; different computers; different Office 2003 installs, and, as stated above, we have been able to do this in the past.... We process millions of rows of data coming in from thousands of sources. While we provide the 'required' format information, invariably we have to 'groom' some data. Due to the fact that some cell entries can be text or number, the cells MUST be forma...

input mask for numbers
Hi to everyone, I'm looking for a way to create an input mask to insert decimal numbers like this: _____________.__ In the first part I should be able to write the number (at least 10 digits) and after the point, it is the decimal part, two numbers. At moment, on the table design mode I configured the field Data Type to "Number" and for the General tab I chose: Single, Standard and 3 respectively for Field Size, Format, Decimal Places. Is it possible to use the facility of the input mask in the form's text box and to manage a numeric data type at same time?How can I do this...

How to divide
Hi, I have a 40 page Word document in Turkish Language and another 40 page word document in English(translation Turkish document). I want to put them in Publisher as book fold style. But I donot know how to adjust the width of each column. Does anyone has any template or does anyone can tell me how to adjust the book fold page width? I thank you in advance. Best Regards, Niyazi In page setup, select booklet. Publisher will ask about 4 pages, click yes. Create a text box on page one, right-click the text box, click format text box, text box tab, click the columns button, input 2. Cop...

error number 0x80040107
Hi thanks for reading I have a customer who has had complete server failure, originally they were running SBS 2003. A new server has now installed with the SBS suite and due to the previous backup only mailboxes have been restored via Backup exec 9.1 Looking through the web many people seem to having issues with the offiline address book but there do not seem to be any solutions . is anybody able to offer advice and assistance ? many thanks ...

Date turns to a number
Hi I have a text field in a report with =Date() as the control source and format as medium date, the problem I have is when I send the report to Excel it turns the date into a number how do I stop this happening? Thanks Bob Just format the cell as a Date. Under the covers, VBA dates are eight byte floating point numbers, where the integer portion is the date as the number of days relative to 30 Dec, 1899 and the decimal portion is the time as a fraction of a day. Today is 27 Oct, 2007: if you ask Access for Format(Date, "0"), it will return 39382. If you plug that number ...

comparing a number to a list of numbers
I have a column of numbers and a single number in a seperate cell. I would like to know what functions I could use where I compare th single number to the column of numbers and return that number whic closest. i.e. compare 14.5 1.5 1.75 1.9 2 5 10 15 17.5 I want it to return 15 as it is the closest number any ideas nic -- mr_nice ----------------------------------------------------------------------- mr_nice!'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2323 View this thread: http://www.excelforum.com/showthread.php?threadid=57253 Sort the data desce...

check numbering #2
We were printing checks and came to check number 9999. Instead of advancing to 10000, the check numbering started over at 0001. Does anyone know of a way to fix this without printing the checks through 9999 and then manually changing the number in the checkbook maintenance screen before printing the remainder of the checks? If you pad your check numbers with zeros, you won't have this problem. ie. 0009999 -- Japheth Nolt Microsoft SBF Specialist Landis Computer www.landiscomputer.com "Carol" <Carol@discussions.microsoft.com> wrote in message news:1AC9C25F-C44D...