Converting julian date (modified) to regular date

[kittykats]

Hi - I have a field called CustomerLot which is made up 5 numbers which consist of the first 3 numbers being the day of the year, starting at 001 and the last 2 numbers representing the year (example 08, so 00108 would be January 1, 2008 and 36508 would be December 31, 2008. I need to convert this number back to a regular date. I have looked at the examples given by Microsoft, but they are all geared towards having the yy first then the ddd. I have accomplished it by parsing out the field, first by the year, then by the day and then joining them back (basically, I am reversing the order) and then using a function to create the date, but if anyone has a one-step solution, it would be appreciated.

 

[Remou]

How about:

NoIn = "01008"
Result = DateSerial(Right(NoIn, 2), 1, 0) + Left(NoIn, 3)

 

[MajP]

Remou,
Nice solution.

Kittykats,
You may want to wrap what Remou presented into a function, then you can use it anywhere (query, form, report, or other code).

Public Function julianToDate(strJulian As String) As Date
julianToDate = DateSerial(Right(strJulian, 2), 1, 0) + Left(strJulian, 3)
End Function

 

[HarleyQuinn]

Just a note, '36508' would return 30/12/2008 as it's a leap year, just something to watch when some results don't return as you may have originally expected.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Golom]

In fact

NoIn = "01008"
Result = DateSerial(Right(NoIn, 2), 1, Left(NoIn, 3))

also works because of the way DateSerial interprets its arguments.

 

[kittykats]

Thank you very much for your suggestions. I will try it out later tonight and let you know. Anything that cuts down the steps to create the end result is most appreciated.

 

[kittykats]

Works like a charm, leap year is not a problem due to DateSerial, it accounts for it.

Many thanks

 

[HarleyQuinn]

Not to be pedantic, but how does DateSerial account for the leap years the way the code is being used (as per my example in my first post)?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Golom]

HarleyQuinn

It doesn't ... nor does any other method of extracting a date from a day-in-the-year value (at least that I can think of.)

It reports ... as you suggest ... that "36508" is December 30 and not December 31. I suppose that you could just add 1 to the computed date if it is after Feb 28 in a leap year but that only moves the problem somewhere else. You can't then refer to Feb 29 in a leap year because adding 1 will skip over it.

Just need to face the fact that different years can have different numbers of days and write code to compensate for that.

 

[HarleyQuinn]

Golom

Couldn't agree more, and that was the point I was trying to address with kittykats because:

leap year is not a problem due to DateSerial, it accounts for it.

lead me to believe that point you and I have just made wasn't entirely clear.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.