Hi all,

I have a whole bunch of Julian dates which I want to convert to Gregorian Dates. They are all in the period before 1900. Currently, I have the year, month and day each in a separate cell in Excel. Any tips how to do this?

Cheers!

wrote in message ...
Hi all,

I have a whole bunch of Julian dates which I want to convert to Gregorian Dates. They are all in the period before 1900.
Currently, I have the year, month and day each in a separate cell in Excel. Any tips how to do this?

Cheers!

Hi.

When you say Julian dates, I take it to mean dates in the julian calendar
(named after Julius Caesar), which has a leap day in every year divisible by 4.
The julian calendar was used in the middle ages,
and for example the Battle of Hastings was on 14 oct 1066
according to the history books.
This converts to Saturday 20 oct 1066 gregorian,
or rather gregorian extended backwards.

Suppose you have the julian day, month, year in A1:C1
From this calculate the day-number, and weekday, gregorian day, month, year in D1:I1
Day-number in D1 (will be negative before 1900)
=INT((C1-(B1<3))*1461/4)+INT(MOD(B1-3,12)*153/5+1/2)+A1-693902
Weekday:
=TEXT(MOD(D1-WEEKDAY(0),7),"dddd")
Day (gregorian):
=DAY(MOD(D1-1462*(WEEKDAY(0)=6),146097)+146097)
Month (gregorian):
=MONTH(MOD(D1-1462*(WEEKDAY(0)=6),146097)+146097)
Year (gregorian):
=YEAR(D1-1462*(WEEKDAY(0)=6)-INT(D1/146097-1)*146097)+INT(D1/146097-1)*400

If your years are before Christ, dont use the BC convention of historians,
use negative years as used by astronomers.
For example 2 BC is year -1; the ancient romans did not have a year 0,
as astronomers do now. The introduction of 0 took a long time!

The julian to gregorian conversion also took a long time,
Pope Gregory in Italy started it in 1582,
but the protestant countries waited stubbornly until about 1700,
and Turkey waited until 1927.
By that time the Oriental Express had been running for about 40 years!
Hopefully railway men used gregorian.

Hans T.