Convert large seconds values to a time format fails with #Value

[Sekerob]

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

[macropod]

For a value in A1, you could use:

=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")

or, more precisely, to take account of leap years:

=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")

As for what features MS might add to a future Excel release, your crystal ball is as good as anyone else's... That said, I don't see that the issue you're having problems with is related to a limitation of Excel; rather, it's a limitation of the formula you're using.

[Sekerob]

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.

[macropod]

Originally Posted by Sekerob

I've been unable to derive where 255611462399 seconds might have been coming from.

Doubtless it's in your data - Excel wouldn't just create it out of thin air..

Here's some simplified formulae:

=TEXT(INT(A1/(86400*365)),"00 ")&TEXT(MOD(INT(A1/86400),365),"00 ")&TEXT(MOD(A1,86400)/86400,"hh:mm:ss")

and, for greater precision:

=TEXT(INT(A1/(86400*365.2425)),"00 ")&TEXT(MOD(INT(A1/86400),365.2425),"00 ")&TEXT(MOD(A1,86400)/86400,"hh:mm:ss")