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