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