Convert day of year date/time format to number (1 Viewer)

[murre08]

Hi all,

I have an inherited database with years worth of julian dates stored as numbers (e.g. days 1-366 for a leap year). I also have some fields stored as short dates. I would like to run a series of parameterized queries on this data, some using the short data and some using the "julian dates" that are actually just numbers. I have the user enter the start and end date in short date format and would like that to be converted and held in an unbound field as a number value. So far I can get the date to appear as a "julian date" (e.g. 1/1/08 appears as 1, obviously the underlying data is still 1/1/08) How do I take that 1 and convert it to number value "1"? Thanks in advance.

Cheers,
Peter

 

[ajetrumpet]

When the date is entered by the user, are you just wanting to see the value "1" as a number data type in the box, or do you want the underlying data to change forever? As in, change to single digits in the source table.

 

[gemma-the-husky]

yuo should be able to just add the number to the date

examples

ie mydate = datevalue("1/1/2007")+offsetvalue-1

(-1 because presumably 1/1/07 is day1 not day0)

if you use a different date, watch for US/UK date issues (depends where you are)

--------
the other way round you get just

dayinyear = mydate - datevalue("1/1/2007") +1

(+1 again corrects for day1 in year)

 

[murre08]

Hi Adam,

The data doesn't get entered into any table, it is just a data holder field on a form to run some parameterized queries after the click of a command button. But, yes, I just want the date 1/1/YYYY to become the integer "1"; 1/2/YYYY the integer "2" etc. This way, I can run queries on another table where the "julian date" has been entered as integers in number format.

Cheers,
Peter

 

[mresann]

Part of the confusion is that Access uses conflicting date formatting information, especially with regard to the Year and Julian date designation.

For all examples, datDate= #March 1 2008#

The first example will return the 4 digit year:

Format(datDate, "yyyy") = 2008

The next example will return the 2 digit year:

Format(datDate, "yy") = 08

Now, the next example will return the Day of Year (not true "Julian Date"):

Format(datDate, "y") = 61 (March 1, 2008 is the 61st day of 2008)

Now, we need to create a double format condition to extract certain digits:

To extract the last digit for the current Year (2008 = 8):

Format(Format(datDate, "yy"), "0") = 8

The last single example provides you with a 3-digit formatted Day of Year:

Format(Format(datDate, "y"), "000") = 061

Now, if you want to convert the date to a "Julian Date" format, which in many businesses is formatted as the Single digit of the year (2008 = 8), combined with the three-digit format of the Julian Date (61 = 061), to equal "8061", then you have to combine two format statements together:

Format(Format(datDate, "yy"), "0") & Format(Format(datDate, "y"), "000")

This will return "8061". This is, by the way, a Text format.

If you want to convert the OTHER way, the best way is to use the Date Add combined with the Date Serial function. Note the unique DateSerial setup that uses the day of "0" to ensure proper calculation:

Variables, string format (or Long or Integer format): strYear = 2008 (or 08); strJDay = 61

datDate = DateAdd ("d", strJDay, DateSerial (strYear, 1, 0))

...returns March 1 2008 in date format.

Hope this helps

 

[ajetrumpet]

Hi Adam,

The data doesn't get entered into any table, it is just a data holder field on a form to run some parameterized queries after the click of a command button. But, yes, I just want the date 1/1/YYYY to become the integer "1"; 1/2/YYYY the integer "2" etc. This way, I can run queries on another table where the "julian date" has been entered as integers in number format.

Cheers,
Peter

I read over your initial post again, and I'm not sure how you want to change this data. But, why can't you just use the Month() function to convert the short date format of the input data to the "Julian" number you are referring to??

I am not familiar with Julian dates, but whatever number it gives you (be it a day of the month, or the month of the year), the Day() and Month() function should work in terms of conversion, shouldn't it??

 

[murre08]

Hi all,

I kept working on it and found this to work =CInt(Format([text0],"y")) . I think my subsequent queries didn't like the fact that when I chose to format properties for that object as Date/Time "y" it was still a Date/Time format. The query I subsequently run using this value as a parameter is running off of a table that the field is in Number format. So I needed to convert the "y" value from Date/Time to Number. I'm assuming all of the Day(), Month(), datDate functions all return values that are formated as Date/Time and would have issues if working with integers. Or am I off base here?

 

[ajetrumpet]

I'm assuming all of the Day(), Month(), datDate functions all return values that are formated as Date/Time and would have issues if working with integers. Or am I off base here?

Murre,

Glad you got it to work, whatever you did.

And those functions you are talking about involve output data that is an integer everytime. So, it's a bit backwards. Month() gives you the month number of the year (1-12) in your date. Day() gives you the day number (1-30/31) of the month in your date.