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
1940 Views

Similar Articles

[PageSpeed] 13

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:

View pareto chart by sorting the max number on the bottom
Is it possible to format the pareto chart by sorting the maximun number show in the bottom of each bar and ignore sort by Legend item? The order is determined by the Row Source of the chart. -- Duane Hookom Microsoft Access MVP "sq75222" wrote: > Is it possible to format the pareto chart by sorting the maximun number show > in the bottom of each bar and ignore sort by Legend item? > > ...

Numbers beginning with 0
Hi all! I'm having trouble figuring out what to do for a client. She has a speradsheet of addresses and unfortunately one postcode is 0200 (australian postcode). Of course when adding it into excel it obviously chops off the 0 and simply sets it to 200. When mail merging it is causing all sorts of grief and simply changing the format of the cell from number to text is apparently not helping. Is there a way to prevent excel from taking away the zero in a number formatted cell? Thanks in advance! How is she "adding" the post code to XL? If typing it in, preformat the c...

Excel cells should take number format and/or optional text eg Dec.
I usually pre-set number formats and other limitations on forms to ensure that I get the information that I need. Often, however, the date is vague and requires text, e.g., Nov 2005 instead of 11/1/05, which is Excel's default if you type in "Nov 2005." I can alleviate the issue on my own work, but my forms need to remain locked when being used by others. My suggestion is that an option be offered so a cell could take either/or text or number in the case of dates. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the ...

Sort House Numbers
How can I get excel to sort house numbers as I would like, i.e 1,2,2a,3,4,5,5a,5b, et. etc. So far I've ailed miserably. Any help out there? TIA Mike G Hi Mike, You will have to separate the digits into a separate cell. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Extract the First Set of Digits (#DigitsFirstID) http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

Checking cell for Number
I have about 200 numbers in column A. I would like to enter any number in Column B. In column C I would like, it to show if the number is used or free. can this be done with a if statement? Thanks Try... =IF(COUNTIF($A$1:$A$200,B1),"Used","Free") If you have a list of numbers in Column B that you'd like to check, enter the above formula in C1, and copy and paste the formula to your other cells in Column C. Hope this helps! In article <E5CA9196-6FCF-492C-B488-1B7E431720CF@microsoft.com>, Lee <Lee@discussions.microsoft.com> wrote: > I have abou...

Format page number in Words Document
Hi, Appreciate if you can help me here. I am trying to number my pages in my document a bit differently using the header and footer but I can't seem to get around doing it. Normally the page numbers will run concurrently in the header or footer as in page 1,2,3,4...etc But what intend to do is add page 5 (a) after page 5 and before page 6. However when I do that under the header or footer page 5 becomes page 5 (a) How do I format this under the header or footer? Thanks You will need to use the following field construction { IF { PAGE } > 5 { IF { PAGE } &...

Count number of records after filtering
Hello, Probably a question that's easy to answer but I have been searching for hours now & it's driving me rather crazy: I have applied a filter to a form and now I want to know how many records I have got as a result, or better, I want to know if there are any records left - how do I do that? Tried so far: If Form.Recordset.Count = 0 Then ... If Iserror(Form.RecordSet.Count) Then ... If EOF(Recordset) Then ... but that all does not work. Thanks, Gerwin Gerwin: An easy way is to add a hidden text box, txtCount say, to the form, with a ControlSourc...

table of numbers 1 thru 52 pre sql2k5
Hi all, What's the easiest way to generate a single column of numbers in a table prior to sql 2k5? thanks, rodchar Check out: http://msdn.microsoft.com/en-us/library/aa175802(SQL.80).aspx -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" <rodchar@discussions.microsoft.com> wrote in message news:C3DD455A-8DC5-42CD-884D-EB67EF6A4B5A@microsoft.com... Hi all, What's the easie...

comparing columns of numbers
Hi If you have 2 columns of numbers and you want to find the numbers that are in one columns but not the other, and you type say A10=B10 then when you find a figure that is in one column but not the other you insert/delete a line and then drag the formula down again to keep checking. Is there any better formula I can use or anyway I can do it so I don't have to keep dragging down the formula Public Sub ProcessData() Const TestCol1 As String = "A" Const TestCol2 As String = "B" Dim i As Long Dim LastRow As Long With Application .ScreenUpdating = Fal...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Excel default formatting of numbers
I have a CSV file which is generated by an inhouse application. One of the columns in this file has a four digit code, which are text but made of numerical digits. Some of the codes start with one or more zeroes, ie 0012, 0013, 0014 etc. The problem is when my users open the file in Excel the column has been formatted as numbers and the leading zeroes have been trimmed, so 0013 becomes 13. I do not want my users to have to play around with formatting. Also even if I do select the column and change the formatting to text the leading zeroes are still missing. Please could anyone help me with a ...

Store numbering
Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores can I number them 1,13,14,15,21? Or does that mess with the licensing? -- Pat pat@arms wrote: > Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores > can I number them 1,13,14,15,21? Or does that mess with the licensing? The StoreCode is not sequentially numbered... I use 120, 250, 377.. 686 ... the code of main supplier of stores to identify electronic-invoice, orders and others bye antonio ...

can you reassign week numbers
want to assign week 1 to the starting date for each new group is this possible? The groups will be around for about 14 weeks each, so would want each group to have 14 weeks of information Any ideas? No, week numbers are calculated using an intrinsic function. What you can do is store the date that is the beginning date of the first week. Then, you can use the datediff function to calculate the weeks from the beginnig to a given date. -- Dave Hargis, Microsoft Access MVP "Pat" wrote: > want to assign week 1 to the starting date for each new group is this > possible? ...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...

Return only the numbers contained in a field
Hello, I would like some help please building a query that will return the numbers only contained in a field: Table "PurchaseOrder" has a "Memo" field that usually has the following data: "Sales Order 7540:" or "Sales Order 34:". How can I create a query that will show me all the Sales Orders and their linked Purchase Orders (PurchaseOrder.RefNumber) where the SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo field? Thanks in advance, Brett Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd expect ugly...

SOP Quick Print
We cannot print more than one copy of the Packing Slip with SOP Quick Print. In Quick Print setup, we have the Destination set to Printer. We want 2 copies of the Order and 2 copies of the Packing Slip. We get the 2 copies of the order but, for some reason, only 1 copy of the Packing Slip prints. We can get multiple copies of every document EXCEPT the packing slip. Even if we print JUST the packcing slip, it still won't print more than one copy. Any ideas? "Elaine" wrote: > We cannot print more than one copy of the Packing Slip with SOP Quick Print. > In Q...

converting numbers to time
I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) Assumes C5 has 900 and Time format set to 00:00:00 HTH Rob =TIME(INT(C5/100),MOD(C5,100),0) "FishandChips" <FishandChips@discussions.microsoft.com> wrote in message news:41FD6501-94DA-4944-93CE-B382DDA05126@microsoft.com... >I have a lis...

pick a number from 1 to 10
hey all, i want to create an asp.net web page that allows internet users to pick a number from 1 to 10. how do i manage users picking the same number? (if 2 users somewhere happen to be selecting the same number and the same time). Is this a concurrency issue? thanks, rodchar hi Rodchar, On 19.12.2009 19:01, rodchar wrote: > i want to create an asp.net web page that allows internet users to pick a > number from 1 to 10. You may use ten links or buttons. But what should happens after they "picked" a number? > how do i manage users picking the same nu...

Counting number of worksheets in a Excel 2007
Dear Sir, I am using MS office 2007, and want to know what is command/formula to count the number of worksheets in a Excel. I deals with some taxation matter wherein I need to have hundreds of sheets in a file. Thank in advance. From within Excel code, Worksheets.Count will return the number of worksheets in the workbook. -- Rick (MVP - Excel) "Y D" <Y D@discussions.microsoft.com> wrote in message news:B2C3A4B9-22F4-4643-8850-BD340C4659C9@microsoft.com... > Dear Sir, > I am using MS office 2007, and want to know what is command/formula to >...

Find cells containing a specified number of characters
I have a spreadsheet with only 1 column of data, but 18,000 lines. Is there an easy way to find all cells that contain exactly 12 characters? I tried searching for ???????????? and ************ but it finds every cell. If you use Data->Filter->Advanced Filter you can use the 12 ?s as your filter. You need a column header - let's say it is Values, and let's say your data is in column A Then in C1 type Values and in C2 type ???????????? Then select a cell in column A and go to Advanced Filter. Set C1:C2 as the criteria range, and clikc on OK "jdanker" wrote:...

Text to Number format
I have copied a column of numbers to excel and now cannot apply any formulas because the numbers are text. How can I convert the text to numbers? Thanks. -- Janet In an un-used cell enter 1 (as a true number). Copy this cell and paste/special/multiply onto the cells you would like to convert. -- Gary''s Student - gsnu201001 "Janet" wrote: > I have copied a column of numbers to excel and now cannot apply any formulas > because the numbers are text. How can I convert the text to numbers? > Thanks. > -- > Janet perfect timing, I jus...

Merging GP Vendor Numbers
We have a situation with a vendor that has two vendor numbers. Both of his vendor numbers have history attached. Is there a way to merge the two numbers into one? Yes there is. You can use the Professional Services Tools Library to do so, specifically the Vendor Combiner tool, however, this may require you to register the tool or work with your partner to do so. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "BFogle" wrote: > We have a situation with a vendor that has two vendor numbers. Both of his ...

Add fax number to RM Statements
Under Tools >> Routines >> Sales >> Statements, the report uses the "Statement To" code to pick up the customer information, but the fax number is not included. When locally modified and the fax number is added, it is the fax number from the main customer maintenance screen and not the fax number from the address record associated with the "Statement To" data. Great Plains indicates that under the current design, the fax number can not be included. We suggest that this be changed to include the fax number within the report design that is associated ...

Problem in displaying number
Having problem in displaying number! A1=25.25 (outcome of some calculations, formatted as number with two decimal places) In A10, I am trying to display Please pay a sum of Rs. 25.25 only I am using the formula A10="Please pay a sum of Rs. "&A1&" only" Problem is coming with amounts like 25.00 or 25.50, the cell displays for 25.50 ----> Please pay a sum of Rs. 25.5 only (what I want is 25.50) for 25.00-----> Please pay a sum of Rs. 25 only (what I want is 25.00) separating Integar and Fraction part is not of help. Using =right(a1...