Calculate Gregorian Julian Date from Regular Date in Excel

2

1

I am trying to get a Julian Date from a regular date (mm/dd/yyyy) in Excel. There seems to be two kinds of Julian date calculation.

  1. One is described Here..
  2. The other is somehow related to Gregorian date and is described here.

If I use the following formula in Excel. I get the result as shown below. 

=TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

For 1/20/2017, I get this value 17020.

enter image description here

However, if I use the second resource, for the same date, I get the following value.

gregorian julian date

I am looking for this second value (2457774). I did much research online to find Excel formula for the second method. But I cannot find any.

Can someone please help me to get this formula in Excel? really appreciate your help.

Cricrazy

Posted 2019-11-21T17:32:36.493

Reputation: 826

If you are looking for how that value is computed you can check the JavaScript source: http://stevegs.com/utils/jd_calc/jd_calc.js

– Titan – 2019-11-21T17:46:43.917

Here is a major hint: The Julian Calendar is 17 days behind the Gregorian. What you are actually looking for is considered a Modified Julian Day You can convert from MJD by adding a 2400000.5 to the number of days since the start of the Julian Calendar. The reason you covert from MJD is due to the fact Julian starts in 4058 BC which excel doesn’t handle and MJD starts in 1858. In both cases you have to subtract 17 days. You can convert from Gregorian and Julian knowing these facts easily enough. – Ramhound – 2019-11-21T18:10:43.587

@ramhound It's more complicated than that and it's never been 17 days." The Julian calendar day Thursday, 4 October 1582 was followed by the first day of the Gregorian calendar, Friday, 15 October 1582" (10 days). In 1752 when the UK adopted the GC the difference was 11 days.

– DavidPostill – 2019-11-21T18:33:43.827

1

@DavidPostill - David; I work exclusively with Julian and Modified Julian Dates. I have C# Code that will calculate the current Julian day and MJD from the current Gregorian day. Today in Julian Calendar is November 8th.. I don’t know what to tell you, I know for a fact, the current offset is 13 days how I know this. I thought it was 17 days, my notes in my private GitHub repository, says that but NASA says otherwise.

– Ramhound – 2019-11-21T18:45:18.890

1Correct. The current offset is 13 days. The point I was trying to make is that the GC started on vastly different days depending on what country you are in. You have to make an assumption (given a Gregorian day in history) about the country to get the correct offset. – DavidPostill – 2019-11-21T18:53:11.770

Answers

0

I am trying to get a Julian Date from a regular date (mm/dd/yyyy) in Excel

Note: What you asking for is actually the Julian Day Number, which is not the same as the Julian Date (which is a date in the Julian Calendar)

The Julian Day Number is defined as the day count since 4713 BC (plus 0.5 if you are an astronomer).

The Julian Day Number for any date (at 0:00 GMT) in the Gregorian calendar (which started on 15-Oct-1582) is calculated using the following formula:

=367*Y - INT(7*(Y + INT((M+9)/12))/4) - INT(3*(INT((Y+(M-9)/7)/100)+1)/4) + INT(275*M/9) + D + 1721028.5

Source Julian Calendar Date Conversion Formulas - Excel Formulas

Full definition of Julian Day Number

The Julian Day Number (JDN) is the integer assigned to a whole solar day in the Julian day count starting from noon Universal time, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar), a date at which three multi-year cycles started (which are: Indiction, Solar, and Lunar cycles) and which preceded any dates in recorded history.

For example, the Julian day number for the day starting at 12:00 UT on January 1, 2000, was 2 451 545.

The Julian date (JD) of any instant is the Julian day number plus the fraction of a day since the preceding noon in Universal Time. Julian dates are expressed as a Julian day number with a decimal fraction added.

For example, the Julian Date for 00:30:00.0 UT January 1, 2013, is 2 456 293.520 833.

Expressed as a Julian date, right now it is 2458809.1344907.

Source Julian day - Wikipedia

DavidPostill

Posted 2019-11-21T17:32:36.493

Reputation: 118 938

this is awesome. Thank you for the detailed answer. – Cricrazy – 2019-11-21T20:35:25.913

Asked: 2019-11-21T17:32:36.493

Viewed: 65 times

Active: 2019-11-21T18:26:01.913