convert decimal hours to HMS?

Can someone please clue me as how to make this conversion?
Thanks,
E-Jo


0
ejoline (61)
8/10/2004 9:10:48 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1105 Views

Similar Articles

[PageSpeed] 33

Divide the decimal hours by 24 and format the cell from hh:mm:ss.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Everett Joline" <ejoline@optonline.net> wrote in message
news:%23n9gn5xfEHA.1356@TK2MSFTNGP09.phx.gbl...
> Can someone please clue me as how to make this conversion?
> Thanks,
> E-Jo
>
>


0
chip1 (1821)
8/10/2004 9:20:01 PM
Hi E-Jo

In general, divide by 24 and format as hh:mm. But "decimal hours" come in
lots of flavors, like 1045, 10.45, 10.75 and 1075, all representing 10:45,
so you may have to adjust a little -or maybe a lot. See Chip Pearson's page
http://www.cpearson.com/excel/datetime.htm for some theory on this.

HTH. Best wishes Harald


"Everett Joline" <ejoline@optonline.net> skrev i melding
news:%23n9gn5xfEHA.1356@TK2MSFTNGP09.phx.gbl...
> Can someone please clue me as how to make this conversion?
> Thanks,
> E-Jo
>
>


0
innocent (844)
8/10/2004 9:22:17 PM
Thanks Chip and Harold for your quick response. I guess the secret is to
divide by 24
to get it into days first, and then convert that.
E-Jo

"Everett Joline" <ejoline@optonline.net> wrote in message
news:%23n9gn5xfEHA.1356@TK2MSFTNGP09.phx.gbl...
> Can someone please clue me as how to make this conversion?
> Thanks,
> E-Jo
>
>


0
ejoline (61)
8/10/2004 9:53:53 PM
E-Jo,

Times are stored as a fraction of a 24 hour day (06:00 = 0.25,
12:00 = 0.5, 18:00 = 0.75, etc), so if you have a number of
hours, such as 14.5, dividing that by 24 converts it to an Excel
time value.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Everett Joline" <ejoline@optonline.net> wrote in message
news:Oo0N0RyfEHA.3048@TK2MSFTNGP09.phx.gbl...
> Thanks Chip and Harold for your quick response. I guess the
secret is to
> divide by 24
> to get it into days first, and then convert that.
> E-Jo
>
> "Everett Joline" <ejoline@optonline.net> wrote in message
> news:%23n9gn5xfEHA.1356@TK2MSFTNGP09.phx.gbl...
> > Can someone please clue me as how to make this conversion?
> > Thanks,
> > E-Jo
> >
> >
>
>


0
chip1 (1821)
8/10/2004 10:04:17 PM
Reply:

Similar Artilces:

convert text to numeric values
My database (Access 2007) keeps track of bids from FE colleges. These are made in an academic year (these span two 'calendar years e.g. 2009-10 for the current academic year). Sometimes the bids are carried forward to, and paid in, the following academic year. The 'Bid Year' and 'Paid Year’ are text fields. When designing a query that will give me the total sum of bids made, bids approved and bids actually paid, I can do separate ones based on 'Bid Year' and 'Paid Year' but the totals are quite often different if a year has bids that have been carr...

converting dates
Hi All I have a couple of excel problems to do with dates (Excel XP/2002). I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365. Secondly does anyone know a formula that sorts out leap years (see above). Any help, suggestions or tutorial links greatly appreciated Rexmann As long as you assume 1 year is always 365 days =INT(A1/365)&" year(s) "&MOD(A1,365)&&q...

Convert years:months to months
Hello- I am hoping to use Excel to convert data that inputted in a years:months format to just months. For example, I want to translate 5:3 to 63 months. I'm running into one hiccup: Excel views the inputted data as time and is translating it into 5:30 AM, making calculations challenging Does anyone have any tips? =HOUR(A1)*12+MINUTE(A1)/10 you may need to format the cell a General as Excel can be overhelpful and use a time format best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jen" <jvarley@u.washington.edu> wrote in ...

converting amount into figures
Is there any wany to convert any amount into figure automatically. 100/- should automatically convert into;;;; one hundred only See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "IRFAN AHMAD" <IRFANAHMAD@discussions.microsoft.com> wrote in message news:7A1570B6-5F0F-4E5C-8C5D-4F5BF6F7943F@microsoft.com... > Is there any wany to convert any amount into figure automatically. > > 100/- > should automatically convert into;;;; > > one hundred only ...

converting linked cells to their referenced values
I have 2 spread sheets for tracking orders. The orders are entered on the first sheet and appear in the second sheet by cell reference formulas. Eventually when the order is no longer active, I change the reference formula to the value of the referenced cell since it will be deleted from the first sheet. When there are a lot of orders isn't very practical to copy past from one spread sheet to the other. Is there a macro or something that I can use to convert the link formula in a cell to the value of the cell it references? -- quimrider ---------------------------------------------...

Convert COleDateTime to Double
How do I convert a Double to COleDateTime I know how to convert COleDateTime to Double, using the following code double XX = DateTime.m_dt; But what is the coding to convert back; Thanks Guys. Regards, Jimmy "Newbie & Noobie" <NewbieNoobie@discussions.microsoft.com> wrote in message news:498FCE41-07A2-4AFA-AFB6-C7669F12EF44@microsoft.com... > How do I convert a Double to COleDateTime > > I know how to convert COleDateTime to Double, using the following code > double XX = DateTime.m_dt; > > But what is the coding to convert back; > If you have a...

converting 1.0 snap-ins to 2.0 modules
Per the subject line, is this documented anywhere? I did a search on MSDN and Google and didn't find anything particularly clear. I have some C# snap-ins that I'd like to convert to 2.0 modules and am looking for developer best practices, tips, hints, etc.. Thanks! Darren I'll assume you've seen this new documentation? "Darren Mar-Elia (Group Policy MVP)" <dmarelia@hotmail.com> wrote in message news:3E257E8B-967F-4667-A403-F360911A2AAD@microsoft.com... > Per the subject line, is this documented anywhere? I did a search on MSDN > ...

Decimals in Formula
how do I turn This: ="This totals need to consider "&(ROUND(SUM(G28:G32),2)) Into This: This totals need to consider 171,150.00 And Not This totals need to consider 171150.00 ="This totals need to consider "&TEXT((ROUND(SUM (G28:G32),2)),"#,###.00") HTH Jason Atlanta, GA >-----Original Message----- >how do I turn This: >="This totals need to consider "&(ROUND(SUM(G28:G32),2)) > >Into This: >This totals need to consider 171,150.00 > >And Not >This totals need to consider 171150.00 > >. > Trella, He...

How to convert time to decimal format automatically...
hi..pls correct if this is not the right forum, i have a big problem i converting time to decimal automatically, i know the formula which i "=A1*24", what i want is when i enter a time (8:34) in cell A1 an press enter key it will convert to decimal (8.5667) forma automatically. sorry if my term is wrong, i'm just quite a starter i using Excel if any one help me with this problem, it's greatl appreciated. i want to know this because almost my work in office i entering time in Excel, i just want my work will be efficient -- alek2 ----------------------------------------------...

Changing from hours to minutes.
I have a report that was sent to me as follows, A1=1:00:00 A2=2:00:00 A3=5:00:00 How can I change this from hours to minutes, the 1, 2, 5 are minutes, not hours!! Put 60 in an empty cell Edit|copy that cell Select the range to fix Edit|Paste special|divide and values clear up that cell with the 60 in it. GEM wrote: > > I have a report that was sent to me as follows, > > A1=1:00:00 > A2=2:00:00 > A3=5:00:00 > > How can I change this from hours to minutes, the 1, 2, 5 are minutes, not > hours!! -- Dave Peterson Thank you very much!! "Dave Peterson&...

convert hex string to byte[]
Hi All, I have the following hex string "03828710073100A22300"; I want to convert it to byte array in the following way: "03" to 3 "82" to 82 "87" to 87 "10" to 10 "07" to 07 the byte array should look like {3,82, 87.10. 7....}; Thank you for your help. "alexia" <alexia.bee@gmail.com> wrote in message news:24ad9d98-a2b1-4625-8521-42ae5b5edd73@l13g2000yqb.googlegroups.com... > I have the following hex string "03828710073100A22300"; > > I want to convert it to byte array in th...

Converting variable text strings to numeric
I've imported some word tables into excel, the 'numbers' have spaces instead of commas and consequently are read as text. The 'numbers' vary from single integer to millions For example 1 101 335 293 207 23 113 101 19 While I can use the left, mid and right functions for the millions that formula is unworkable for other numbers. Any ideas, or do I have to get a VB programmer to write a function for me? Hi! Try using Find/Replace Select the range Goto Edit>Replace Find What: enter a space by hitting the space bar Replace With: nothing...

Convert 1.15 to 1.25 for time purposes.
I've been using decimals for time calculation. My totals output to the exact minute. I'm needing my time to stay in the "0.00" format. But I'm needing it to display in quarter hour increments. example: 1.15 = 1.25 1.30 = 1.50 1.45 = 1.75 1.01 to 1.14 = 1.00 1.15 to 1.29 = 1.25 1.30 to 1.44 = 1.50 1.45 to 1.59 = 1.75 Is this possible? Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ One way =INT(A1)+(MOD(A1,1)/14.4*24) or ...

convert slide master to title master
Hello, I am moving slides between decks, and the slide and title master have become "disconnected". I have two slide masters, and no title master. Is there a way to reconnect them without having to build the title again? Which version of PPT are you using? If it's 2003, you might just want to add a title master to the 2nd slide master (which is really your title master, right?). It should look pretty similar to the slide master, so maybe you don't have to rebuild so much. Then go to Format | Slide Design and choose the 2nd slide master -- right-click it ...

how excel 2000 convert the number to time format ( Example 130511.
how excel 2000 convert the number to time format ( Example 130511 become 13:05:11 ) ? wong Wrote: > how excel 2000 convert the number to time format ( Example 13051 > become > 13:05:11 ) ? Assuming A1 contains your number... =TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)) OR =SUM(MID(A1,{1,3,5},2)/{24,1440,86400}) ...confirmed with CONTROL+SHIFT+ENTER, and format cell as "Time". Hope this helps -- Domeni ----------------------------------------------------------------------- Domenic's Profile: http://www.excelforum.com...

Convert Contacts to Address Book
I have always (for years) entered my email addrees and info in Contact folder. Now I would like to convert this Contacts folder to an personal Address Book. The outlook Address Book is a filtered view of your Contacts, showing only those with an electronic address (email / Fax), it is not a seperate item "OR Mike" <OR Mike@discussions.microsoft.com> wrote in message news:8C81EDA0-369F-41ED-8457-C6326719AEDB@microsoft.com... >I have always (for years) entered my email addrees and info in Contact > folder. Now I would like to convert this Contacts folder to...

Pivot table total decimal places rounding
The totals in my pivot tables have too many decimal places. I total -$1,025.31 and $1,023.87 and the result is $1.43999999999994 in the total cell of the pivot table but I need just $1.44 I have formatted the table as currency and using 97. Pivot tables can lose their formatting if the data is refreshed. Reapply the formatting, and make sure that 'Preserve formatting' is checked (under PivotTable>Table Options). AM wrote: > The totals in my pivot tables have too many decimal places. > I total -$1,025.31 and $1,023.87 and the result is > $1.43999999999994 in the to...

convert number format to time format
I have a whole column in which I need to be able to enter a number an it should convert it to the time format. If I enter 100 it shoul convert it to 1:00. Similarly entering 2300 should automatically b converted to 23:00. Thank yo -- Message posted from http://www.ExcelForum.com Hi Fullymooned! No need to send the same question to different groups. The answer given in programming was: See: Chip Pearson: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax...

Is there any way to convert a pdf document into an excel document.
Is there any way to convert a pdf file into an excel document? ...

Scheduled Duration as Hours
Hi The scheduled duration is reflected in minutes in CRM. When we extract reports on the time allocated to the completion of service activities all scheduled time is reflected in minutes. We would like to change this to hours. Is this possible in CRM? I have attempted to solve this by creating a custom attribute named 'Scheduled Duration (Hrs)'. I inserted Jscript on the OnSave event and my script was: crmForm.all.new_scheduleddurationhrs.DataValue = crmForm.all.scheduleddurationminutes.SelectedText / 60; I received an error stating: "Microsoft CRM was unable to save the...

Converting MSMoney 2000 files into MSMoney 2006
Hi Guys, I have a trial version of MSMoney 2006 but it doesn't seem to support files that were created in MSMoney 2000. Is this correct or is there a different way to do it ? Also, it it does not support MSMoney 2000 files, what version amd I best off using as I do need to access the old file ? Regards, Anestis@ement.com What makes you think that? Do you get an error message or something else happens? -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.mi...

converting to pdf.
Is it possible to convert access2000 objects (forms, tables, queries, reports) to a .pdf file? And if it is how do I convert it? ...

How do I convert date values to non-date format e.g. 01-06-78?
Hello, we have a large spreadsheet (Excel 2003) with bank codes that have been automatically converted to date format. Any bank code in column G that could also be a date, such as 03-07-78, will get converted to 03/07/78. Is there a way to convert these 'dates' back to format for bank code (we do need date format for other column)? Thank you, I think you'd be much better off reimporting the data into excel. If you're importing a text file, make sure that the extension is .txt. Then file|open your workbook and specify Text for that field. But you could try a helper co...

Converting dataset to XML
(Type your message here) -------------------------------- From: Rohit D Hi All, I have a dataset and want to write a standard code in vb=2Enet for= converting that dataset to an heirarchical XML that refers to an= XML schema so that i can finally deserialize the xml to the= class object generated from the XSD=2EIs there any way out for= this? ----------------------- Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/) <Id>1doa/ku2UUqWoP40YVDdqQ=3D=3D</Id> Look into DataSet.WriteXml(); Alex "Rohit D via .NET 247" <anonymous@dotnet247.com> wro...

Converting dates to fiscal quarters
Is there a way to easily convert a date to a fiscal quarter Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-199 Thanks in advance for your respons One way with the date in A1 ="Q"&INT((MONTH(A1)+2)/3)&" - "&TEXT(YEAR(A1),"@") -- Regards, Peo Sjoblom "James" <anonymous@discussions.microsoft.com> wrote in message news:9F45345C-B1DB-4353-8292-A216A88121A3@microsoft.com... > Is there a way to easily convert a date to a fiscal quarter? > Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-1998 > > Thanks in advance for your res...