return day of year (julian day)

[y2k]

I work for an insurance company and we use the julian calendar to track the age of claims. So so for example, a claim will be assigned 03032 if it was received on 1st Feb '03 - this is then used as the 1st 5 digits of the claim# assigned to it.

What I need to do is create a query which will return today's julian day and subtract from it the julian day of the date it was received and return it as an intiger. How can I go about doing that, I'm not very well up on VBA unfortunately?

Any help would be greatly appreciated.

 

[simongallop]

Don't knw much about Julian but where does 03032 come from? Julian day count for 1st Feb 2003 is 245 2671. If you can explain the calc that returne 03032 then might be able to help

 

[y2k]

julian day counts the number of the day in the year, so 001 would be January 1st, 032 would be February 1st because it's the 32nd day of the year. We just append 03 to the start of that so that we can also record the year.

 

[Rich]

Article ID: Q162745 should help

 

[Mile-O]

Function ConvertJulian(ByVal strDate1 As String, ByVal strDate2 As String) As Integer

    Dim strYear(1 To 2) As String, intDays(1 To 2) As Integer
    Dim dteDates(1 To 2) As Date, intCounter As Integer

    For intCounter = 1 To 2
        strYear(intCounter) = IIf(intCounter = 1, Left(strDate1, 2), Left(strDate2, 2))
        intDays(intCounter) = IIf(intCounter = 1, CInt(Right(strDate1, 3)), CInt(Right(strDate2, 3)))
        dteDates(intCounter) = DateAdd("d", intDays(intCounter), DateSerial(strYear(intCounter), 1, 1))
    Next intCounter
    
    ConvertJulian = Abs(DateDiff("d", dteDates(1), dteDates(2)))
    
End Function

This function will do what you are asking for.

You send two dates in your Julian format to it (as strings) and it well return the difference in days.