I need to be able to calculate the number of day between two dates. One
date will be a BC date and the other and AD date.

Example
How many days are there from 399 BC to 1202 AD?

Thanks for the help.



*** Sent via Developersdex http://www.developersdex.com ***

On Sun, 21 Aug 2005 13:05:45 -0700, Tim Russell wrote:

I need to be able to calculate the number of day between two dates. One
date will be a BC date and the other and AD date.

Example
How many days are there from 399 BC to 1202 AD?

Thanks for the help.



*** Sent via Developersdex http://www.developersdex.com ***

This is really a fascinating question, and the solution may depend, in part, on
your location.

In the time frame you pose as an example, multiple calendars were in use, and
years had differing numbers of days. For example, the Julian calendar started
about 45 B.C.E.; in the 1500's, various countries began to change over to the
Gregorian calendar because of problems with synchronicity with the solstices
due to the way the Julian calendar handled leap years. But some did not change
over until the 1900's.

Prior to 45 B.C.E. the Roman calendar was basically a mess, and we can only
guess at it. The priests were supposed to keep track of things, but they did
not do a good job. Part of this was due to ignorance, part due to being bribed
to make certain years longer or shorter. In addition, leap years were
considered unlucky so were avoided during times of crisis.

Perhaps the best solution would be to go to a calendar site and use one of
their tools, at least for the C.E. dates. For the B.C.E. dates you will have
to devise your own rules :-)

Good luck!

--ron

Tim,

You'll have to remember that there was no year 0 (unless you're Pol Pot and
living in Cambodia) so a simple addition of the BC year and the AD year
won't work.

Henry

"Tim Russell" wrote in message
...
I need to be able to calculate the number of day between two dates. One
date will be a BC date and the other and AD date.

Example
How many days are there from 399 BC to 1202 AD?

Thanks for the help.



*** Sent via Developersdex http://www.developersdex.com ***

On Sun, 21 Aug 2005 13:05:45 -0700, Tim Russell wrote:

I need to be able to calculate the number of day between two dates. One
date will be a BC date and the other and AD date.

Example
How many days are there from 399 BC to 1202 AD?

Thanks for the help.



*** Sent via Developersdex http://www.developersdex.com ***

Giving some more thought to your problem, one could convert both dates into
astronomical Julian dates, and then subtract one from the other. This will
work for dates after 1 Jan 4713 BCE (Day 0 in this system)

Although the algorithm lends itself to worksheet functions, I chose to write a
UDF as it is easier for me to debug.

Data entry is critical:

Dates must be entered in the format M/D/Y (US Style)

The year must be entered as the full year.

If the year is prior to year 1, it must be preceded by a minus sign. In other
words 1 jan 10 BCE must be entered as 1/1/-10

Dates in the first century must be entered as TEXT, or else Excel will convert
them to dates in the 20th century. e.g. enter 1 Jan 99 as '1/1/99 or
pre-format the cell as TEXT. Otherwise excel will convert it to 1/1/1999.

The default start of the Gregorian calendar is 15 Oct 1582 but the function
will take an optional argument of a date if you choose a different start date
for that calendar. Enter this date as a string in the form of "M/D/Y"

To use this function, enter =JDate(cell_ref) into some cell where the date you
wish to convert is in cell_ref.

For your dates

A1: 1/1/-399 JDate: 1,575,689
A2: 1/1/1202 JDate: 2,160,089

Difference = 584,400 days

I think this'll work.

To enter the UDF, <alt-F11 opens the VB Editor. Insert/Module and then paste
the code below into the window that opens.

================================================== =
Function JDate(dt As Variant, _
Optional Gregorian_Calendar_Start_date = #10/15/1582#) As Double
Dim a As Long, y As Long, m As Long, d As Long
Dim MDY
MDY = Split(dt, "/")

'If UBound(MDY) < 2 Then Exit Function

d = MDY(1)
a = (14 - MDY(0)) \ 12
y = MDY(2) + 4800 - a
If MDY(2) < 0 Then y = y + 1
m = MDY(0) + 12 * a - 3

JDate = d + (153 * m + 2) \ 5 + 365 * y + y \ 4 - 32083

If CLng(MDY(2)) = 100 Then
If DateSerial(MDY(2), MDY(0), MDY(1)) = _
DateValue(Gregorian_Calendar_Start_date) Then
JDate = d + (153 * m + 2) \ 5 + _
365 * y + y \ 4 - y \ 100 + y \ 400 - 32045
End If
End If

End Function
====================================



==================================


--ron

On Sun, 21 Aug 2005 23:02:49 -0400, Ron Rosenfeld
wrote:

On Sun, 21 Aug 2005 13:05:45 -0700, Tim Russell wrote:

I need to be able to calculate the number of day between two dates. One
date will be a BC date and the other and AD date.

Example
How many days are there from 399 BC to 1202 AD?

Thanks for the help.



*** Sent via Developersdex http://www.developersdex.com ***

Giving some more thought to your problem, one could convert both dates into
astronomical Julian dates, and then subtract one from the other. This will
work for dates after 1 Jan 4713 BCE (Day 0 in this system)

Although the algorithm lends itself to worksheet functions, I chose to write a
UDF as it is easier for me to debug.

Data entry is critical:

Dates must be entered in the format M/D/Y (US Style)

The year must be entered as the full year.

If the year is prior to year 1, it must be preceded by a minus sign. In other
words 1 jan 10 BCE must be entered as 1/1/-10

Dates in the first century must be entered as TEXT, or else Excel will convert
them to dates in the 20th century. e.g. enter 1 Jan 99 as '1/1/99 or
pre-format the cell as TEXT. Otherwise excel will convert it to 1/1/1999.

The default start of the Gregorian calendar is 15 Oct 1582 but the function
will take an optional argument of a date if you choose a different start date
for that calendar. Enter this date as a string in the form of "M/D/Y"

To use this function, enter =JDate(cell_ref) into some cell where the date you
wish to convert is in cell_ref.

For your dates

A1: 1/1/-399 JDate: 1,575,689
A2: 1/1/1202 JDate: 2,160,089

Difference = 584,400 days

I think this'll work.

To enter the UDF, <alt-F11 opens the VB Editor. Insert/Module and then paste
the code below into the window that opens.

================================================= ==
Function JDate(dt As Variant, _
Optional Gregorian_Calendar_Start_date = #10/15/1582#) As Double
Dim a As Long, y As Long, m As Long, d As Long
Dim MDY
MDY = Split(dt, "/")

'If UBound(MDY) < 2 Then Exit Function

d = MDY(1)
a = (14 - MDY(0)) \ 12
y = MDY(2) + 4800 - a
If MDY(2) < 0 Then y = y + 1
m = MDY(0) + 12 * a - 3

JDate = d + (153 * m + 2) \ 5 + 365 * y + y \ 4 - 32083

If CLng(MDY(2)) = 100 Then
If DateSerial(MDY(2), MDY(0), MDY(1)) = _
DateValue(Gregorian_Calendar_Start_date) Then
JDate = d + (153 * m + 2) \ 5 + _
365 * y + y \ 4 - y \ 100 + y \ 400 - 32045
End If
End If

End Function
====================================



==================================


--ron


Hmmm. Further investigation suggests that the algorithm is probably only valid
for Gregorian Calendar Start Dates in the 1500's and 1600's. Later conversions
lost more days.

I'll look at that aspect a bit further, but not tonight.

Here's a slightly cleaned up version of the above:

===============================
Function JDate(dt As Variant, _
Optional Gregorian_Calendar_Start_date _
As Date = #10/15/1582#) As Double
Dim a As Long, y As Long, m As Long, d As Long
Dim MDY
MDY = Split(dt, "/")

'If UBound(MDY) < 2 Then Exit Function

d = MDY(1)
a = (14 - MDY(0)) \ 12
y = MDY(2) + 4800 - a
If MDY(2) < 0 Then y = y + 1
m = MDY(0) + 12 * a - 3

JDate = d + (153 * m + 2) \ 5 + 365 * y + y \ 4 - 32083

If CLng(MDY(2)) = 100 Then
If DateSerial(MDY(2), MDY(0), MDY(1)) = _
Gregorian_Calendar_Start_date Then
JDate = d + (153 * m + 2) \ 5 + _
365 * y + y \ 4 - y \ 100 + y \ 400 - 32045
End If
End If

End Function
=============================

--ron