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.
| Format | Formula | 14 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.
| Input | Formula |
|---|---|
| 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.