Excel-fu needed.

January 26, 2006 2:37 PM

Excel problem. How do I convert the following number format. 106026 where from left - first digit is CENTURY. Next two are YEAR and last three are JULIAN DAY. So last example is CENTURY 1, YEAR 06, and 26th Julian day of the year. I would like to convert to to mm/dd/yy. Anyone have good Excel-fu for this?
posted by thimk to computers & internet (10 comments total)

What are the possible values for Century? Just 1?
posted by blue mustard at 2:56 PM on January 26, 2006

I have a partial answer: A1 is the cell with the number.
=DATE((TRUNC(A1,4)/1000)-100,MONTH(MOD(A1,1000)),DAY(MOD(A1,1000)))

But Excel assumes year 04 is 1904, not 4AD. I don't know how to fix that.
posted by dness2 at 2:57 PM on January 26, 2006

Do you really want to convert from CyyDDD to mm/dd/yy, or did you intend to say mm/dd/yyyy? Your example seems to imply that you will be working with years in the range 1-999, unless there is a convention regarding the century digit that you did explain (ie. 2-9 for 1200-1900, and 0-1 for 2000-2100). Or do expect a millennium digit to appear for years after 999? Similarly, do you need to handle dates before the adoption of the Gregorian calendar (in many countries this occurred in either 1582 or 1752).
posted by RichardP at 3:02 PM on January 26, 2006

Here's a function written in C that will return the nmoth given the julian day:


int monthFromJulianDay( int year, int jd ) {
int leap = isLeapYear( year ) ;
int adjust = jd - leap ;
if( adjust < 60 )
adjust += 365 + leap ;
double dpm = 30.6001 ;
double finagle = 0.053 ;
return ( (int) floor( ( adjust / dpm ) + finagle ) ) % 12 + 1;
}


Note that because Matt won't ficx teh <pre> tag, it's not formatted correctly, and that the cast to int is equivaluent to a round in excel.

Given this function, developing a a function to return the day of the month is trivial.
posted by orthogonality at 3:16 PM on January 26, 2006

=DATE(LEFT(A1,1)*100+1900+MID(A1,2,2),MONTH(RIGHT(A1,3)),DAY(RIGHT(A1,3)))

This assumes that century = "0" if between 1900 and 1999. It also assumes that leading zeroes are included in each data element.
posted by Saucy Intruder at 3:29 PM on January 26, 2006

Excel's DATE function copes with incorrect dates like '34th January' by translating it into the proper month and day-of-the-month. So a simpler function would be something like

=DATE(LEFT(A1,3), 1, RIGHT(A1,3)

Unfortunately, none of these formulas get around the fact that Excel just cannot cope natively with dates earlier than 1st January 1900. It just can't represent them. If you try and feed it the year '106', it'll convert it into '2006'.

So I guess the real question is, as RichardP has pointed out: what does your 'century' number really represent? And do you want this formula to output a true Excel date (in which case, it might not be possible due to Excel's limitations), or would you be happy with a text cell that just reads '01/26/06' (which is definitely possible)?
posted by chrismear at 3:47 PM on January 26, 2006

thimk, we haven't heard back from you, so I'm going to attempt to answer your question, but I'm going to have to make a number of assumptions in my answer.

Assuming...

1. Cell A1 contains a value in your date format.
2. A1 is formatted as an Excel number, not an Excel string.
3. You want the result to be an Excel date, not an Excel string, and you are willing to put up with the resulting limitation that dates before Jan. 1, 1900 or Jan. 1, 1904 will not work (depending on whether Excel is configured to use its 1900 date system or its 1904 date system).
4. A century value of zero indicates 1900-1999.

...I suggest the following function:

=DATE((TRUNC(A1)/1000), 1, MOD(A1,1000))
posted by RichardP at 4:43 PM on January 26, 2006

Argh...I somehow put two extraneous parentheses in that formula. That should have been:

DATE(TRUNC(A1/1000), 1, MOD(A1,1000))
posted by RichardP at 5:09 PM on January 26, 2006

The CENTURY number will always be 1. These are invoices and they have none before 2000. The year can be YYYY instead of YY
posted by thimk at 3:01 PM on January 27, 2006

Regardless your answer works. Thanks for the help.
posted by thimk at 3:05 PM on January 27, 2006