2206RE: [ISO8601] ISO8601 Std for Day in Year?
- Jul 18, 2010
Paul, sorry I read too fast.
Given a date as a serial number, the =YEAR(datenumber) function gives the year and you can use that to create Jan 1 of that year =DATE(year,1,1).
So the difference of the date, and the first of the year plus one should give the day number.
So if cell A1 contains the date, then =A1-DATE(YEAR(A1),1,1)+1 should give the day number of the date serial number in A1.
That's not what the OP asked about; he wants year and day number.
Anyway, the yyyy-mm-dd format is already available in Excel (2007, at least, and when
Windows is set to use that format).
On 17 Jul 2010 at 14:10, Tex Texin wrote:
> If you go into number formats and choose custom, you can enter
> yyyy-mm-dd or
> 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
> 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.
> From: ISO8601@yahoogroups.com [mailto:ISO8601@yahoogroups.com] On
> Behalf Of
> Sent: Saturday, July 03, 2010 8:44 PM
> To: ISO8601@yahoogroups.com
> Subject: [ISO8601] ISO8601 Std for Day in Year?
> 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.
> Paul Myers
- << Previous post in topic Next post in topic >>