Thread: [Solved] Convert large seconds values to a time format fails with #Value
View Single Post
 
Old 07-18-2015, 01:56 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,366
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Quote:
Originally Posted by Sekerob View Post
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")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote