Export a real date as a cyyddd code

Turn November 4, 2026 into the text 126308: century, year, then day of the year.

What you'll make

In Excel: November 4, 2026 becomes text 126308.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1(YEAR(A8)-1900)Counts the years since 1900.126
2*1000Moves that number in front of three zeros.126000
3+A8-DATE(YEAR(A8),1,0)Adds the day number in the year.126308
4"000000"The pattern: six digits, with zeros in front."000000"
5TEXT( … )Writes the number as six-digit text."126308"

Read it left to right. Dates in the 1900s start with 0, like 098185 for July 4, 1998.

=TEXT((YEAR(A8)-1900)*1000+A8-DATE(YEAR(A8),1,0),"000000")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own dates. Paste real dates with no time into the green column of the Template tab. The codes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only use this code where the system you send to asks for cyyddd. People won't read it as a date.

Microsoft's help: TEXT function