	AllExperts > Using MS Access
	Search

About Scottgem
Expertise
I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience
I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Covert number of days to date

Using MS Access - Covert number of days to date

Expert: Scottgem - 7/9/2009

Question
Hi, i have this issue of converting days to date.
eg 18809 which is: 188 are days starting from first January and the 09 is the year 2009.

so i need converting this: 18809 to 7 July 2009.

thank you

Answer
This is assuming, the value is a text field, if not it need to be connverted to one.

This would be easier to follow as a function rather than as an expression so:

Public Function ConvertJulian(strdate As String) As Date

Dim intDays As Integer
Dim strYear As Sttring, intYear As Integer

strYear = Right(strDate,2)
intYear = CInt("20" & strYear)
intDays = Cint(Left(strDate,Len(strDate)-2))

ConvertJulian = DateSerial(intYear,1,1)
ConvertJulian = DateAdd("d",intDays,ConvertJulian)

End Function

So what this code does is extract the year and number of days from the julian date. It then adds those days to the first day of the year.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Add to this Answer Ask a Question