Converting a number to Hour & Min format

I am attempting to take a basic number, say 2000, and convert the number into an hour and minute format (i.e. 33:33) only.  Everytime I use the custom format tab or the time/date tab I get a date included or an AM/PM setting.  Any ideas or suggestions?  Thanks in advance.
0
Morgan (35)
6/22/2004 5:51:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
402 Views

Similar Articles

[PageSpeed] 36

Hi,
For any number to be converted into time format you need to divide tha
number by 24, since Excel time code is 24 (hr) code. then you forma
the divided answer (83.33) into time, you will get 8:00
regards
sazi

--
Message posted from http://www.ExcelForum.com

0
6/22/2004 6:04:34 AM
Use

    Format/Cells/Number/Custom   [hh]:mm

In article <FD085308-B7BB-4654-83E8-71AB49C7A70D@microsoft.com>,
 Morgan <Morgan@discussions.microsoft.com> wrote:

> I am attempting to take a basic number, say 2000, and convert the number into 
> an hour and minute format (i.e. 33:33) only.  Everytime I use the custom 
> format tab or the time/date tab I get a date included or an AM/PM setting.  
> Any ideas or suggestions?  Thanks in advance.
0
jemcgimpsey (6723)
6/22/2004 7:27:39 AM
This was helpful, but perhaps I'm still not grasping the concept fully.  Lets say I have several cells and I have them as 50, 7, 13, 15, 3, and 9.  I want  add these up but I want to total them in hours and minutes format.  I need the numbers (i.e. 50, 7, 13) to be listed as is and do not wish them to be listed in decimals or anything else.  I am attempting to calculate minutes (seconds are dropped) on a tardy spreadsheet and needing them to be calculated in an hour and minute format.  I appreciate your reply and look forward to hearing back from you.


"saziz >" wrote:

> Hi,
> For any number to be converted into time format you need to divide that
> number by 24, since Excel time code is 24 (hr) code. then you format
> the divided answer (83.33) into time, you will get 8:00
> regards
> saziz
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
Morgan (35)
6/22/2004 7:45:01 AM
If your numbers (50, 7, 13,...) are minutes, then sum them and divide by 
24*60:


    =SUM(A1:A6)/1440

formatted as time.

In article <73B1C007-8AF7-426B-88ED-0D26DA13D346@microsoft.com>,
 Morgan <Morgan@discussions.microsoft.com> wrote:

> This was helpful, but perhaps I'm still not grasping the concept fully.  Lets 
> say I have several cells and I have them as 50, 7, 13, 15, 3, and 9.  I want  
> add these up but I want to total them in hours and minutes format.  I need 
> the numbers (i.e. 50, 7, 13) to be listed as is and do not wish them to be 
> listed in decimals or anything else.  I am attempting to calculate minutes 
> (seconds are dropped) on a tardy spreadsheet and needing them to be 
> calculated in an hour and minute format.  I appreciate your reply and look 
> forward to hearing back from you.
>
0
jemcgimpsey (6723)
6/22/2004 8:29:49 AM
Reply:

Similar Artilces:

Format function in Ms Access 97
I am in trouble in comparing two dates: as I wrote code as If (Format(Me![Delivery Timescales], "short date") < Format(Now(), "short date")) Then MsgBox "Please enter Requested Start date as current date or any future date", vbCritical End If where [Delivery Timescales] is a Text Box and the whole code has been written on the click of a button. The Error I am getting is the If condition is coming false as I entered 31/12/2004 in [Delivery Timescales] text box. Can anybody help? Thanks in advance. -- Message posted via http://www.acc...

Page Numbering 101
When you want to assign different page numbers to a manuscript line i, ii. iii, iv to the preface and so on and 1, 2, 3, 4 . . . to body, how is this done? I knew once, and it was so easy. But then, the jungle grew back. Thanks, Tom Insert section... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Thomas Minter" <tminter5@adelphia.net> wrote in message news:459ffa8a$0$8969$4c368faf@roadrunner.com... > When you want to assign different page numbers to a manuscript line > i, ii. iii, iv to the preface and so on ...

Adding numbers with #VALUE! between
Hi all I'm trying to add numeric data in a column but because it has "#VALUE! inbetween I get a "#VALUE!" return. Please let me know if there's a way of adding the numbers and ignorin the "#VALUE!" Many Thanks Joe -- Message posted from http://www.ExcelForum.com Hi Joey, Try, =SUM(IF(ISNUMBER(A1:A100),A1:A100)) to be entered using CTRL+SHIFT+ENTER Hope this helps! In article <Joey.17mwvw@excelforum-nospam.com>, Joey <<Joey.17mwvw@excelforum-nospam.com>> wrote: > Hi all > > I'm trying to add numeric data in a colum...

Convert VBA to ASP
I am considering changing my extensive VBA code database to ASP. Is this an impossible task and should I just rewrite the whole thing? Is ASP the up and coming language for database code? Is Microsoft going to be changing from VBA to something else as its main code? I think I am out of my league on this one and would appreciate some help. No, I don't think ASP is "the up and coming language for database code". ASP.Net has surplanted it already, and who knows how long it'll be before Microsoft tells us ASP.Net wasn't the correct answer either? Afraid I don'...

Duplicate Item Numbers
We have a vendor that occasionally will supersede part numbers to a new number. Is there a way to merge the old(no longer used item number) into the new number and have the sales history transfer? I do not think GP has built in capacity to combine/modify items. There is a 3rd party software called Professional Service Tools (PSTL) that can do what you're asking. "Chad K" wrote: > We have a vendor that occasionally will supersede part numbers to a new > number. Is there a way to merge the old(no longer used item number) into the > new number and ha...

count current run of positive numbers
hi, in Column M i have a list of results that will go on down the page as values are entered elsewhere, these results will be both negative and positive, i was after a formula that will give me the count of the current run of positive numbers, eg. below, the cell with the formula in it would return a value of 4 for the current run of positive numbers, and if there was a run of negative numbers it would be 0 2 5 6 -5 2 4 7 9 -- thanks Will there be any numeric 0 entries? >a list of results that will go on down the >page as values are entered elsewhere That ...

Roundup, Roundown to a specific digit or number
Is there any way to use the "Round, Roundup or Rounddown" function to round results to a specific number? In my case I'm trying to roundup the sum's result to the nearest "9" =ROUND(x*9,0)/9 or =MROUND(x,9) MROUND requires the Analysis ToolPak, which is on the Excel CD, but may not have been installed. Jerry tamato43 wrote: > Is there any way to use the "Round, Roundup or Rounddown" function to round results to a specific number? > > In my case I'm trying to roundup the sum's result to the nearest "9" I believe in...

Converting from Money 99 up to 2007
Can somebody tell me how I can upgrade information from Money 99 up to Money 07 ? I tried to upgrade from 99 to 2003 however, the 2003 can't read the 99 version. A year ago I gave up and decided to keep a hard copy of all the information I keep within my 99 version, but hopefully somebody smarter than me can help me to recover this information In microsoft.public.money, tico67 wrote: >Can somebody tell me how I can upgrade information from Money 99 up to Money >07 ? I tried to upgrade from 99 to 2003 however, the 2003 can't read the 99 >version. A year ago I gave u...

Multiplying numbers in a single cell
I have 12 x 6 x 20 (as a length x highth x width) in cell A1 and I woul like B2 to represent the total area. In other words multiply 12 x 6 20 to equal 1440 -- gacolleg ----------------------------------------------------------------------- gacollege's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=151 View this thread: http://www.excelforum.com/showthread.php?threadid=27251 Hi you may split this info in separate cells. This would be much easier -- Regards Frank Kabel Frankfurt, Germany "gacollege" <gacollege.1equzy@excelforum-nospam.com> sc...

Formatting Headers & Footers in Excel
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I can't figure out how to set the alignment (left, center, right) for what I enter in a custom Header box. In 2004, the Left, Right, Center alignment bars in the formatting bar used to be available so you could place the Text in the box as you wanted, now they are gray'd out. Using the Customize Text only changes the text, not the alignment in the cell? In article <59bb0f73.-1@webcrossing.JaKIaxP2ac0>, peter155@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (T...

How to generate unique MRV Numbers?
In my DB we track all non-conforming material (stock items from vendors) and RGA's (Return Goods-field returns from our Customers/Distributors). We use Access 2003.My problem is that I have a form that is ran using a query and then a command button to print the report to be shipped with the part back to the vendor that we use to create MRV's (Material Return to Vendors). When I create the first MRV for a particular vendor (ABC) it gives me the new number (113-F) and shows the 3 parts to be returned to that vendor on the sub-form. That is connected to the MRV primary form using the v...

Office 2007
Have purchased MS Office Home & Student 2007. Want to install product on my laptop and desktop. Believe license agreement will allow that. Also wish to install additional license on my daughter's laptop as she occasionally does editing work for me. Does MS Office 2007 support third license installation? Also, after installation, can I de-install MS Office 2003 from the desktop and laptop that contain the MS Office 2007? Without doing any harm to MS Office 2007. Rather than relying on peer support for a legal question, you might want to look up your End Us...

Converters
Is there a converted for Excel XP 2002 SP 2 to read in a spreadsheet created by an older version (Version 4 of Works for Windows)... If so, how can I get it... Please reply to my email address jpryce85260@yahoo.com ...

convert PDF to text
Do yall have a trick to convert a PDF to text (without Adobe, the rich person's application)? I use PrimoPDF to generate PDFs. Thanks! -- Hugs, Erika You can try a copy and paste but the results are iffy. I use ScanSoft's PDF Convert and have had varied levels of success. -- JoAnn Paules MVP Microsoft [Publisher] "Erika" <erikakatz@mail.com> wrote in message news:eb8DBYVwGHA.3996@TK2MSFTNGP03.phx.gbl... > Do yall have a trick to convert a PDF to text (without Adobe, the rich > person's application)? I use PrimoPDF to generate PDFs. Thanks! > ...

How do I change cell that contains a formula to an actual number
I would like to use the results of a formula in a cell as an actual number. Any help would be appreciated? Hi copy the cell and use 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany "Carlton A. Barlow" <CarltonABarlow@discussions.microsoft.com> schrieb im Newsbeitrag news:D8B1F514-E50A-4CB8-98B5-50D6FAA4B739@microsoft.com... > I would like to use the results of a formula in a cell as an actual number. > Any help would be appreciated? An easy way to do this is when editing the formula just type cntl+"=". It immediately chan...

How do I change date format in excel footer
the format dd-mm-yy would be more convenient for me and my colleagues. is there a way to do that? thank you. Hi only possible with vBA directly inserting the date in the required format. There's no build-.in way to achieve this -- Regards Frank Kabel Frankfurt, Germany "nunee" <nunee@discussions.microsoft.com> schrieb im Newsbeitrag news:AEED7770-DECB-497E-A21A-686332689A02@microsoft.com... > the format dd-mm-yy would be more convenient for me and my colleagues. is > there a way to do that? thank you. If you change your Windows>Control Panel>Regional and L...

How to Convert Char to Datetime
Hello. I am trying to use DATEDIFF here. DATEDIFF(day, getdate(),CONVERT(varchar, tblMain.dtOpen, 101)) I got the following error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." It appears that I need to convert "tblMain.dtOpen" into datetime format to calculate DATEDIFF, correct? How can I convert Char to Datetime? Thanks in advance. Justin What is the data type of the column dtOpen. If character data type, then what is the format? You can try: SELECT DATEDIFF(DAY, C...

Search Business Number field in The Outlook 2002 address book
Hi all, For various reasons I need to be able to search the Business Number field from from Outlook address field so I can find who has a particular number. Currently you can search most of the addressbook fields but not this one. Does anyone have any ideas on how I would do this? Thanks Murdo ...

Format issue
Hi all, One of my user got a message from an external user with a notification shown as below and the original message has attached as text file. 'This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. ' My servers are windows server 2003 servers. i have checked the 'internet message format' under global settings in exchange sytem ...

Date formats
Hi I am trying to design a query in design view and need to insert the dates under the criteria. when i give the dates for example as 01/01/2007, it changes to #01/01/2007# but these dates r not recognised. am i using the right format? if yes where could i go wrong? cuz when i take the dates off, i see the data. Nick, My first guess is that your data actually contains dates and times? This happens when your program uses the Now() function to store a data/time value instead of using the Date() function to store only a date value. If that is the case, change the column to: FilterDate...

pocket money United kingdom date format
Does anyone know if this can be achieved? If so how! Limitation that can';t be changed sorry! -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to any unsolicited email regarding Money "Stuart beaton" <stuart.beaton@nfdc.gov.uk> wrote in message news:31f101c37b88$f525cdd0$a501280a@phx.gbl....

MS Access Customizing an Auto Number
Hi - I would like to create my own auto number. It should include: project acronym+date+#### Example: 65947WHB-CAE_042210-0001 I have a table set up that I can pull the project acronym from, the date would be the date entered into the database, the number would generate automatically. What is the best way to do this? Thanks for your help. April On Thu, 22 Apr 2010 14:08:03 -0700, April Klein <April Klein@discussions.microsoft.com> wrote: >Hi - > >I would like to create my own auto number. It should include: > >project acronym+date+###...

Converting some fields to columns
Hi, I have the following table - Test Tester Result Test1 Brian Pass Test1 John Pass Test21 Brian Fail Test23 John Pass Test77 John Fail Test111 Aine Pass Test111 John Pass what query would i need to achieve the following - Test Aine Brian John Test1 Null Pass Pass Test21 Null Fail Null Test23 Null Null Pass Test77 Null Null Fail Test111 Pass Null Pass Thanks, Aine <aine_ wrote:... > Hi, > > I have the following table - > > Test Tester Result > Test1 Brian Pass > Test1 John Pass > Test21 Brian Fail > Test23 John Pass > Test77 John Fail > Test111 Ain...

time delay refresh on random number
Is it possible to have a time delay on a random number. For example the random number refresh every x seconds. Read this: http://www.cpearson.com/Excel/OnTime.aspx -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bertrand" wrote: > Is it possible to have a time delay on a random number. For example the > random number refresh every x seconds. Bertrand, You could use the OnTime method to fire a macro that writes the random number to a cell or a variable once every six seconds or so. Run Random6...

Conditional Formatting for Changes to cells
I want to use either conditional formatting or change tracking to change the font color on any changes made to a spreadsheet by a user. Just one user will be using each spreadsheet but the changes are then submitted to someone for data entry into the big iron. Thanks dick Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.Color = vbRed End Sub Bob Umlas Excel MVP "Dick Stapleton" <dick.stapleton@coldist.com> wrote in message news:uxK7%23IqvJHA.4928@TK2MSFTNGP03.phx.gbl... >I want to u...