0

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

Mo Correll
  • 57
  • 1
  • 8
  • 1
    People seem to define the term "Julian Date" in many different ways - how are you defining it - can you give an example? If you have the value 970623 - that is supposed to be 23rd June 1997 I presume - what do you want to convert to in that case? How is a date like 1st Jan 2000 shown in your data? – barry houdini Jan 19 '15 at 14:46

2 Answers2

0

First, you have to decide at which point you decide if it's 20xx or 19xx for the year, and using that, create the value

=DATEVALUE(TEXT(VALUE(IF(VALUE(LEFT(A2,2))<50,"20","19")&A2),"0000-00-00"))

or if they are all 19xx dates,

=DATEVALUE(TEXT(VALUE("19"&A2),"0000-00-00"))

This formula will add a 19 or 20 to the beginning, depending on the year, then convert that to a number, then convert that to a formatted number that excel can understand is a date (yyyy-mm-dd)
Format the result as you would any other date.

from CPearson's very useful website,

The formula below will convert a standard Excel date in A1 to a Julian Date. 

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
Community
  • 1
  • 1
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Thank you! However this formula actually changed my dates into calendar dates, not julian dates. I am using 1900 as the base date, not 2000. – Mo Correll Jan 19 '15 at 15:32
  • Sorry, my comment was incorrect and I am over the 5 minute edit time - this formula changed my example date to 35604? – Mo Correll Jan 19 '15 at 15:39
  • As per my comment on your question - for the example 970623, what result do you want? – barry houdini Jan 19 '15 at 15:42
  • @MoCorrell, see my edit to convert the regular excel date (yes, 35604 is an excel date, 6/23/1997) – SeanC Jan 19 '15 at 16:24
0

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.

Daniel
  • 1,695
  • 15
  • 33