Calculating a Julian Date

[tkosel]

I have a need to Calculate a Julian Date which is for my requirements the
last two digits of the year and three digits indicating the day of the year.
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc. Dec 31, 2009 would be 09365. Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job. It appears below.

=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")

If you place a date in Cell A2, it works fine. I am not smart enough to
convert this to an Access control source formula. Anybody help?

 

[Jerry Whittle]

Format([TheDateField], "yyy")

 

[tkosel]

Jerry,

Thanks a lot, works like a charm. I was going to make it a lot harder than
you did!!! Again, thanks.

Format([TheDateField], "yyy")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a need to Calculate a Julian Date which is for my requirements the
last two digits of the year and three digits indicating the day of the year.
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc. Dec 31, 2009 would be 09365. Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job. It appears below.

=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")

If you place a date in Cell A2, it works fine. I am not smart enough to
convert this to an Access control source formula. Anybody help?

 

[Keven Denen]

I have a need to Calculate a Julian Date which is for my requirements  the
last two digits of the year and three digits indicating the day of the year.  
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc.  Dec 31, 2009 would be 09365.  Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job.  It appears below.

=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")

If you place a date in Cell A2, it works fine.  I am not smart enough to
convert this to an Access control source formula.  Anybody help?

Something along these lines should do it, where bdate is whatever
field you want to reproduce in your format.

Format([bdate],"yy") & Format(Format([bdate],"y"),"000")

Keven Denen

 

[Keven Denen]

Jerry,

Thanks a lot, works like a charm.  I was going to make it a lot harder than
you did!!!  Again, thanks.

Format([TheDateField], "yyy")

"tkosel" wrote:

Only issue with Jerry's solution is it doesn't put the day part into 3
number is it isn't 100+. January 1, 2009 will be 091

Keven Denen

 

[tkosel]

Keven,

You are right, Jerry's solution doesn't do it right. I tried yours and it
doesn't work right either. I think I will go back to my original approach.
When I work it out, I will post it here.

I have a need to Calculate a Julian Date which is for my requirements the
last two digits of the year and three digits indicating the day of the year.
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc. Dec 31, 2009 would be 09365. Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job. It appears below.

=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")

If you place a date in Cell A2, it works fine. I am not smart enough to
convert this to an Access control source formula. Anybody help?

Something along these lines should do it, where bdate is whatever
field you want to reproduce in your format.

Format([bdate],"yy") & Format(Format([bdate],"y"),"000")

Keven Denen

 

[Jerry Whittle]

Excellent point. Your solution is better.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

Thanks a lot, works like a charm. I was going to make it a lot harder than
you did!!! Again, thanks.

Format([TheDateField], "yyy")

"tkosel" wrote:

I have a need to Calculate a Julian Date which is for my requirements the
last two digits of the year and three digits indicating the day of the year.
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc. Dec 31, 2009 would be 09365. Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job. It appears below.

If you place a date in Cell A2, it works fine. I am not smart enough to
convert this to an Access control source formula. Anybody help?

Only issue with Jerry's solution is it doesn't put the day part into 3
number is it isn't 100+. January 1, 2009 will be 091

Keven Denen

 

[tkosel]

Keven,

I was wrong, your approach works fine. I was using it wrong. Sorry about
that and thanks. I did come up with some code of my own that works by
calculating the date difference in days from the first of the year, but yours
is simpler. Again, thanks to all.

Keven,

You are right, Jerry's solution doesn't do it right. I tried yours and it
doesn't work right either. I think I will go back to my original approach.
When I work it out, I will post it here.

I have a need to Calculate a Julian Date which is for my requirements the
last two digits of the year and three digits indicating the day of the year.
(YYDDD)

In this scenario, Jan 1, 2009 would be julian dated as 09001, Jan 2, 2009
would be 09002, etc. Dec 31, 2009 would be 09365. Leap years are another
story, but all day numbers are incremented by one after Febuary 29 which has
a 060 designation.

I have found an Excel formula that does the job. It appears below.

=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")

If you place a date in Cell A2, it works fine. I am not smart enough to
convert this to an Access control source formula. Anybody help?

Something along these lines should do it, where bdate is whatever
field you want to reproduce in your format.

Format([bdate],"yy") & Format(Format([bdate],"y"),"000")

Keven Denen