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

macropod · #4 07-18-2015, 01:56 PM

Quote:

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:

Code:

=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:

Code:

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

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