5 Replies Latest reply: May 15, 2014 10:04 AM by ARICE01 _ RSS

    Translating Week Numbers to Dates

    ARICE01 _

      I have been give a series of week numbers and related years.  I need to translate these week#/year# into the Start of the week dates. For example, if week # 2 of 2009 began on Monday, 1/5/2009.  I want Monarch to return the Monday date for each week#/year# combination it finds in the database.  Thanks in advance for your help

        • Translating Week Numbers to Dates
          Grant Perkins

          I suspect that your most cost effective route would be to upgrade to V10 where I believe you will find your requirements and more readily available as new functions.

           

          If that is not an option for some reason I guess you will need to take the time to build a translation table so let us know but frankly the upgrade is likely to be a better way forward.

           

           

          HTH.

           

          Grant

            • Translating Week Numbers to Dates
              ARICE01 _

              Thanks Grant for your quick response.  Actually, I am already on V10.  As this is my first time encountering this requirement, I am not sure which Function to use.  Please advise and thanks again.

                • Translating Week Numbers to Dates
                  Grant Perkins

                  Thanks Grant for your quick response. Actually, I am already on V10. As this is my first time encountering this requirement, I am not sure which Function to use. Please advise and thanks again.[/quote]

                   

                  Ah, OK. Your profile still indicates V9.

                   

                  I think you will need to set up you fiscal year information in the 'Options - Time Intervals' sections and then work out a reverse engineer from there to convert a given period back to a date using a reference point offset.

                   

                  So, for example, if the period is a week and you can get to the first date of the year you can then calculate the first day or date of the period in question.

                   

                  It's not something I have yet used myself other than for brief experimentation so the best I can suggest is to dig through the help files for the new functions if you have no already done so and see if that uncovers the the key to the solution. Obviously the functions are primarily intended to work the other way - from date to period number - but reversing that process should be possible I think.

                   

                  I'll experiment some more later if I get time.

                   

                   

                  Grant

                    • Translating Week Numbers to Dates
                      Nick Osdale-Popa

                      You could try this formula:

                      WeekNum = week number from report

                      RptYear = Year from report

                      (Formula name StartWeek)

                      CTOD("1/1/"str(RptYear))(2-WEEKDAY(CTOD("1/1/"str(RptYear))))((WeekNum*7)-7)

                      /code

                       

                      Though, you'll probably want to make another formula to determine if the monday on Week 1 is in the previous year of the given year. Then just make that equal to the 1st day of the year given.

                      If(Year(StartWeek)<RptYear,CTOD("1/1/"+str(RptYear)),StartWeek)

                      /code

                        • Translating Week Numbers to Dates
                          ARICE01 _

                          Thanks Nick:

                           

                          This works like a charm.  It is the great knowledge and timely responses from experts like you and Grant Perkins that reinforces my commitment to Monarch.  I am usually one of the first to upgrade as I look to leverage all the additional functionality. 

                           

                          Thanks guys... you are the greatest!!!