5 Replies Latest reply: May 15, 2014 10:14 AM by elginreigner _ RSS

    Date Issue

    p12 _

      I am importing a report into monarch 11.3 which includes a column "Date Due".  The report has a number of dates which are outside of the regular calendar.  For example, 04/31/2012 and 02/30/2012.  When I import the data from Monarch into Access, the dates are not importing into Access and I am getting error messages.  If I manually adjust the dates to the first day of the next month, it will import ok.  I am trying to find a solution which will not require manual corrections.  Any ideas?

        • Date Issue
          Olly Bond

          Hello p12,

           

          In the report, define the field as character, and then in the table, define a calculated field "A" using the ctod() function. You should then see which ones are not valid dates as Monarch will recognise them as null.

           

          Define another calculated field "B" which adds one to the month and sets the day as "01", and ctod that.  Then the final calculated field "C" as if(isnull(A)=1;B;A). Hide A & B, export your data into Access, and column C should give you good data.

           

          Hope this helps,

           

          Olly

            • Date Issue
              Grant Perkins

              Hello p12 and welcome to the forum.

               

              Dates are often 'interesting' because different formats are used in different parts of the world.

               

              Unless you have something very peculiar going on Monarch should allow you to specify the default date format suppied by a report that might have a different default to your PC's regional date setting. There are a couple of ways to make adjustment and revisions to thing to suit inputs with unfriendly date formats. Olly has indicated one approach above.

               

              Have you already checked for consistency of format between your PC and the report you are working with?

               

               

              Grant

            • Date Issue
              elginreigner _

              I am importing a report into monarch 11.3 which includes a column "Date Due".  The report has a number of dates which are outside of the regular calendar.  For example, 04/31/2012 and 02/30/2012.  When I import the data from Monarch into Access, the dates are not importing into Access and I am getting error messages.  If I manually adjust the dates to the first day of the next month, it will import ok.  I am trying to find a solution which will not require manual corrections.  Any ideas?[/QUOTE]

               

              Quick question. Is there a specific reason your report has dates that don't exist?

                • Date Issue
                  p12 _

                  The report is from an outside source, external to my organization.  I assume it may be how the date due (the date that the next loan payment is due) is being calculated and is not taking into account the actual number of days in the month or if it is a leap year.

                    • Date Issue
                      elginreigner _

                      The report is from an outside source, external to my organization.  I assume it may be how the date due (the date that the next loan payment is due) is being calculated and is not taking into account the actual number of days in the month or if it is a leap year.[/QUOTE]

                       

                      Is the report setup old? Is it prone to errors? I only ask as adding values to dates is fairly common and simple in almost all programming languages and software products. I haven't seen bad date values like that on a report since college.