 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007format second to hh:mm:ss should be simple but I'm stuck
I'm using Excel 2007 and this should be simple but I'm stuck. I have a column of seconds and I want to see them formated as hh:mm:ss but when I do the time format I get something that isn't correct. I've tried the TEXT, CONVERT and several other. Here's an example of what I'd expect: 185.4 should show 3:05 Thanks, Mark If you convert those values into seconds, you'll see it: With the 185.4 in (say) A17: =A17/24/60 And format as hh:mm > I'm using Excel 2007 and this should be simple but I'm stuck. I have a > column of seconds and I want to see them formated as hh:mm:ss but when I do [quoted text clipped - 7 lines] > Thanks, > Mark  SignatureDave Peterson That works and I've also found if I use the TIME function. 185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss I have a followup questions: How do I display the time in hh:mm:ss but if there is no hours that I don't get the zeros? > If you convert those values into seconds, you'll see it: > [quoted text clipped - 16 lines] >> Thanks, >> Mark One hour is equal to 1/24 (or 0.0416666666666667] You can test that in a custom format: [>=0.0416666666666667]hh:mm:ss;mm:ss Personally, I would find this confusing. I'd want to see the same format (with the hh=00) no matter what. > That works and I've also found if I use the TIME function. > [quoted text clipped - 27 lines] > > > > Dave Peterson SignatureDave Peterson Thanks Dave, that work well. Let me ask something about how excel does dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day, correct? And from what you wrote below, 0.04166666666667 is one hour. > One hour is equal to 1/24 (or 0.0416666666666667] > [quoted text clipped - 39 lines] >> > >> > Dave Peterson For some great info on how Excel stores and computes dats/times see Chip Pearson's site. http://www.cpearson.com/excel/datetime.htm#SerialDates Gord Dibben MS Excel MVP >Thanks Dave, that work well. Let me ask something about how excel does >dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day, [quoted text clipped - 43 lines] >>> > >>> > Dave Peterson That's a very helpful site. Thanks! > For some great info on how Excel stores and computes dats/times see Chip > Pearson's site. [quoted text clipped - 54 lines] >>>> > >>>> > Dave Peterson I should have scrolled down <sigh>. > For some great info on how Excel stores and computes dats/times see Chip > Pearson's site. [quoted text clipped - 54 lines] > >> > >> Dave Peterson SignatureDave Peterson You needed the practice<g> And I still haven't activated my chellspecker. Gord >I should have scrolled down <sigh>. > [quoted text clipped - 56 lines] >> >> >> >> Dave Peterson Nope. If you type 1 into a cell, it's 1 day (= 24 hours). Excel keeps track of dates (and times) by just counting from a common starting date. For most WinTel users, it's the number of days since Dec 31, 1899. For most Mac users, it's Dec 31, 1903. You can change the starting date via Tools|Options|Calculation tab (1904 date system). And times are just fractions of days. So one hour =1/24 (so .75 = 3/4 = 18 hours = 18/24ths of a day) And one minute =1/24/60 Chip Pearson goes into more details: http://cpearson.com/excel/datetime.htm > Thanks Dave, that work well. Let me ask something about how excel does > dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day, [quoted text clipped - 47 lines] > > > > Dave Peterson SignatureDave Peterson Dave Peterson <peter...@verizonXSPAM.net> wrote... ... >For most WinTel users, it's the number of days since Dec 31, 1899. For most >Mac users, it's Dec 31, 1903. ... Someone has to be picky. 1900 date system: numbers 61 to 2958465 (31 Dec 9999) are 60 plus the number of days since 28 Feb 1900; numbers 1 to 59 are number of days since 31 Dec 1899; 60 is fubar since 1900 wasn't a leap year. 1904 date system: numbers 0 to 2957003 (31 Dec 9999) are number of days from 1 Jan 1904, *not* 31 Dec 1899 since 0 represents 1 Jan 1904 in this date system. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.