Julian Date

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

----------

re: Julian Date

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

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

-Tom.

[color=blue]
>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![/color]

re: Julian Date

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:A7Kdna2IlMCSDtbcRVn-tA@comcast.com...[color=blue]
> 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![/color]

re: Julian Date

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" <AllenBrowne@SeeSig.Invalid> wrote in message
news:414bb1fe$0$24385$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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:A7Kdna2IlMCSDtbcRVn-tA@comcast.com...[color=green]
>> 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![/color]
>
>[/color]

re: Julian Date

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

--
Reggie

----------
"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
news:8fank0puf5vb7vgjj83n8t2qc39hvrf0gk@4ax.com...[color=blue]
> On Fri, 17 Sep 2004 18:46:22 -0700, "Reggie"
> <NoSpam_chief123101@NoSpam_yahoo.com> wrote:
>
> DateAdd("d", 4023, #1/17/1993#)
>
> -Tom.
>
>[color=green]
>>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![/color]
>[/color]

re: Julian Date

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:-rqdndw8c49NJtbcRVn-rA@comcast.com...[color=blue]
> Allen/Tom, Both of these produce the result I am looking for. Now I'm[/color]
going to try to figure out[color=blue]
> how they work. Thanks for your time!!!!
>
> --
> Reggie
>
> ----------
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:414bb1fe$0$24385$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
> > Is a Julian date the year concatenated with 3 digits representing the[/color][/color]
day of the year?[color=blue][color=green]
> >
> > 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:A7Kdna2IlMCSDtbcRVn-tA@comcast.com...[color=darkred]
> >> Hi and TIA! I have a field that I want to convert from Julian to a[/color][/color][/color]
short date. I've tried[color=blue][color=green][color=darkred]
> >> several procedures, but can't come up with the right solution. What[/color][/color][/color]
I'm trying to do is convert[color=blue][color=green][color=darkred]
> >>
> >> 4023 to 01/23/04.
> >>
> >> If you know of a solution or somewhere I can find the answer I would[/color][/color][/color]
appreciate it. Thanks for[color=blue][color=green][color=darkred]
> >> your time![/color]
> >
> >[/color]
>
>[/color]

re: Julian Date

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

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

[color=blue]
>Tom, Works great. One question though. How/why did you decide to use 1/17/93.[/color]

re: Julian Date

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.
[color=blue]
>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[/color]

re: Julian Date

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.spam.tom7744@cox.net> wrote in message
news:25jok01dmjnmec10dialoj7sdslq744o5f@4ax.com...[color=blue]
> 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.
>[color=green]
> >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[/color][/color]
the[color=blue][color=green]
> >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[/color][/color]
last[color=blue][color=green]
> >day of the previous year.
> >
> >?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3),[/color][/color]
1,[color=blue][color=green]
> >0))
> >2003-01-23
> >?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3),[/color][/color]
1,[color=blue][color=green]
> >0))
> >2005-01-23[/color]
>[/color]

re: Julian Date

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_2d.58055$Q7D.45202@twister01.bloor.is.net. cable.rogers.com...[color=blue]
> 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.spam.tom7744@cox.net> wrote in message
> news:25jok01dmjnmec10dialoj7sdslq744o5f@4ax.com...[color=green]
>> 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.
>>[color=darkred]
>> >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[/color][/color]
> the[color=green][color=darkred]
>> >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[/color][/color]
> last[color=green][color=darkred]
>> >day of the previous year.
>> >
>> >?DateAdd("d", Right("3023", 3), DateSerial(Left("3023", Len("3023")-3),[/color][/color]
> 1,[color=green][color=darkred]
>> >0))
>> >2003-01-23
>> >?DateAdd("d", Right("5023", 3), DateSerial(Left("5023", Len("5023")-3),[/color][/color]
> 1,[color=green][color=darkred]
>> >0))
>> >2005-01-23[/color]
>>[/color]
>
>[/color]