Prev: Check Control if Visible or hidden
Next: Query/subquery in form
From: Reggie on 4 Mar 2010 23:37
Hi and TIA! I've researched and tried many many conversion functions but
none give me the results. One Example


Public Function JDatetoDate(MyDate As Integer) As Date

JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
Left(year(Now()), 3) & Left(MyDate, 1))

End Function

?JDatetoDate(8044) returned 2/13/2018 expected 2/13/2008
?JDatetoDate(0044) returned 2/13/2014 expected 2/13/2010

Any ideas. Basically want to convert a 4 digit julian to a date/time and
then I will add 1 minute to it. Thanks!

--

Reggie

From: John W. Vinson on 5 Mar 2010 01:11
On Thu, 4 Mar 2010 21:37:52 -0700, "Reggie" <No_Spam_chief123101(a)yahoo.com>
wrote:

>Hi and TIA! I've researched and tried many many conversion functions but
>none give me the results. One Example
>
>
>Public Function JDatetoDate(MyDate As Integer) As Date
>
> JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
>Left(year(Now()), 3) & Left(MyDate, 1))
>
>End Function
>
>?JDatetoDate(8044) returned 2/13/2018 expected 2/13/2008
>?JDatetoDate(0044) returned 2/13/2014 expected 2/13/2010
>
>Any ideas. Basically want to convert a 4 digit julian to a date/time and
>then I will add 1 minute to it. Thanks!

You don't have a Y2K millenium problem - you have a decade problem! How do you
decide that 8 is 2008 as opposed to 2018, since the latter is in the current
decade? What would 2044 be: 2002 or 2012? How can you decide?

That said, you're applying the Left function to a Number, which won't
necessarily give you what you want. I'd use the DateSerial function instead:

DateSerial(10 * (Year(Date())\10) + Val(Left([MyDate], 1), 1,
Val(Right([MyDate], 3)))

This will return a date in the current decade. Since that isn't apparently
what you want, you may want to subtract 10 years from the first expression
under certain circumstances that you would understand better than I do!
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Check Control if Visible or hidden
Next: Query/subquery in form