 |
 | 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 / March 2008keep digits on right of decimal
just finished a wack of datamining for times and have run into a problem with time formatting. Whoever build the db had time stamps with the dates in some fields and without in others. I'm analyzing everything in excel so some of the time stamps are 51758.423 (10:30 on a date) whereas other are just 0.423 (10:30). The easiest thing (I think) is to make a column with corrected values where I just keep the digits to the right of the decimal for the date/time fields but don't know how to do it. I'm formatting the cells as [mm]. I think I want a formula that will convert 51758.423 to 0.423. Any ideas? If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will return 0.423. The number 51748 formatted as a date is Sept.14, 2041 and 0.423 formatted as time is 10:09:07 AM Tyro > just finished a wack of datamining for times and have run into a problem > with [quoted text clipped - 9 lines] > [mm]. I think I want a formula that will convert 51758.423 to 0.423. Any > ideas? I should have added that if A1 is 51748.423 then in some other cell: =INT(A1) will return 51748. Tyro > If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will > return 0.423. [quoted text clipped - 18 lines] >> Any >> ideas? thanks guys - both work great. Ian. > If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will return > 0.423. [quoted text clipped - 16 lines] > > [mm]. I think I want a formula that will convert 51758.423 to 0.423. Any > > ideas? Try one of these: A1 = 51758.423 =MOD(A1,1) Returns 0.423 =A1-INT(A1) Returns 0.423  SignatureBiff Microsoft Excel MVP > just finished a wack of datamining for times and have run into a problem > with [quoted text clipped - 9 lines] > [mm]. I think I want a formula that will convert 51758.423 to 0.423. Any > ideas? > some of the time stamps are 51758.423 (10:30 on a date) whereas > other are just 0.423 (10:30). [....] I think I want a formula that will > convert 51758.423 to 0.423. I would use one of the following formulas, whichever suits your fancy: =hour(A1)/24 + minute(A1)/1400 + second(A1)/86400 =(hour(A1) + (minute(A1) + second(A1)/60)/60)/24 (You might consider omitting the term with SECOND.) The problem with A1-int(A1) is that the binary result might not compare equal to the timestamp that was just hh:mm:ss. I presume you see ".423" only you formatted the cell to 3 decimal places. The actual time of day might have been between 10:08:24.24 and 10:09:50.50. The date (51758) is 9/14/2041. If you enter, say, 9/14/2041 10:09 in A1 and just 10:09 in A2, then compute A1-int(A1) in B1, you will find that if(A2=B1,true,false) is false. The reason has to do with the way most binary computer do arithmetic. But if you compute hour(A1)/24+minute(A1)/1440 in B1, you will find that if(A2=B1,true,false) is true. The reason is because that is probably close to the way the time of day was converted to a fraction of a day in the first place. The general question was separating the integral from the fractional portion of a number. The number 51758.423 is a date/time, only if formatted as such for human consumption, otherwise, it's just a number. Tyro On Mar 21, 11:53 am, Ian <I...@discussions.microsoft.com> wrote: > some of the time stamps are 51758.423 (10:30 on a date) whereas > other are just 0.423 (10:30). [....] I think I want a formula that will > convert 51758.423 to 0.423. I would use one of the following formulas, whichever suits your fancy: =hour(A1)/24 + minute(A1)/1400 + second(A1)/86400 =(hour(A1) + (minute(A1) + second(A1)/60)/60)/24 (You might consider omitting the term with SECOND.) The problem with A1-int(A1) is that the binary result might not compare equal to the timestamp that was just hh:mm:ss. I presume you see ".423" only you formatted the cell to 3 decimal places. The actual time of day might have been between 10:08:24.24 and 10:09:50.50. The date (51758) is 9/14/2041. If you enter, say, 9/14/2041 10:09 in A1 and just 10:09 in A2, then compute A1-int(A1) in B1, you will find that if(A2=B1,true,false) is false. The reason has to do with the way most binary computer do arithmetic. But if you compute hour(A1)/24+minute(A1)/1440 in B1, you will find that if(A2=B1,true,false) is true. The reason is because that is probably close to the way the time of day was converted to a fraction of a day in the first place. > The general question was separating the integral from the fractional > portion of a number. It's a matter of interpretation. Yours is no more nor less right than mine; reasonable people can differ reasonably. On the one hand, the OP wrote: "the time stamps are 51758.423 (10:30 on a date) whereas other are just 0.423 (10:30)". On the other hand, he wrote: "I'm formatting the cells as [mm]" -- which seems odd for time of day, IMHO. In either case, it seems clear that the OP wants to consider the two expressions of time as equivalent. My only point was: they might not be equal if he does A1-int(A1). (They are not equal for the OP's example.) That fact is not altered by your interpretation. There is no harm in presenting the OP with two solutions and an explanation of the difference. The OP can decide which is better for his purpose. You bicker over something like 6.14e-13. I'm sure you will agree that this will not affect one second of your lifetime. Tyro On Mar 22, 12:50 pm, "Tyro" <T...@hotmail.com> wrote: > The general question was separating the integral from the fractional > portion of a number. It's a matter of interpretation. Yours is no more nor less right than mine; reasonable people can differ reasonably. On the one hand, the OP wrote: "the time stamps are 51758.423 (10:30 on a date) whereas other are just 0.423 (10:30)". On the other hand, he wrote: "I'm formatting the cells as [mm]" -- which seems odd for time of day, IMHO. In either case, it seems clear that the OP wants to consider the two expressions of time as equivalent. My only point was: they might not be equal if he does A1-int(A1). (They are not equal for the OP's example.) That fact is not altered by your interpretation. There is no harm in presenting the OP with two solutions and an explanation of the difference. The OP can decide which is better for his purpose. > You bicker over something like 6.14e-13. I'm sure you will > agree that this will not affect one second of your lifetime. No, that's not what I am concerned about (for the OP). As usual, you missed the point entirely. As I wrote, what I am concerned about is that a comparison of two timestamps -- one converted, another data entry -- will not be equal, even thought that might have been the intent. Whether or not that is something to be concerned about is up to the OP to decide, based on his unique knowledge of his application. I was merely informing the OP of the potential problem and workaround.
|
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.