Results 1 to 7 of 7
  1. #1
    Cutthroat is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    10

    how to convert standard date to julian day (i.e., nth day of year)


    So, I'm posting this in Macros to hopefully avoid answers full of VBA code which might as well be a foreign language to me.

    Anyway, as the title states, I want to convert a date value to an integer value representing Julian day, or the nth day of the year, in a separate field in the same table. I'm thinking I would do this as a calculated field, but what expression would I use? For example, if the date = 2/1/2016 I want it to return Julian day = 32. I've tried to mimic examples that use the Date() and DateSerial() functions w/out success as I cant seem to get the syntax correct.

    Any help is very much appreciated, I'm hoping there's a simple expression to do this w/out relying on other code. Thank you in advance.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't know about doing this in a macro - I NEVER use macros.

    There are several functions to convert a Gregorian date to a Julian date; the simplest one I found is
    Code:
    CLng(Format(Date, "y"))
    Naturally, you could enter a date explicitly or use a date variable.
    Code:
    CLng(Format(#2/1/2016#, "y"))
    Code:
    CLng(Format(MyDateVar, "y"))

  3. #3
    Cutthroat is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    10
    Thank you for your response, I am just now getting back to this problem. Anyway, I managed to get this to work in a query using the code you provided, but I had to create a new field and use the code as an expression (i.e., JulDay: CLng(Format([SurveyDate],"y"))) which is a great start. However, I could not get this to work as an expression in the table as part of a calculated field. I'd like to store that converted value separately in my table and thought it would be straight-forward, but like I said it didn't work (it didn't seem to recognize the CLng function). Do you have any suggestions how to convert my SurveyDate and store it in a separate table field? This is important b/c I have over 20 yrs of data I will be migrating into the db and it would be helpful for various reasons.

    Also, the output is an integer where 1/1/YYYY = 1, 2/1/YYYY = 32, and 5/1/YYYY = 122. Do you know how I can get it to generate a 3-digit variable regardless of date (i.e., 001, 032 and 122, respectively)? The reason being is that we collect 100's of field samples and use Julian Day in our sample code to create unique codes for each sample, but it has to be a 3-digit number (ok if stored as text) as I will have the db use that value to generate our unique sample codes based on this and other entered/calculated data (i.e., agency code, year, etc).

    Thanks again for your help!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    expression in the table as part of a calculated field.
    Sorry, no. I NEVER use calculated fields in tables. Plus, the functions in a calculated field are limited (AFAIK).


    Do you know how I can get it to generate a 3-digit variable regardless of date
    Try
    Code:
    Format(CLng(Format(Date, "y")),"000")
    Example:
    Code:
    Format(CLng(Format(#1/2/2016#, "y")),"000")
    results in "002". It would have to be stored in a text field.


    Or you could store the Julian date in a Integer field (lets call it "JulianDay") and use
    Code:
    Format(JulianDay,"000")
    to convert it to 3 text characters ("002"). It is much better to have 1 field and use the Format function or conversion functions to change the value to another type rather that store it in multiple fields as different types.

    For that matter, you could store the calendar date and use
    Code:
    Format(CLng(Format(MyDateVar, "y")),"000")
    to convert the date to a3 character Julian day on the fly.

  5. #5
    Cutthroat is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    10
    Excellent, thx Steve. As a follow up, I did figure out how to get this to work in a calculated field and thought I would share for future reference. As I mentioned, I used your suggestion and got it to work in a query, but I'm really interested in storing the Julian Day value in a table; so, here's the expression I used:

    Code:
    [SurveyDate]-DateSerial(Year([SurveyDate]),1,1)+1
    and when I set the Format = 000 I get exactly what I need in return (e.g., 1/28/2016 = 028). Anyway, thanks again for your help as you set me on the right path.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are very welcome...

  7. #7
    furlong1518 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jul 2017
    Posts
    1
    That works to display the day count, how would you have the last two of the year as a prefix? IE. 17201 (07/20/2017)

    Also is there a variation for leap years?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Current Date to Julian Date?
    By Hammilton in forum Forms
    Replies: 12
    Last Post: 12-11-2015, 09:27 PM
  2. Replies: 7
    Last Post: 06-07-2015, 11:57 PM
  3. Replies: 4
    Last Post: 01-11-2014, 11:57 PM
  4. Julian (ordinal) date one day off
    By gregu710 in forum Access
    Replies: 6
    Last Post: 02-02-2012, 06:21 PM
  5. Julian Date Function Help Please
    By campanellisj in forum Programming
    Replies: 3
    Last Post: 05-13-2011, 12:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums