Search code examples
exceldateexcel-2011

Text calendar date to Julian date in excel

I have a column of yymmdd dates formatted as text (ex: 970623) I'm trying to convert into Julian date in Excel 2011. I've tried converting to excel-formatted date, but it throws me back into the 1950s with all of my dates. I also found a suggestion online about using error check in excel. Any ideas for formulas to tease this column into Julian date?

Thanks very much,

Mo

Solution

  • Your best option is to use the DATE function as follows:

    B1=DATE(LEFT(A1,2), MID(A1,3,2),RIGHT(A1,2))

    This will turn your text into an Excel date.

    Then, assume you're looking for a 2-digit year and 3-digit Day-Of-Year, just calculate the difference between that day and the 1st day of the year. So:

    C1=RIGHT(YEAR(B1),2)&B1-DATE(YEAR(B1),1,1)

    That should give you the Julian date.