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

ISO8601 Std for Day in Year?

Expand Messages
  • prmyers1
    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
    Message 1 of 6 , Jul 3, 2010
    • 0 Attachment
      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@...
    • Tex Texin
      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
      Message 2 of 6 , Jul 17, 2010
      • 0 Attachment

        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@...

      • Will Scott
        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
        Message 3 of 6 , Jul 17, 2010
        • 0 Attachment
          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:
           
          =YEAR(E1)*1000+(E1-DATE(YEAR(E1),1,1)+1)

          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.

          HTH,

          Will


          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.

           

          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@acslink. net.au




        • Paul Myers
          Thanks for the info, Tex. I already know about the available Excel formats that can be accessed via the Format() statement in VBA for Excel as well as the
          Message 4 of 6 , Jul 17, 2010
          Thanks for the info, Tex.

          I already know about the "available" Excel formats that can be accessed via the "Format()" statement in VBA for Excel as well as the "Selection.NumberFormat" statement, also available in VBA for Excel.  The "Format()" statement requires a cell coordinate, such as "Format(B3, "yyyymmdd")" which the
          "Selection.NumberFormat" statement doesn't.   However, I have an Excel macro (copy attached including my additions, can be read using any text editor) that was published in the May 2008 issue of "(Australian) PC User : Excel Macro of the Month" which allows the user to apply a format to a cell by selecting the format from a list instead of having to use the builtin Excel "process".  Some of the desired ISO8601:2004 "formats" can't be constructed in this way.  That is, the following code segment shows what I am after and these formats already work as expected:

                      Select Case ListBox1.ListIndex
                          Case 0
                              Selection.NumberFormat = "ddd, dd mmm yyyy"
                          Case 1
                              Selection.NumberFormat = "dd mmm yyyy"
                          Case 2
                              Selection.NumberFormat = "ddd, dd-mmm-yyy"
                          Case 3
                              Selection.NumberFormat = "dd-mmm-yyyy"
                          Case 4
                              Selection.NumberFormat = "ddd, dd mmm yyyy hh:mm:ss"
                          Case 5
                              Selection.NumberFormat = "dd mmm yyyy hh:mm:ss"
                          Case 6
                              Selection.NumberFormat = "ddd, dd-mmm-yyyy hh:mm:ss"
                          Case 7
                              Selection.NumberFormat = "dd-mmm-yyyy hh:mm:ss"
                          Case 8
                              Selection.NumberFormat = "ddd, dd/mm/yyyy hh:mm:ss"
                          Case 9
                              Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
                          Case 10
                              Selection.NumberFormat = "ddd, dd/mm/yyyy"
                          Case 11
                              Selection.NumberFormat = "dd/mm/yyyy"
                          Case 12
                              Selection.NumberFormat = "ddd, ddmmmyyyy"
                          Case 13
                              Selection.NumberFormat = "ddmmmyyyy"
                          Case 14
                              Selection.NumberFormat = "ddd, ddmmmyyyy hh:mm:ss"
                          Case 15
                              Selection.NumberFormat = "ddmmmyyyy hh:mm:ss"
                          Case 16
                              Selection.NumberFormat = "yyyymmddhhmmss"
                          Case 17
                              Selection.NumberFormat = "yyyymmdd-hhmmss"
                          Case 18
                              Selection.NumberFormat = "yyyymmddhhmmss (ddd)"
                          Case 19
                              Selection.NumberFormat = "yyyymmdd-hhmmss (ddd)"
                          Case 20
                              Selection.NumberFormat = "yyyymmdd"
                          Case 21
                              Selection.NumberFormat = "yyyymmdd (ddd)"
                          Case 22
                              Selection.NumberFormat = "yyyy-mm-ddThh:mm:ss"
                          Case 23
                              Selection.NumberFormat = "yyyymmddThhmmss"
                          Case Else
                              'not selected
                      End Select

          It seems that it is currently, at least, no possible to enter code such as "Selection.NumberFormat = "yyyyWwwd"", etc, into Excel.  The additional "formats" that I would like to gain access to are shown below.

          I have also written a VB6ProSP6 demo program to generate dates and times according to a selection of the various ISO8601:2004 "formats" some of which can't (currently) be simulated using any of the Excel "Format" statements or the "Format Cell | Date, Time or Custom" dialog options.  The formats which are inaccessible in Excel, unless one writes a function such as the inbuilt "WeekDay(cell,2)" for the ISO8601 week commencement day of Monday = 1 thru Sunday = 7, are as follows and it is these that I would like to "simulate":
          • yyyyWww & yyyy-Www; for example, 2010W28 & 2010-W28
            • ISO8601:2000(E) | AnnexB | Examples | B.1 Dates | Calendar week
          • yyyyWwwD & yyyy-Www-D; for example, 2010W287 & 2010-W28-7
            • ISO8601:2000(E) | AnnexB | Examples | B.1 Dates | Week date
          • yyyyddd & yyyy-ddd; for example, 2010200 & 2010-200
            • ISO8601:2000(E) | AnnexB | Examples | B.1 Dates | Ordinal date
          If anyone knows how I might be able to include these formats into the above (and attached) macro code it would be much appreciated.

          Thanks,

          Paul

          On 18/07/2010 07: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@acslink. net.au


          --
          Paul Myers, JP(NSW), BSc(UNSW), MACS
          PO Box 7007 Karabar NSW 2620 AUSTRALIA
          Ph/Fx: 02 6299 2392 / +61 2 6299 2392
          Mobile: 041 629 9239 / +61 41 629 9239
          Email: prmyers@...
          Web: http://pmyers.pcug.org.au/

          "The only thing we have to fear is fear itself"
          - Franklin D. Roosevelt's First Inaugural Address, 04 March 1933
          FEAR - "False Evidence Appearing Real"
          See my favourite quotes by clicking here

        • John Bogie
          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
          Message 5 of 6 , Jul 18, 2010
          • 0 Attachment
            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).

            John

            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@...
            >
            >
            >
            >
            >
            >
            >
          • Tex Texin
            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
            Message 6 of 6 , Jul 18, 2010
            • 0 Attachment

              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).

              John

              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@...
              >
              >
              >
              >
              >
              >
              >

            Your message has been successfully submitted and would be delivered to recipients shortly.