Thread: [Solved] Convert large seconds values to a time format fails with #Value
View Single Post
 
Old 07-17-2015, 09:57 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,365
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
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Last edited by macropod; 07-17-2015 at 10:03 PM. Reason: Formula revision
Reply With Quote