Convert seconds to years days hours minutes format in google sheet-CodePudding

I am trying to convert the seconds to years days hours minutes format in GSheet. I am using seconds/86400 and then using a custom format yy" years "dd" days "hh" hours "mm" minutes". But result always has 1 day difference from expected output. How can I solve it ?

CodePudding user response：

this will never work because there is no zero day/month in history of humankind

January of year 0000 minus one day is 31. December of year -0001

to convert unix / epoch time use:

=INDEX(IF(A2:A,
 TEXT(DATEDIF(DATE(1970, 1, 1), TEXT(A2:A/86400 DATE(1970, 1, 1), "yyyy-mm-dd"), "Y"), "00")&" years "&
 TEXT(DATEDIF(DATE(1970, 1, 1), TEXT(A2:A/86400 DATE(1970, 1, 1), "yyyy-mm-dd"), "YD"), "00")&" days "&
 TEXT(A2:A/86400 DATE(1970, 1, 1), "hh \hour\s mm \minut\e\s"), ))