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

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | (YEAR(A8)-1900) | Counts the years since 1900. | 126 |
| 2 | *1000 | Moves 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" |
| 5 | TEXT( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own dates. Paste real dates with no time into the green column of the Template tab. The codes fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

Export a date as a yyyyddd code
November 4, 2026 → 2026308
Get the template →
Create a six-digit mmddyy date field
November 4, 2026 → 110426
Get the template →
Create a six-digit ddmmyy date field
November 4, 2026 → 041126
Get the template →
Show a letter date as November 5, 2026
11/5/2026 → November 5, 2026
Take this route →