秒を日付に変換

秒を日付に変換する関数として、
以下のものを見つけました。
=TEXT(a1/86400+("1970/1/2"*1-"1900/1/1"*1+"9:0"*1),"yyyy/mm/dd hh:mm:ss")

この関数で変換するとこのようになります。
1384664725
2013/11/17 14:05:25 JST

ユニックスタイムといって1970年1月1日 00:00 UTCが起点になっているそうですが、

1601年1月1日 00:00　UTC　を起点に計算する関数はどうやって作れば良いのでしょうか？

130291350118733
が
2013/11/17 13:10　JST
になります。

No.6 ベストアンサー 回答者： bunjii 回答日時： 2013/11/18 13:53

>1601年1月1日 00:00　UTC　を起点に計算する関数はどうやって作れば良いのでしょうか？

下記の算式を試してください。

=TEXT(秒数/86400-INT((365+1/4-1/100)*基準までの年数)+1+時差/24,"yyyy/mm/dd hh:mm:ss")

基準までの年数はExcelで扱えない西暦1601年の場合は299です。
時差はUTCとJSTの時、+9時間で、1日の24時間で割れば時刻表示できます。

>130291350118733が2013/11/17 13:10　JST
桁を間違えているようです。
1/10000にすると微小差で一致します。

この回答へのお礼

ありがとうございます！できました！あの数値はマイクロ秒だったようです。

お礼日時：2013/11/19 11:02

No.5 回答者： bunjii 回答日時： 2013/11/17 22:18

>1601年1月1日 00:00　UTC　を起点に計算する関数は・・・・

>130291350118733が2013/11/17 13:10　JSTになります。
他の回答者から指摘されている提示の秒数を算出した根拠を示さないと誤差が大きすぎます。
グレゴリオ暦を基準に逆算する手法で1600年1月1日起算でも提示の秒数に満たないので議論の余地がありません。

Excelでは基準日が1900年1月1日を整数の1とし、1日は24時間、1時間は60分、1分は60秒として少数のシリアル値を使うことで日付と時刻を算出します。
従って、閏年が無ければ経過年数、月数、日数、時間、分、秒を単純に計算できます。
閏年を加味することになればグレゴリオ暦に従って算出すると誰もが納得できると思います。
1601年1月1日を起算とした理由は閏年の翌年から数えるとしていると思います。
計算方法は1601年1月1日0時丁度から1901年1月1日0時丁度までの400年間の日数を秒数に変換して定数とし、提示の秒数から差し引いた経過秒数を1901年1月1日0時丁度からの経過秒数で日付、時刻を割り出したうえでUTCとJSTの時差を加えれば回答になります。
但し、提示の秒数が途轍もなく大きな値なので残念ながら計算できません。

No.4 回答者： MackyNo1 回答日時： 2013/11/17 18:40

>130291350118733が2013/11/17 13:10　JSTになります

数値(桁数）が間違えているように思います。

おおよそですが、上記の数値を10000分の1にすると近い日付にになると思います。
この場合は（1000年単位ではうるう年の関係があっても同じ秒数となる？）、おそらく以下の式で計算できるのではないかと思います。

=TEXT(A2/86400+("2601/1/2"*1-("3000/1/1"-"2000/1/1")-"1900/1/1"*1+"9:0"*1),"yyyy/mm/dd hh:mm:ss")

数式を検証するためにも、実際の秒数と日付の正しい例を示されたほうが良いと思います。

No.3 回答者： web2525 回答日時： 2013/11/17 18:34

訂正

=YEAR(A1/86400+"2001/1/1")-400 &"/"& TEXT(A1/86400+("2001/1/2"*1-"1900/1/1"*1+"9:0"*1),"mm/dd hh:mm:ss")

です

No.2 回答者： web2525 回答日時： 2013/11/17 18:30

なぜ1601年？

なのかがよく分かりませんが

最初に、
Excelは1900年以前の日付けは計算ができませんので、1601年～1900年の間は文字列としてしか表示できません

次にグレゴリオ暦の場合は、400年周期でカレンダーが一周するので
1601年を2001年として計算した上で結果から400年引くことで擬似的に表示することは可能

質問中にある関数式を一部変更
=YEAR(A1/86400+"2001/1/1")-400 &"/"& TEXT(A1/86400+("1970/1/2"*1-"1900/1/1"*1+"9:0"*1),"mm/dd hh:mm:ss")

但しこれは文字列なのでその後の計算には利用できない


ちなみに130291350118733秒は約413万年でエクセルでは計算しきれません

No.1 回答者： maiko0318 回答日時： 2013/11/17 15:59

+"9:0"　　を消してみれば？おそらく