Convert Julian Dates in Excel, Google Sheets & SQL

Formulas you can copy into a spreadsheet or query to turn dates into Julian dates and back. All examples assume the date or code is in cell A2.

Excel: convert a date to a Julian date

The trick is A2-DATE(YEAR(A2),1,0): DATE(year,1,0) is 31 December of the previous year, so subtracting it gives the day of the year.

FormatFormula14 Feb 2026
YYDDD=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")26045
YYYYDDD=YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")2026045
YDDD=RIGHT(YEAR(A2),1)&TEXT(A2-DATE(YEAR(A2),1,0),"000")6045
Day of year=A2-DATE(YEAR(A2),1,0)45

These return text, which keeps leading zeros (for example 05032 for 2005). Wrap the formula in VALUE() if you need a number. For the day-of-year formula, set the cell format to General or Number, otherwise Excel may display it as a date.

Excel: convert a Julian date to a date

DATE() accepts a day number larger than the month length and rolls it forward, so DATE(year,1,day_of_year) gives the right date, including in leap years.

InputFormula
YYDDD (2000s)=DATE(2000+INT(A2/1000),1,MOD(A2,1000))
YYDDD (1900s and 2000s)=DATE(IF(INT(A2/1000)<50,2000,1900)+INT(A2/1000),1,MOD(A2,1000))
YYYYDDD=DATE(INT(A2/1000),1,MOD(A2,1000))
YDDD (this decade)=DATE(YEAR(TODAY())-MOD(YEAR(TODAY()),10)+INT(A2/1000),1,MOD(A2,1000))

Format the result cell as a date. If your codes are stored as text, the formulas still work because Excel converts them to numbers automatically. The YDDD formula assumes the current decade, so check codes from late in the previous decade.

JD Edwards Julian dates (CYYDDD)

JD Edwards EnterpriseOne and World, and some other ERP and AS/400 systems, store dates as CYYDDD: centuries since 1900, two-digit year, then day of year. So 126045 is 14 February 2026.

CYYDDD → date:   =DATE(1900+INT(A2/1000),1,MOD(A2,1000))
date → CYYDDD:   =(YEAR(A2)-1900)*1000+(A2-DATE(YEAR(A2),1,0))

Excel: astronomical Julian Date

Excel stores dates as days since 30 December 1899 (in the default 1900 date system). That day's midnight is JD 2415018.5, so:

Date/time → JD:   =A2+2415018.5
JD → date/time:   =A2-2415018.5
Date/time → MJD:  =A2-DATE(1858,11,17)   (or =A2+15018)

Excel treats the times as having no time zone, so enter them in UTC for a correct Julian Date. These formulas work for dates from 1 March 1900 onwards. See the Julian Day calculator to check a result.

SQL

PostgreSQL

SELECT to_char(DATE '2026-02-14', 'YYDDD');     -- 26045
SELECT to_char(DATE '2026-02-14', 'YYYYDDD');   -- 2026045
SELECT to_date('2026045', 'YYYYDDD');           -- 2026-02-14
SELECT to_char(DATE '2026-02-14', 'J');         -- Julian Day Number

MySQL / MariaDB

SELECT DATE_FORMAT('2026-02-14', '%y%j');        -- 26045
SELECT MAKEDATE(2000 + 26045 DIV 1000, 26045 % 1000);  -- 2026-02-14

SQL Server

DECLARE @d date = '2026-02-14', @code int = 26045;
SELECT CONCAT(RIGHT(YEAR(@d), 2), FORMAT(DATEPART(dayofyear, @d), '000'));  -- 26045
SELECT DATEADD(day, @code % 1000 - 1, DATEFROMPARTS(2000 + @code / 1000, 1, 1));  -- 2026-02-14

Oracle

SELECT TO_CHAR(DATE '2026-02-14', 'YYDDD') FROM dual;   -- 26045
SELECT TO_DATE('2026045', 'YYYYDDD') FROM dual;         -- 2026-02-14
SELECT TO_CHAR(DATE '2026-02-14', 'J') FROM dual;       -- Julian Day Number

Python

from datetime import date, datetime

d = date(2026, 2, 14)
d.strftime("%y%j")                     # '26045'
d.strftime("%Y%j")                     # '2026045'
datetime.strptime("26045", "%y%j").date()  # date(2026, 2, 14)

# Astronomical Julian Date at midnight
d.toordinal() + 1721424.5              # 2461085.5

JavaScript

function dayOfYear(d) {
  return (Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()) - Date.UTC(d.getFullYear(), 0, 0)) / 864e5;
}
function toYYDDD(d) {
  return String(d.getFullYear() % 100).padStart(2, '0') + String(dayOfYear(d)).padStart(3, '0');
}
function fromYYYYDDD(code) {
  return new Date(Math.floor(code / 1000), 0, code % 1000);
}
// Astronomical Julian Date for "now"
const jd = Date.now() / 864e5 + 2440587.5;

Frequently asked questions

How do I convert a date to a Julian date in Excel?

With the date in A2, use =TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000") for a five-digit YYDDD Julian date, or =YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000") for YYYYDDD.

How do I convert a Julian date to a normal date in Excel?

For a YYDDD number in A2, use =DATE(2000+INT(A2/1000),1,MOD(A2,1000)) and format the cell as a date. For YYYYDDD, use =DATE(INT(A2/1000),1,MOD(A2,1000)).

How do I convert a JD Edwards Julian date in Excel?

JD Edwards stores dates as CYYDDD, where C is the number of centuries after 1900. Use =DATE(1900+INT(A2/1000),1,MOD(A2,1000)). For example, 126045 becomes 14 February 2026.

Do these formulas work in Google Sheets?

Yes. Google Sheets uses the same date serial numbers and functions as Excel, so all the formulas on this page work unchanged.

Related