469,950 Members | 1,450 Online

# Help Convert Julian Date to regular date

I am new to access and I am learning as I go along; I am attempting to convert Julian dates to regular dates "MM-DD-YY"; I have tried the code below in the Query screen under criteria.

Expand|Select|Wrap|Line Numbers
1. CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your Field Name]/1000))))+[Your Field Name]-Int([Your Field
2. Name]/1000)*1000))-1,"mm-dd-yy"))
Trouble is it's not bringing up any result; the database field it's been applied to is blank now?

I am not even sure if I am going about it the right way
Aug 13 '09 #1
15 15453
Delerna
1,134 Expert 1GB
There may be a function that will convert it for you but I don't know it.

So
The format that I am familiar with for Julian date is 4 digits the first 3 is the day number of the year and the last is the last digit in the year. Therefore you must know the decade that the date falls within.

So to convert it yourself you need to calculate the year
I will assume the date is in the decade 01/01/2000 - 31/12/2009
which means the decade start year is 2000
I also assume the Julian date is of type string

so the year is
2000 + cint(right(JulianDte,1))
and the date is 01/01/CalculatedYear + (The3DigitJulianDay-1)

The complete statement would be something like

cdate("01/01/" & cstr(2000 + cint(right(JulianDte,1)) )) + cint(left(JulianDte,3)-1)

Where JulianDate is the field name of the Julian Date field
and of course you can add the date format bit in if you want

I hope I got enough brackets there and I hope it helps
Aug 13 '09 #2
OldBirdman
675 512MB
Widipedia informs that Julian Date, as used in Computer Science is Ordinal Date, a date as yyyy-ddd where ddd is the day number for the year, with values from 1 to 365 (366 if leap year).

After verifying that yyyy is a numeric value (probably between 1900 and Now() ) and that ddd is numeric and 1-366, then the date is
Expand|Select|Wrap|Line Numbers
1. dteYourDateVariable = DateAdd("d", Mid(strJulianDate, 6), "12/31/" & Left(strJulianDate, 4) - 1)
Aug 13 '09 #3
Thank you Delerna and OldBirdMan for the prompt reply.

The following are samples of the dates I am attempting to convert

81105
120505
102901
71894

The field name is [TOBIDE]; the Data Type is "Number" and the field size is set to "Decimal" (I have tried to change the data type to Date/Time - Keep getting error messages saying it's run of disk space) everytime I run the query below

CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([TOBIDE]/1000))))+[TOBIDE]-Int([TOBIDE]/1000)*1000))-1,"mm-dd-yy"))

It just falls over does not bring any results in the table; but once I take the query out from the Criteria it returns the julian dates listed above???

I need to get these dates into "MM-DD-YY"
Aug 13 '09 #4
OldBirdman
675 512MB
These are neither true Julian Dates, where today would be 2455057, or Ordinal Dates, where today would be 2009-226. They are not as Delerna thought, days since the start of the decade. Subtracting 120505 from today's date gives a year of 1570, not a probable start date for any counting system.
The best guess is that these numbers are the date & time where the time is the last 3 digits. The remaining digits: 81, 120, 102, 71 would all then be number of days in year, but which year?

Rereading the original post, I am completely confused.
1) Where are these numbers? In a table, I assume because
The field name is [TOBIDE]; the Data Type is "Number" and the field size is set to "Decimal"
that field is a field in a table, and not a control on a form.

2)The criteria stated seems to be copied from an example without any adjustments for this project.
I have tried the code below in the Query screen under criteria.
Name]/1000)*1000))-1,"mm-dd-yy"))
What is [Your Field Name] in this context?

3)A criteria is to test against a field. What is in the field being tested against. WHERE [Value]=7 tests the field "Value" against the number 7, but what is this criteria testing against.

4)
I am attempting to convert Julian dates to regular dates "MM-DD-YY";

5)
Trouble is it's not bringing up any result; the database field it's been applied to is blank now?
What is a database field? What & where is this 'blank'?

This question needs to be clarified before any real help can be given. Without information such as how to manually interpret the numbers given as dates, I don't see an answer. As far as the query goes, more explanation is needed.
Aug 13 '09 #5
I appreciate your help OldBirdman; this is the situation:

These numbers are being displayed in a table called MKF22(table name) under TOBIDE(field name)

81105
120505
102901
71894

I am trying to extract the date and time from the numbers above and to display them as "MM/DD/YY" in the TOBIDE(field name) under the (MKF22) table instead of the current form that is being displayed.

Eg:

Currently 81105 is what is being displayed would like it to show 08/11/2005 (if that is the correct date format)? I hope I am making sense????????? As I said I am very new to access.

Thank you!!!!!!!!!!!
Aug 13 '09 #6
Delerna
1,134 Expert 1GB
So those are simply the date with the /'s removed in
either "mmddyy" or "mddyy" format

81105=08/11/05
120505=12/05/05
101201=10/29/01
71894=07/18/94

If that is the case then something like this might work
Expand|Select|Wrap|Line Numbers
1. cdate(
2.    iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(TOBIDE),2)
3.    & "/" &
4.    left(right(cstr(TOBIDE),4),2)
5.    & "/" &
6.    right(cstr(TOBIDE),2)
7. )
8.
Aug 13 '09 #7
Delerna
1,134 Expert 1GB
Incidentally, I thought I would try plugging one of the values into your formula
and calculate it out by hand to try and see why you were getting the error.

I didn't discover the reason for the error but the result is interesting
Expand|Select|Wrap|Line Numbers
1. CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([TOBIDE]/1000))))+[TOBIDE]-Int([TOBIDE]/1000)*1000))-1,"mm-dd-yy"))
2. CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int( 120505/ 1000))))+ 120505 -Int( 120505 /1000)*1000))-1,"mm-dd-yy"))
3. CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+        120       )))+ 120505 -        120       *1000))-1,"mm-dd-yy"))
4. CDate(Format(((DateValue("01/01/"+LTrim(Str(        2020           )))+ 120505 -           120000      ))-1,"mm-dd-yy"))
5. CDate(Format(((DateValue("01/01/"+          "2020"                   )+            505                 ))-1,"mm-dd-yy"))
6. CDate(Format(((DateValue("01/01/2020"                                )+            505                 ))-1,"mm-dd-yy"))
7. CDate(Format(( 05/20/2021)-1                                                                               ,"mm-dd-yy"))
8. CDate(Format(05/19/2021,"mm-dd-yy")
9.
is nothing like the result 12/05/05
you are after.

When I have difficulty with large unwieldly formulas I sometimes do this to help me spot why its not working.

More usually I tend to build the formula up one piece at a time as separate fields before combining them into the final formula. That way I can see which part of the formula is going wrong as I write it.

Going from memory here, most of my queries are written on SQL server these days,
but I don't think you can do this
"01/01/" + "2020" in access can you?
Dosn't it have to be "01/01/" & "2020" ?
Aug 14 '09 #8
Delerna you are genius; expert doesn't even begin to describe your abilities.

Works like a charm; the thing is I could never figure out why I kept getting 05/19/2021

I got those same results even with simple formating like

Expr1: Format:([TOBIDE], "mm/dd/yy")

Even though the date should already be in "mmddyy" format.

Thank you and OldBirdman!!!!!!!!!!! YOU BOTH ROCKKKKKKKKKKKKKKKKKK
Aug 14 '09 #9
Delerna
1,134 Expert 1GB
I can't get my head out the door now :)

Seriously though, every expert (whether they have that word against their name or not) in every topic here ....... You ROCKKKKKKKKK.
Aug 16 '09 #10
OldBirdman
675 512MB
To simplify the issue, these numbers can be converted to Access dates by
Expand|Select|Wrap|Line Numbers
1. CDate(Format(120505,"&&/&&/&&"))
Aug 16 '09 #11
Delerna
1,134 Expert 1GB
That's much better than my clunky method
Aug 16 '09 #12
Quick question? I attempting to include an 'OR' condition to handle fields that are blank or have a '0' value.

I have tried both of the following statements and non of them seem to do it?

Eg 1 :
OR IIf(([TOBIDE])= "", [TOBIDE])

Eg 2:

OR IIf(Len([TOBIDE])= 1, [TOBIDE])

the entire statement in my query goes like this

cdate(
iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(T OBIDE),2)
& "/" &
left(right(cstr(TOBIDE),4),2)
& "/" &
right(cstr(TOBIDE),2) OR IIf([TOBIDE]) = "",([TOBIDE])
)

The entire OR section is being ignored when I run the query...........
Aug 17 '09 #13
I have figured out a way to get the blank field to display "0" as against the following data 12:00:00 AM or 12/29/1899 which it was giving me earlier by using the following query(tips from Delerna)

IIf(Len([TOBIDE])=1,0)

but the problem right now is combining it with the previous IIf statement; I keep getting #Error or it totally ignores the query altogether???? Below is the query

cdate(
iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(T OBIDE),2)
& "/" &
left(right(cstr(TOBIDE),4),2)
& "/" &
right(cstr(TOBIDE),2), IIf(Len([TOBIDE])=1,0))

I have tried using OR in there to handle the second IIf function and it does not work????????????? Any Suggestions????????????
Aug 17 '09 #14
Delerna
1,134 Expert 1GB
Blank fields can be handled with NZ.
In a query it is used something like this
Expand|Select|Wrap|Line Numbers
1. SELECT NZ([TheField],TheValueToReturnIf[TheField]IsNull) as FieldName
2.

With the method I gave you, you would wrap my if in the false part of another if. (Its called a Nested IIF)

so
IIF(len(NZ(TOBIDE,0))=1,null,MyClunkyIIF)

You finish up with an iff within an iif like this
Expand|Select|Wrap|Line Numbers
1. iif(len(NZ(TOBIDE,0))=1,null,
2.    cdate
3.    (   iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(TOBIDE),2)
4.        & "/" &
5.        left(right(cstr(TOBIDE),4),2)
6.        & "/" &
7.        right(cstr(TOBIDE),2)
8.    )
9. )
10.

HOWEVER
Using OldBirdman's much neater suggestion you could do something like this

Expand|Select|Wrap|Line Numbers
1.
2. IIF(len(NZ(TOBIDE,0))=1,null,CDate(Format(TOBIDE,"&&/&&/&&")))
3.
4.
Aug 17 '09 #15
YOU Guys did it ones again; thank you so much. The more I discover the more interesting Access gets!!!!!!!!!!!!!!!! Awesome
Aug 18 '09 #16