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

2207Re: [ISO8601] ISO8601 Std for Day in Year?

Expand Messages
  • Will Scott
    Jul 17, 2010
      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.




      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.


      Paul Myers
      prmyers@acslink. net.au

    • Show all 6 messages in this topic