CODEWITHSUNDEEP
excelvba
Oisin Reilly
Oisin Reilly

Reputation: 1

Dates in Excel to convert to a Julian date

So, I'm trying to convert a column of dates from mm/dd/yyyy in excel to Julian format (just a number between 1 and 365/366) and the code I have posted below won't work. I then tried making it as a function only to be met with "Compile Error: Expected:End of statement. Any help would be appreciated.

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

The intention is to make this reproducible over multiple spreadsheets for a long period of time which is why I tried coding it as a function.

edit for clarity:

Function Julian Transform=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000") End Function

Upvotes: 0

Views: 1447

Answers (1)

Bathsheba
Bathsheba

Reputation: 234785

Use

=A2-DATE(YEAR(A2),1,0)

to get the day of the year. This isn't the Julian date by the way. That's something different. See https://en.wikipedia.org/wiki/Julian_day

Upvotes: 2

Related Questions