将朱利安日期（5或4个字符）转换为公历日期，反之亦然。

Question

我正在尝试创建一个VBA脚本或公式/函数，将儒略日（5001或15001）转换为常规日期（01/01/2015）。我能找到以下公式将5位数的儒略日转换为格里高利日期，它可以正常工作。我不是数学家，所以我不知道如何编写4位数JD的公式，也不知道如何开始反向操作（常规日期转JD）。非常感谢您对此事的任何帮助。

5位数JD的公式在此处：

Assuming that the Julian Date is in A1:


=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

感谢您提前的任何帮助。

- flwr_pwr

Answer 1

您需要测试目标单元格的长度，以确定其是否为4或5个字符长（为了清晰起见，换行）：

=DATE(IF(0+(VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))))<30,2000,1900)
      +VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))),1,RIGHT(A1,3))

反过来就容易多了 - 只需将日期部分组合成一个数字，然后将结果字符串转换为数字以消除任何前导零：

=VALUE(RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000"))

编辑： 根据评论，如果一个四位数的儒略日的第一位数字小于或等于当前年份的最后一位数字，则以下方法将使用当前十年代，否则使用200x年代：

=DATE(IF(0+(VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))))<30,
      IF(LEN(A1)=5,2000,IF(LEFT(A1,1)<=RIGHT(YEAR(NOW()),1),2010, 2000)),1900)
      +VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))),1,RIGHT(A1,3))

差点忘了……如果您需要公式在2019年之后仍然有效，可以用2000+（MID（YEAR(NOW()),3,1)*10）来替换2010。

- Comintern

---

谢谢你的回复。你提供的根据JD转换到公历的公式基本上有效，但是其中存在错误。例如，如果JD是4位数，并且如下所示：'4001'，那么对应的日期应该是01/01/2014；然而，如果是'9001'，则对应的日期应该是01/01/2009。所以它必须是过去的某一天，但是是最近的年份。因此，如果以5开头，则表示2015年，但6表示2006年。明白我的意思吗？我无法弄清楚这个转换。这真让我苦恼。 - flwr_pwr

@flwr_pwr - 我以为4位数的JD的标准约定是它有一个隐含的前导零。如果4001代表1/1/14，那就无法区分它和1/1/04、1/1/24等日期了。 - Comintern

在某些系统中，这是准确的，所以你绝对没有错！但我们使用的系统遵循我刚才提到的标准。抱歉，我应该一开始就提到这一点。这确实让事情变得更复杂！ - flwr_pwr

@flwr_pwr - 好的，所以我理解得对 - 如果第一个数字小于或等于当前年份的最后一个数字，那么它就属于现在这个十年？这难道不会让你每年的数据都失效吗？ - Comintern

这是准确的，这也是准确的。对于支持4个字符的JD系统，凭证文件只需保存10年，因此这些记录将被历史归档到旧系统中，然后转换为常规的5个字符的JD。希望我表达得足够清楚，请告诉我。 - flwr_pwr

Answer 2

你可以从this页面使用这个公式：

=DATE(IF(0+(LEFT(JULIAN_DATE_CELL,2))<30,2000,1900)+LEFT(JULIAN_DATE_CELL,2),1,RIGHT(JULIAN_DATE_CELL,3))

你需要将JULIAN_DATE_CELL替换为存储儒略日期的单元格编号。

- bman

---

谢谢回复，但是这个公式是我在文章里找到的用来计算五位数日期的。我现在在努力计算四位数的儒略日期和公历日期之间的转换。 - flwr_pwr

Answer 3

你需要处理2000年之前的日期吗？如果不需要，你可以使用以下公式：

=DATE(IF(LEN(A1)=5,LEFT(A1,2)+100,LOOKUP(YEAR(NOW()),FLOOR(YEAR(NOW()),10)+LEFT(A1)-{10,0})),1,RIGHT(A1,3))

这个公式使用了LOOKUP函数来查找当前年份与可能选项的对应关系，例如，如果A1单元格中有6001，它会在类似{2006,2016}的数组中查找2015，并选择较小的值。如果明年进行同样的计算，将会选择2016。

这个公式将在未来继续有效，例如，在2020年，它将把9001解释为2019年1月1日。

对于反向计算，你是否总是希望得到一个4位数的JD，那么2010或2020怎么办？

- barry houdini