- Jul 17, 2010View Source
I think Paul's question was how to get Excel to display
dates in the format YYYY-DDD, e.g. 2010-198 for today's date.
I think this formula will give you what you want:
Where E1 is the cell containing the date in Excel's serial number date format. To get it to display with a dash between the elements, create a custom number format like this: 0000-000.
On 7/17/2010 5:10 PM, Tex Texin wrote:
If you go into number formats and choose custom, you can enter yyyy-mm-dd or yyyymmdd.
If you need a macro to accomplish that, turn on macro recording and format a cell as above. Then save it. that should get you going.
You can probably also set that in a function, but I haven’t tried and haven’t time right now.
If you can’t get it to work as you like, come back and clarify what you need and I’ll give it a go later.
Has anyone come across an Excel macro (ideally compatible with all versions from Excel 2000 onwards) for formatting cells for the ISO8601 "Day in Year" format (ie "YYYYDDD" and "YYYY-DDD")? The year ("YYYY") component can be obtained using the Excel function "YEAR(cell)" but I can't find an existing Excel builtin function for the "day-in-year" ("DDD") component.