#1
|
|||
|
|||
Convert large seconds values to a time format fails with #Value
Working with a volunteer distributed computing project that provides it's time keeping data in seconds. This is pulled into Excel 2013 and then converted to a y:ddd:hh:mm:ss notation. In using the format d:hh:mm:ss as intermediate step to get to the object format of years:days:hours:minutes:seconds, find that any value over 255611462399 seconds evaluates to #Value i.e. the maximum working number is 8105:140:23:59:59. An example of large number is shown on this web page of mapping cancer markers which has accumulated 160,000 years computing time as of this post: https://secure.worldcommunitygrid.or...ShortName=mcm1
The formula used [with semicolon as separators] is INT(E7/SecYr)&":"&RIGHT("00"&INT(MOD(E7;SecYr)/SecDay);3)&":"&TEXT(E7/SecDay;"hh:mm:ss") To overcome this [bug/limit], am using a much convoluted formula that e.g. converts 407140107951 seconds [in H7] to 12910:119:18:25:51 with INT(H7/SecYr)&":"&RIGHT("00"&INT(MOD(H7/SecYr;1)*365);3)&":"&RIGHT("0"&INT(MOD(H7/SecHr;24));2)&":"&RIGHT("0"&INT(MOD(H7/60;60));2)&":"&RIGHT("0"&MOD(H7;60);2) The variables such as SecYr, SecDay and SecHr speak for themselves, e.g. SecHr being 3600 seconds, where MOD is used to compute the residuals for each of the time parts. Is there any better way or a fix to this issue underway in a future excel release? Thx |
#2
|
||||
|
||||
For a value in A1, you could use:
Code:
=TEXT(INT(A1/(86400*365)),"00 ")&TEXT(MOD(INT(A1/86400),365),"00 ")&TEXT(MOD(INT(A1/3600),24),"00:")&TEXT(MOD(INT(A1/60),60),"00:")&TEXT(MOD(A1,60),"00") Code:
=TEXT(INT(A1/(86400*365.2425)),"00 ")&TEXT(MOD(INT(A1/86400),365.2425),"00 ")&TEXT(MOD(INT(A1/3600),24),"00:")&TEXT(MOD(INT(A1/60),60),"00:")&TEXT(MOD(A1,60),"00")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 07-17-2015 at 10:03 PM. Reason: Formula revision |
#3
|
|||
|
|||
Thanks for your reply. Saw the mail notification earlier and played with your 'improved' version. Now it's 146 char long from before 160 and substituted the old whereever it showed in the workbooks.
Bug or feature, I've been unable to derive where 255611462399 seconds might have been coming from. Pity your MS MVP crystal ball is as hazy as mine with regards to what's being fixed. Maybe I'll have a look at the OF2015 test release. Curious is, if using d:hh:mm:ss as text mask, the feedback value is 31:23:59:59 or 32 days minus 1 second. Did not find [d]:hh:mm:59 as working. Pity as that would have allowed to reuse the days determined... 2958100 it seems. Anyway, Thx again. |
#4
|
||||
|
||||
Quote:
Here's some simplified formulae: Code:
=TEXT(INT(A1/(86400*365)),"00 ")&TEXT(MOD(INT(A1/86400),365),"00 ")&TEXT(MOD(A1,86400)/86400,"hh:mm:ss") Code:
=TEXT(INT(A1/(86400*365.2425)),"00 ")&TEXT(MOD(INT(A1/86400),365.2425),"00 ")&TEXT(MOD(A1,86400)/86400,"hh:mm:ss")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
time format |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Convert alphabets to numeric values | kjxavier | Word | 3 | 07-06-2014 05:34 AM |
Convert numeric values with decimals to alphabets | kjxavier | Word | 1 | 07-06-2014 01:44 AM |
PDF converter fails to convert equations | Jennifer Murphy | Word | 3 | 01-12-2012 02:57 PM |
Adding a long column of time increments in seconds | klippert | Excel | 1 | 09-18-2010 12:26 AM |
Is there a way to change the header in a large amount of documents at one time? | ntsstaffing | Word | 1 | 07-11-2009 12:12 PM |