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