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

Sekerob · #1 07-18-2015, 10:02 AM

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 · #2 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