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

macropod · #2 07-17-2015, 09:57 PM

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

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

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

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.

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