Julian to VB Date and Back - Tip of the Month

[Larry Rebich]

From time-to-time I have noticed, on the various news groups, a request to
convert a Julian date to a date. Julian dates are represented in the format
yyddd, where yy is the year and ddd is the day of the year. For example:
01001 is January 1, 2001, 01365 is December 31, 2001. I wrote a class module
that will convert a Julian date to its equivalent VB date and back.

Link to http://www.buygold.net/tips to view the tip and download a demo.



Cheers,
Larry Rebich


More tips link to:
http://www.buygold.net/tips

Please:
No personal e-mail questions :-)

[Jeremy Bruening]

"Larry Rebich" <lrebich@earthlink.net> wrote:
>From time-to-time I have noticed, on the various news groups, a request
to
>convert a Julian date to a date. Julian dates are represented in the format
>yyddd, where yy is the year and ddd is the day of the year. For example:
>01001 is January 1, 2001, 01365 is December 31, 2001. I wrote a class module
>that will convert a Julian date to its equivalent VB date and back.
>
>Link to http://www.buygold.net/tips to view the tip and download a demo.
>
>
>
>Cheers,
>Larry Rebich
>
>
>More tips link to:
>http://www.buygold.net/tips
>
>Please:
>No personal e-mail questions :-)
>
>
>
>
So why can't you just do this:

Format("05/15/2001", "yy") & Format(DatePart("y", "05/15/2001"), "000")

This gives you "01135", and then you use this to convert it back.

DateAdd("y",RIGHT("01135",3) - 1, FORMAT("01/01/" & LEFT("01135",2),"mm/dd/yyyy"))

Sounds like you have to much time on your hands. The VB Date function handles
everything for you

[Larry Rebich]

Jeremy,

Thanks for the insight. The function you described will work for users how
use the dd/mm/yy or dd/mm/yyyy format. If you don't need to worry about the
rest of the world then your functions will work very nicely. Software I
create and distribute is used in many countries including those that use the
mm/dd/yy or mm/dd/yyyy format and others. I use yyyy/mm/dd.

> Sounds like you have to much time on your hands.
I try to be helpful. I didn't appreciate the comment.

Cheers,
Larry Rebich

More tips link to:
http://www.buygold.net/tips.html

Please:
No personal e-mail questions :-)


"Jeremy Bruening" <jbrue24@yahoo.com> wrote in message
news:3b014cee$1@news.devx.com...
>
> "Larry Rebich" <lrebich@earthlink.net> wrote:
> >From time-to-time I have noticed, on the various news groups, a request
> to
> >convert a Julian date to a date. Julian dates are represented in the
format
> >yyddd, where yy is the year and ddd is the day of the year. For example:
> >01001 is January 1, 2001, 01365 is December 31, 2001. I wrote a class
module
> >that will convert a Julian date to its equivalent VB date and back.
> >
> >Link to http://www.buygold.net/tips to view the tip and download a demo.
> >
> >
> >
> >Cheers,
> >Larry Rebich
> >
> >
> >More tips link to:
> >http://www.buygold.net/tips
> >
> >Please:
> >No personal e-mail questions :-)
> >
> >
> >
> >
> So why can't you just do this:
>
> Format("05/15/2001", "yy") & Format(DatePart("y", "05/15/2001"), "000")
>
> This gives you "01135", and then you use this to convert it back.
>
> DateAdd("y",RIGHT("01135",3) - 1, FORMAT("01/01/" &
LEFT("01135",2),"mm/dd/yyyy"))
>
> Sounds like you have to much time on your hands. The VB Date function
handles
> everything for you

[Douglas J. Steele]

Not wanting to flame to fires, Larry, but you may have dismissed Jeremy's
suggestion a little quickly. I think there is some merit in what he's
presented (although I agree it could have been better worded).

Dates are stored internally as floating point values, where the integral
part represents the number of days relative to 30 Dec., 1899, and the
decimal part represents the time as a fraction of a day (6:00 AM is .25,
Noon is .5, 6:00 PM is .75, and so on) Issues like mm/dd/yyyy, dd/mm/yyyy
and yyyy/mm/dd are strictly formatting issues: they shouldn't affect the
underlying algorithm.

The approach Jeremy suggested to get the Julian Date in the first place:

Format("05/15/2001", "yy") & Format(DatePart("y", "05/15/2001"), "000")

is perfectly valid, and doesn't rely on regional settings.

As you correctly pointed out, his suggestion for converting it back relies
on a specific regional setting format (although either mm/dd/yy or dd/mm/yy,
since it's trying to coerce into Jan. 1st). I believe you can get around
this by delimiting the date with # characters: when you do that, the dates
are assumed to be in mm/dd/yyyy format regardless of regional settings. Even
easier, though, is to use the DateSerial function.

DateSerial(1,1,135) will, in fact, give you 2001/05/15 as the date.
Therefore, strip off the year as the first parameter, always use 1 as the
second paramter, and use the last 3 characters as the third parameter.

HTH

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


"Larry Rebich" <larry@buygold.net> wrote in message
news:3b028fc9@news.devx.com...
> Jeremy,
>
> Thanks for the insight. The function you described will work for users how
> use the dd/mm/yy or dd/mm/yyyy format. If you don't need to worry about
the
> rest of the world then your functions will work very nicely. Software I
> create and distribute is used in many countries including those that use
the
> mm/dd/yy or mm/dd/yyyy format and others. I use yyyy/mm/dd.
>
> > Sounds like you have to much time on your hands.
> I try to be helpful. I didn't appreciate the comment.
>
> Cheers,
> Larry Rebich
>
> More tips link to:
> http://www.buygold.net/tips.html
>
> Please:
> No personal e-mail questions :-)
>
>
> "Jeremy Bruening" <jbrue24@yahoo.com> wrote in message
> news:3b014cee$1@news.devx.com...
> >
> > "Larry Rebich" <lrebich@earthlink.net> wrote:
> > >From time-to-time I have noticed, on the various news groups, a request
> > to
> > >convert a Julian date to a date. Julian dates are represented in the
> format
> > >yyddd, where yy is the year and ddd is the day of the year. For
example:
> > >01001 is January 1, 2001, 01365 is December 31, 2001. I wrote a class
> module
> > >that will convert a Julian date to its equivalent VB date and back.
> > >
> > >Link to http://www.buygold.net/tips to view the tip and download a
demo.
> > >
> > >
> > >
> > >Cheers,
> > >Larry Rebich
> > >
> > >
> > >More tips link to:
> > >http://www.buygold.net/tips
> > >
> > >Please:
> > >No personal e-mail questions :-)
> > >
> > >
> > >
> > >
> > So why can't you just do this:
> >
> > Format("05/15/2001", "yy") & Format(DatePart("y", "05/15/2001"), "000")
> >
> > This gives you "01135", and then you use this to convert it back.
> >
> > DateAdd("y",RIGHT("01135",3) - 1, FORMAT("01/01/" &
> LEFT("01135",2),"mm/dd/yyyy"))
> >
> > Sounds like you have to much time on your hands. The VB Date function
> handles
> > everything for you
>
>