Excel Dates to Access Dates (Julian)

[Michael Kintner]

Well, just discovered that excel really does not do Julian Dates but a calc
from the date 1/1/1900.

Does anybody have a function that converts excel date values into real dates
in an access database.

I have imported over 30 tables with excel funny ("Bill Gates") julian value.

Thank you in advance,
Mike

 

[Douglas J. Steele]

I've never had any problems importing dates from Excel to Access. What
problem are you encountering?

For what it's worth, Access stores dates in the same manner: they're 8 byte
floating point numbers, where the integer portion represents the date as the
number of days relative to 30 Dec, 1899, and the decimal portion represents
the time as a fraction of a day.

 

[Guest]

Jullian dates are a representative of the year (one to four digits) and the
numerical count of days of the current year. January 15 2005 is represented
as 5015, 05015, or 2005015.
If this is what you have in Excel then to convert to date in Access you need
to parse it like this --
Your Date:
DateSerial(Left([Jullian],4),Month(DateAdd("d",Val(Right([Jullian],3)),0)),Day(DateAdd("d",Val(Right([Jullian],3)),0))+1)

 

[Guest]

Dear Michael,

Depending on the Julian date format,

Try the following with Excel
2004060 =DATE(LEFT(A1,4),1,RIGHT(A1,3))
99060 =DATE((LEFT(A2,2)+IF(VALUE(LEFT(A2,2))<30,2000,1900)),1,RIGHT(A2,3))
4060 =DATE((LEFT(A3,1)+IF(VALUE(LEFT(A3,1))<10,2000,1900)),1,RIGHT(A3,3))

Apply the same formulae with Access

2004060 DateSerial(Left([Field name],4),1,Right([Field name],3))
99060 DateSerial(Left([Field name],2),1,Right([Field name],3))
You may need to apply the "If" statement if your julian date format is not
7-digits.

Hope it can help you. I have been using such formulae in both Excel and
access for conversion dates in different combination.

Fanny