EXCELでユリウス通日を求める関数がわかりません。

関数もVBAもほぼ初心者です。
セルD２に2190701と年月日
セルM2に09:15:15と時分秒
と入力されていて、秒単位でユリウス通日を求めたいです。
いろいろ調べたのですがよくわかりませんでした。
TEXT関数でやるということはわかりましたが、結局できませんでした。
よろしくお願いいたします。

質問者からの補足コメント

• 申し訳ございません。誤記です。
  
  ×：セルD２に2190701と年月日
  〇：セルD２に20190701と2019年7月1日
  
  219年ではありません。すいません！

    補足日時：2019/09/09 15:07

• 

  回答ありがとうございます。
  その後試行錯誤繰り返して元のデータD2を削除してしまい、
  D2に4桁数値で年2019
  E2に月日、この月日が３桁（１月～９月はｍ）と４桁（１０月～１２月はｍｍ）にしてしまいました。
  format関数で元に戻そうとしましたが、これも失敗しました。
  D2　2019
  E2　ｍｄｄまたはｍｍｄｄを数値
  X2→J2
  でお願いできますでしょうか。
  よろしくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/09/09 20:04

No.5 ベストアンサー 回答者： tatsu99 回答日時： 2019/09/09 20:41

＞D2　2019

＞E2　ｍｄｄまたはｍｍｄｄを数値
＞X2→J2
この前提です。（添付図参照）

J2へ
=DATE(D2,E2/100,MOD(E2,100))

ユリウス日を表示したいセルへ（添付図ではF2）
=(J2-1)+M2+2415020.125
と入力します。（書式は数値にしてください。確認のために小数点以下６桁程度を表示するとよいでしょう。）

値が正しいかどうかは
https://eco.mtk.nao.ac.jp/cgi-bin/koyomi/cande/d …
で確認してください。（私もここで確認済みです）時刻は日本標準時刻を選択してください。（世界標準を選んではいけません）

この回答へのお礼

3日かけてちっとも進まなかったのが一瞬で出来ました。すっきりした。
ありがとうございます。！！

お礼日時：2019/09/09 21:15

No.4 回答者： fujillin 回答日時： 2019/09/09 15:49

No1です

補足がほとんど無いので、式にはできないので方法としての回答です。

エクセルのデフォルトでのシリアル値は、1900/1/1 00:00:00 を1として、1日を1とカウントする実数で表されています。（Macは1904/1/1を基準に計算）
No2様が既にご指摘ですが、これ以前の日付をシリアル値として計算しようとするのには無理がでます。

一方で、ユリウス日の計算方法を国立天文台の方式によることにすれば、
http://eco.mtk.nao.ac.jp/cgi-bin/koyomi/cande/da …

1899/12/31　00：00：00（＝シリアル値の0に相当する日付）のユリウス通日は、2414989.50 となりますので、シリアル値で示すことのできる日（＝1900/1/1以降）であれば、シリアル値にこの通日を加算すれば求められることがわかります。
1900/1/1より旧い日付の場合は、シリアル値では表せないので、日数を数値として計算して減算するような計算で対応する必要があります。

ひとまず、そこまでは必要ないものと考えれば、上述のように
　ユリウス通日 ＝ 日付のシリアル値 + 2414989.5
で求められます。
ただし、「シリアル値」は世界標準時換算であるものとします。

結果が実数のままでも良ければそのままでOKですし、時間部分を時刻表示にしたければ、少数以下の数値だけを切り出して、エクセルの書式で時刻表示すれば良いでしょう。
（日数は、少数以上の部分がそのまま日数になります）

この回答へのお礼

回答ありがとうございます。
現在、シリアル値にする作業で行き詰っています。もう3日もやってほとんど進んでいません。

お礼日時：2019/09/09 20:15

No.3 回答者： tatsu99 回答日時： 2019/09/09 15:26

＞219年ではありません。

すいません！
時刻は日本標準時でよろしいのですね。そのつもりでの回答です。（補足要求の回答がないので・・・）
D２はシリアル日付にできませんでしょうか。
セルD2に2017/7/1と入力し、書式設定で日付にします。（そうすると書式設定を標準にすると４３６４７の数字になります）

この状態で、
ユリウス日を表示したいセルに
=IF(AND(LEFT(CELL("format",D2),1)="D",INT(SUM(D2))=D2,SUM(D2)>1,LEFT(CELL("format",M2),1)="D",ISNUMBER(M2),SUM(M2)>0,SUM(M2)<2),D2+M2-"1904/1/1"+2416480.125,"")
と入力すればOKです。

もし、どうしても、D2をシリアル日付にできないなら（数値で20190701と書きたいなら）その旨、補足してください。
その場合は、D2を一旦シリアル日付に変換し（X2がその日付とすると）X2とM2からユリウス日を算出する案を提示します。
（X2はこちらで提示したセルですが、ほかのセルを望むならその旨提示してください）

No.2 回答者： tatsu99 回答日時： 2019/09/09 13:46

＞似たような質問をみつけて以下のようにかいてみたのですが、ダメでした。

関数の意味もよくわかりませんでした。お手上げ状態です。
=IF(AND(LEFT(CELL("format",D2),1)="D",INT(SUM(D2))=D2,SUM(D2)>1,LEFTCELL"format",M2),1)="D",ISNUMBER(B1),SUM(B1)>0,SUM(B1)<2),D2+M2"1904/1/1"+2416480.125,"")

私が、正解を回答できる保証はありませんが、補足要求です。
この関数式では、西暦219年7月01日では、あまりにも古い日付なので対応できません。（上記の関数はシリアル日付を前提にしています）
本当に西暦219年のユリウス日がほしいのでしょうか。
もし、そうだとして、変換対象となる西暦の範囲は何年から何年までなのでしょうか。
また、時刻は、日本標準時刻の時刻ですか、それとも、世界標準時の時刻ですか。

No.1 回答者： fujillin 回答日時： 2019/09/08 17:18

こんにちは

＞セルD２に2190701と年月日
これはユリウス日なのでしょうか？
（にしても随分旧い）
太陽暦にしては記述ルールがよくわからないですし…

その辺りに加えて、入力値が文字列なのかシリアル値なのか等もハッキリさておけば、回答がつくものと思います。

この回答へのお礼

わかりにくくてすいません。
D2に太陽暦で年月日で標準書式
M2に時刻で書式は時刻です。

似たような質問をみつけて以下のようにかいてみたのですが、ダメでした。関数の意味もよくわかりませんでした。お手上げ状態です。
=IF(AND(LEFT(CELL("format",D2),1)="D",INT(SUM(D2))=D2,SUM(D2)>1,LEFTCELL"format",M2),1)="D",ISNUMBER(B1),SUM(B1)>0,SUM(B1)<2),D2+M2"1904/1/1"+2416480.125,"")
よろしくお願いいたします。

お礼日時：2019/09/08 19:38