CODEWITHSUNDEEP
exceltimeexcel-formula
Ibrahim Ahmed
Ibrahim Ahmed

Reputation: 3

Convert Modified Julian Date to UTC Date in Excel

I am looking for a formula where I can put a 5 digit Modified Julian Date and have it convert it to today's date for me.

I did it in a way where I have a separate sheet with a table but although it works, I am looking for a way without the need of a separate table. Here is some information:

00000 should translate to 1858-11-17

58890 should translate to 2020-02-11

Every time the number goes up 1, it should go up 1 day. This needs to have leap years in consideration as well. Any help will be appreciated.

Here is a website that currently does the conversions:

http://www.csgnetwork.com/julianmodifdateconv.html

This has to be done without macros, literally need it in formula format.

Upvotes: 0

Views: 2107

Answers (2)

François
François

Reputation: 1

Yes or no…

It was in the Middle Ages that the French Joseph Juste Scaliger (1540-1609), proposed in 1583 in his treatise on chronology “Opus novum de emendatione temporum”, a calendar based on the counting of days called “Julian days". He proposed to set as the origin of his calendar January 1 ~4713 at noon. (~ means that we do not take into account year 0. ~4713 = -4712 BC)

For convenience, since 1973, we have used November 17, 1858 at 0 a.m. as the origin of the Julian calendar, which we will call “Modified Julien Date”. The MJD was chosen by subtracting 2400000.5 from January 1 -4712 in order to use smaller numbers and above all bring the time back to 0h instead of noon (0.5 day). Today the MJD is given with 5 digits.

Today for simplification, it was agreed that:

  1. Unix systems, start on January 1, 1970 at midnight.
  2. Excel for Windows start on January 1, 1900 at midnight.
  3. Excel for Mac up to and including version 2008 would start on January 1, 1904 at midnight.
  4. Mac versions of Excel after version 2008 start on January 1, 1900 at 12 a.m.
  5. 1 Julian year = 365.25 days (leap year)

UNIX:

  • The standard Unix calendar starts on 01/01/1970.
  • The MJD calendar begins on 11/17/1858.
  • Number of days between 11/17/1858 and 12/31/1858 = (30-17)+31=44
  • Number of days between 01/01/1859 and 01/01/1970 = (1970-1859)*365.25=40542.75
  • Number of days between 11/17/1858 and 01/01/1900 = 44+40542.75=40586.75
  • Rounding up, we will use 40587 days as the difference between the MJD date and the unix date.
  • so: current date (Unix)= MJD + 40587

for example : for ntp, in loopstats file, first field is MJD to read this file : awk '{ printf "%s -- %s\n", strftime("%c", ($1-40587)*86400 + $2), $0 }' /var/log/ntpsec/loopstats

Excel:

  • The standard Excel calendar starts on 01/01/1900.
  • The MJD calendar begins on 11/17/1858.
  • Number of days between 11/17/1858 and 12/31/1858 = (30-17)+31=44
  • Number of days between 01/01/1859 and 01/01/1900 = (1900-1859)*365.25=14975.25
  • Number of days between 11/17/1858 and 01/01/1900 = 44+14975.25=15019.25
  • Rounding up, we will use 15018 days as the difference between the MJD date and the unix date.
  • so: current date (Excel)= MJD - 15018

just type the formula in a cell and apply the date format to this cell: =A1-15018

For summarize:

For Unix/Debian:

In /var/log/ntpsec/loopstats file, first field ($1) = MJD on 5 numbers, second field ($2) = time

awk '{ printf "%s -- %s\n", strftime("%d/%m/%y %H:%M:%S", ($1-40587)*86400 + $2), $0 }' /var/log/ntpsec/loopstats

For Excel:

H5 = MJD on 5 digits and I5 = time

Date = H5-15018 (and apply a date format to the cell)

Time = I5/86400 (and apply a time format to the cell)

So, conclusion:

MJD = 58890 => February 11, 2020 it’s ok for me.

But as I wrote above, Excel only knows the dates from January 1, 1900. As proof, define the date format of a cell with the format dddd mmmm yyyy then indicate in this cell 01/01/1900, Excel will display Sunday, January 1, 1900. But if you indicate in this same cell 31/12/1900 Excel will indicate 31/12/1900 without indicating the name of the day, proof that is not able to calculate that. (I'm French, the date for me is day/month/year, but that doesn't change the problem)

Yes MJD = 00000 => November 17, 1858 but Excel cannot calculate it with basic functions. If you want to do this, you need to make a program to do that.

Upvotes: -2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

Just subtract 15018 and format the result as a date.

Why 15018 and not 15020?

  • In Excel, 1-Jan-1900 value is 1
  • In your date scheme 15020 = 1-Jan-1900 But, if you had the number 15020 to convert and subtracted 15020 it would --> 0, not the desired 1.
  • Therefore we reduce the 15020 by 1 to 15019.

Next, there is a deliberate "bug" in Excel, widely discussed both on SO and the internet, whereby the year 1900 is erroneously classified as a leap year.

So for any date equivalent that is after 28-Feb-1900, we have to subtract an extra 1.

If you might be dealing with dates between 1/1/1900 and 2/28/1900 you will need to modify the formula to take that into account.

Upvotes: 2

Related Questions