Loading ...
Sorry, an error occurred while loading the content.

2206RE: [ISO8601] ISO8601 Std for Day in Year?

Expand Messages
  • Tex Texin
    Jul 18, 2010

      John, thanks.

      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.



      From: ISO8601@yahoogroups.com [mailto:ISO8601@yahoogroups.com] On Behalf Of John Bogie
      Sent: Sunday, July 18, 2010 3:52 AM
      To: ISO8601@yahoogroups.com
      Subject: Re: [ISO8601] ISO8601 Std for Day in Year?



      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
      > 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.
      > tex
      > From: ISO8601@yahoogroups.com [mailto:ISO8601@yahoogroups.com] On
      > Behalf Of
      > prmyers1
      > 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.
      > Thanks.
      > Paul Myers
      > prmyers@...

    • Show all 6 messages in this topic