subtracting AD and BC dates

On Sun, 21 Aug 2005 13:05:45 -0700, Tim Russell <tr2yhb@yahoo.com> 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 <ronrosenfeld@nospam.org>
wrote:

>On Sun, 21 Aug 2005 13:05:45 -0700, Tim Russell <tr2yhb@yahoo.com> 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