On the left of the ampersand, we generate the year value.
We do this by subtracting the last day of the previous year from the date we are working with.
Because dates are just serial numbers, this will give us the “nth” day of year.
To get the last day of year of the previous year, we use the DATE function.
Now we need to pad the day value with zeros.
For example, this would create a date from a yyyyddd Julian date like 1999143.
If you just have a day number (e.g.
100, 153, etc.
This works because the DATE function knows how to adjust for values that are out of range.
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number.
TEXT Function
The Excel TEXT function returns a number formatted as text.
in a text string with the number format of your choice.
