Julian Date

[Reggie]

Hi and TIA! I have a field that I want to convert from Julian to a short date. I've tried several
procedures, but can't come up with the right solution. What I'm trying to do is convert

4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for your
time!

--
Reggie

----------

[Tom van Stiphout]

On Fri, 17 Sep 2004 18:46:22 -0700, "Reggie"
<NoSpam_chief123101@NoSpam_yahoo.com> wrote:

DateAdd("d", 4023, #1/17/1993#)

-Tom.

Hi and TIA! I have a field that I want to convert from Julian to a short date. I've tried several
procedures, but can't come up with the right solution. What I'm trying to do is convert

4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for your
time!

[Allen Browne]

Is a Julian date the year concatenated with 3 digits representing the day of
the year?

If so, try something like this, where z is the field name:
DateAdd("d", Right([z], 3), DateSerial(Left([z], Len([z])-3), 1, 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:A7********************@comcast.com...

Hi and TIA! I have a field that I want to convert from Julian to a short
date. I've tried several procedures, but can't come up with the right
solution. What I'm trying to do is convert

4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would
appreciate it. Thanks for your time!

[Reggie]

Allen/Tom, Both of these produce the result I am looking for. Now I'm going to try to figure out
how they work. Thanks for your time!!!!

--
Reggie

----------
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Is a Julian date the year concatenated with 3 digits representing the day of the year?

If so, try something like this, where z is the field name:
DateAdd("d", Right([z], 3), DateSerial(Left([z], Len([z])-3), 1, 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:A7********************@comcast.com...

Hi and TIA! I have a field that I want to convert from Julian to a short date. I've tried
several procedures, but can't come up with the right solution. What I'm trying to do is convert

4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for
your time!

[Reggie]

Tom, Works great. One question though. How/why did you decide to use 1/17/93.

--
Reggie

----------
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:8f********************************@4ax.com...

On Fri, 17 Sep 2004 18:46:22 -0700, "Reggie"
<NoSpam_chief123101@NoSpam_yahoo.com> wrote:

DateAdd("d", 4023, #1/17/1993#)

-Tom.

Hi and TIA! I have a field that I want to convert from Julian to a short date. I've tried
several
procedures, but can't come up with the right solution. What I'm trying to do is convert

4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for
your
time!

[Douglas J. Steele]

I wouldn't worry about figuring out why Tom's worked. It's not a generic
solution: it will not work for dates in 2003 or 2005, for instance.

?DateAdd("d", 3023, #1/17/1993#)
2001-04-28
?DateAdd("d", 5023, #1/17/1993#)
2006-10-19

On the other hand, Allen's works because it adds the number of days to the
last day of the previous year. Right(string, 3) gives you the last 3
characters of a string, while DateSerial(year, 1, 0), it gives you the last
day of the previous year.

?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3), 1,
0))
2003-01-23
?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3), 1,
0))
2005-01-23


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:-r********************@comcast.com...

Allen/Tom, Both of these produce the result I am looking for. Now I'm going to try to figure out how they work. Thanks for your time!!!!

--
Reggie

----------
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Is a Julian date the year concatenated with 3 digits representing the day of the year?
If so, try something like this, where z is the field name:
DateAdd("d", Right([z], 3), DateSerial(Left([z], Len([z])-3), 1, 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:A7********************@comcast.com...

Hi and TIA! I have a field that I want to convert from Julian to a short date. I've tried several procedures, but can't come up with the right solution. What I'm trying to do is convert
4023 to 01/23/04.

If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for your time!

[Tom van Stiphout]

On Fri, 17 Sep 2004 22:09:10 -0700, "Reggie"
<NoSpam_chief123101@NoSpam_yahoo.com> wrote:

Simple:
Dateadd("d", -4023, #01/23/04#)
-Tom.

Tom, Works great. One question though. How/why did you decide to use 1/17/93.

[Tom van Stiphout]

On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

The OP spoke about julian days.
I just pointed out how they work. But I'm with everyone else:
1/17/1993 is a very curious begin date, and for me points out that it
is NOT a julian date. I was hoping the OP would come to that
conclusion himself.

-Tom.

I wouldn't worry about figuring out why Tom's worked. It's not a generic
solution: it will not work for dates in 2003 or 2005, for instance.

?DateAdd("d", 3023, #1/17/1993#)
2001-04-28
?DateAdd("d", 5023, #1/17/1993#)
2006-10-19

On the other hand, Allen's works because it adds the number of days to the
last day of the previous year. Right(string, 3) gives you the last 3
characters of a string, while DateSerial(year, 1, 0), it gives you the last
day of the previous year.

?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3), 1,
0))
2003-01-23
?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3), 1,
0))
2005-01-23

[Douglas J. Steele]

To many people, Julian Date is year and day of year, so that 4023 is the
23rd day of 2004.

And yes, I know that isn't actually a Julian Date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:25********************************@4ax.com...

On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

The OP spoke about julian days.
I just pointed out how they work. But I'm with everyone else:
1/17/1993 is a very curious begin date, and for me points out that it
is NOT a julian date. I was hoping the OP would come to that
conclusion himself.

-Tom.

I wouldn't worry about figuring out why Tom's worked. It's not a generic
solution: it will not work for dates in 2003 or 2005, for instance.

?DateAdd("d", 3023, #1/17/1993#)
2001-04-28
?DateAdd("d", 5023, #1/17/1993#)
2006-10-19

On the other hand, Allen's works because it adds the number of days to thelast day of the previous year. Right(string, 3) gives you the last 3
characters of a string, while DateSerial(year, 1, 0), it gives you the lastday of the previous year.

?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3), 1,0))
2003-01-23
?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3), 1,0))
2005-01-23

[Reggie]

Thanks again for all the help. I know that 4023 isn't a Julian date, but this is government data
coming from a main frame and this is the format I get it in so gotta live with it. Thank ya'll for
your time!

--
Reggie

----------
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:Ul*******************@twister01.bloor.is.net. cable.rogers.com...

To many people, Julian Date is year and day of year, so that 4023 is the
23rd day of 2004.

And yes, I know that isn't actually a Julian Date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:25********************************@4ax.com...

On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

The OP spoke about julian days.
I just pointed out how they work. But I'm with everyone else:
1/17/1993 is a very curious begin date, and for me points out that it
is NOT a julian date. I was hoping the OP would come to that
conclusion himself.

-Tom.

>I wouldn't worry about figuring out why Tom's worked. It's not a generic
>solution: it will not work for dates in 2003 or 2005, for instance.
>
>?DateAdd("d", 3023, #1/17/1993#)
>2001-04-28
>?DateAdd("d", 5023, #1/17/1993#)
>2006-10-19
>
>On the other hand, Allen's works because it adds the number of days to the >last day of the previous year. Right(string, 3) gives you the last 3
>characters of a string, while DateSerial(year, 1, 0), it gives you the last >day of the previous year.
>
>?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3), 1, >0))
>2003-01-23
>?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3), 1, >0))
>2005-01-23