By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 424,961 Members | 1,320 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,961 IT Pros & Developers. It's quick & easy.

# Julian Date

 P: n/a 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 ---------- Nov 13 '05 #1
Share this Question
9 Replies

 P: n/a On Fri, 17 Sep 2004 18:46:22 -0700, "Reggie" 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 severalprocedures, but can't come up with the right solution. What I'm trying to do is convert4023 to 01/23/04.If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks for yourtime! Nov 13 '05 #2

 P: n/a 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" 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! Nov 13 '05 #3

 P: n/a 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" 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" 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! Nov 13 '05 #4

 P: n/a Tom, Works great. One question though. How/why did you decide to use 1/17/93. -- Reggie ---------- "Tom van Stiphout" wrote in message news:8f********************************@4ax.com... On Fri, 17 Sep 2004 18:46:22 -0700, "Reggie" 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 triedseveralprocedures, but can't come up with the right solution. What I'm trying to do is convert4023 to 01/23/04.If you know of a solution or somewhere I can find the answer I would appreciate it. Thanks foryourtime! Nov 13 '05 #5

 P: n/a 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" 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" 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" 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! Nov 13 '05 #6

 P: n/a On Fri, 17 Sep 2004 22:09:10 -0700, "Reggie" 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. Nov 13 '05 #7

 P: n/a On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele" 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 genericsolution: 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-19On 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 3characters 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 Nov 13 '05 #8

 P: n/a 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" wrote in message news:25********************************@4ax.com... On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele" 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 genericsolution: 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-19On 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 3characters 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 Nov 13 '05 #9

 P: n/a 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" 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" wrote in message news:25********************************@4ax.com... On Sat, 18 Sep 2004 11:48:44 GMT, "Douglas J. Steele" 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 Nov 13 '05 #10

### This discussion thread is closed

Replies have been disabled for this discussion.