Excel MJD formula

[Ripley]

I "proudly" present my latest XL creation (made with Excel 2003).

It's just an MJD conversion formula I made with the help of Wikipedia and various cross-checks websites and Orbiter's own "date.exe" utility.

XL sheet is nothing more than a draft and follows Wikipedia's page calculations and my own "findings".

Nevertheless, there is a "one-cell" formula that sums all the bits and pieces up, and it only needs a date in a cell and an (optional) hour in another.
If you need it, use it.

This will probably be my first and last post in the Math sub-forum, where I don't really belong...I just like fiddling with XL.

Algorithm is here
http://en.wikipedia.org/wiki/Julian_day#Calculation
More infos
http://www.ehow.com/how_5121943_calculate-mjd.html


Download MJD dates in Excel

 

[Michael_Chr]

Was looking for a formula to do the mjd ><gregorian date/time conversion within excel. But the link above seems to point to an italian site where its not that obvious where the mjd conversion tool is.
Any chance of a reposting
Best regards
Michael

 

[Ripley]

Give me a couple of hours and I'll see into it.

---------- Post added at 23:41 ---------- Previous post was at 20:40 ----------

Done!
Link corrected in the first post. It opens a description page.
Of course you have to click on the filename, at the top-center of the screen (MJDfinal.zip).

Hope it can be of some use.

Sorry for the mishap, we updated the whole Tuttovola.org website last summer, and this was one of the (many) links I forgot to update.

 

[Michael_Chr]

Thanx a bunch Ripley
Got it to work like a charm
I experienced one problem though with two cells returning bad value but the culprit was found to be due to a regional setting (the , or . problem). Apparently my settings did not match the settings of the spreadsheet. This is not a problem of the spreadsheet though.
For other users that tries this spreadsheet that experience this try looking into formulas in cells that return illigal vaule and try to find entries in the formula that looks like "," or "." and change it to the opposite value (so "." changes to "," and vice versa - worked for me).

 

[Ripley]

In Italy our decimal separator is comma and not period.
Do you mean I used both "," and "."? I don't understand.

BTW, isn't it the same in Denmark?

 

[Michael_Chr]

Tried to modify my posting above to make it clearer. And yes...the setting for , and . are the same in DK as in IT. These settings on my system I change regularly in order to test various software

 

[Urwumpe]

Do you really need a complex formula for that? Shouldn't it be just Date1 - Date Reference, converted into a floating point number? (after all, office internally represents all dates as Single data type, a single precision floating point number, counting the days since 1900)

 

[Michael_Chr]

Do you really need a complex formula for that? Shouldn't it be just Date1 - Date Reference, converted into a floating point number? (after all, office internally represents all dates as Single data type, a single precision floating point number, counting the days since 1900)

Urwumpe:
Maybe you are right. Maybe the only thing you need to do to get to MJD is to offset the MJD from the startdate since they are both floating point type of data. Will investigate in the coming week.

Keine hexerei - nur behändichheit :thumbup:

 

[Ripley]

Do you really need a complex formula for that?...

I really don't know.
As already said, I'm no math-minded at all, but I enjoy playing with XL and I just followed what those sites suggested.

If there's an easier formula (IF we need one at all), I'd be happy to read it on these pages.

 

[shayam]

hi guys! i read all the post! i am here with request to help in calculating MJD values to Normal values.
Example: DD 91 14 10 00 ----> 5 March 2014, 14h10

I have done manaul calculation but i want to excute it in excel. please help me.

EXAMPLE: DD 91 14 10 00


0 1 2 3 4
DD 91 14 10 00

mjd = (dvb_buf[0] & 0xff) << 8; è DD
mjd += (dvb_buf[1] & 0xff); è mjd = mjd + (dvb_buf[1] & 0xff)

so now mjd will be DD91

hour = bcdtoint(dvb_buf[2] & 0xff); è 14
min = bcdtoint(dvb_buf[3] & 0xff); è 10
sec = bcdtoint(dvb_buf[4] & 0xff);è 00

è year = (int) ((mjd - 15078.2) / 365.25);

= (int) (56721 – 15078.2) / 365.25
= (int) (41642.8 / 365.25)
= (int) (114.011)
(Note: int wil get only the integer values will eliminate the decimal values so answer is 114
Year = 114 (1900 + 114 = 2014)

è month = (int) ((mjd - 14956.1 - (int) (year * 365.25)) / 30.6001);
= (int) ((56721 – 14956.1 – (int) (114*365.25)) / 30.6001);
= (int) ((56721- 14956.1 – (int) (41638.5))/30.6001);
= (int) ((56721- 14956.1 – 41638)/30.6001);
= (int) (126.9/30.6001);
= (int) (4.14);
Month = 4

è day = mjd - 14956 - (int) (year * 365.25) - (int) (month * 30.6001);

= 56721 – 14956 – (int) (114 * 365.25) – (int) (4 * 30.6001);
= 56721 - 14956 – (int) (41638.5) – (int) (122.4004);
= 56721 - 14956 – 41638 – 122;
DAY = 5

è if (month == 14 || month == 15)
i = 1;
else
i = 0;

we have the month as “4”, so for us i = 0
year += i; è Year = year + i è year = 114 +0 è year =114
month = month - 1 - i * 12; è month = 4 -1 – (0*12)à month = 4 -1 = 3
So the year is 1900+114 = 2014, month =3(March), day = 5, hour = 14, minutes=10, sec =00