4 Replies Latest reply: Aug 28, 2016 10:43 AM by Grant Perkins RSS

    Converting Excel Serial Date to "mm/dd/yy" format

    Al Rice

      Can any  one give me a procedure to Converting Excel Serial Date to "mm/dd/yy" format... for example 42170 converts to 06/06/15.  I am using Monarch 13.

       

      Thanks in advance.

       

      Al

        • Re: Converting Excel Serial Date to "mm/dd/yy" format
          Lloyd L

          Hi,

           

          I believe you want the same functionality of Excel in Monarch Classic. I tested it in version 13.5.0 and I do not see an option similar to it. The only available types are character and numeric. I'll get back to you if I find something helpful.

           

          -Lloyd

          • Re: Converting Excel Serial Date to "mm/dd/yy" format
            Grant Perkins

            Al,

             

            Monarch would, by my expectation, normally translate a serial date number to an actual "date" format. It's a display things rather than a data things unless I have misunderstood what you are asking.

             

            Are you just reading a column from Excel and wishing to display that numeric value as a date in Monarch?

             

            I assume monarch sees the field value as either numeric or character when it creates a field in the table.

             

            If you change the field in Monarch to be a Date field, what happens?

             

            Is it recognised, not recognised or do you see some fields recognised and others not recognised?

             

             

            Grant

              • Re: Converting Excel Serial Date to "mm/dd/yy" format
                Al Rice

                Hi Grant

                 

                Thanks for your reply. I should have been more clear. The Excel file (field=Comment) being read by Monarch contains alphanumeric data and where the lone, specific value is a date, the date is displayed as a Serial date value.  With my Monarch Model, I am calculating as 2nd Field (New Comment) and when I find the Serial Date value I want to include the it in New Comment for example:

                 

                “The Customer made final payment on 06/06/2015”

                 

                As a work around, I am creating this statement in the Excel file and read it into Monarch as a Character field, but for many reasons it would be more effective to create New Comment in the Monarch Model.

                 

                Hopefully I am clear.

                 

                Al

                 

                  • Re: Converting Excel Serial Date to "mm/dd/yy" format
                    Grant Perkins

                    Hi Al,

                     

                    Ok, now I see the problem.

                     

                    As I understand it the Excel Serial number represents the number of days since somewhere around 1900.  (or 1904)

                     

                    ( "around" seems rather vague but I got it here  - nominally from an Excel "Authority". http://www.exceluser.com/formulas/nineteen-date-calculations-in-excel.htm)

                     

                    So you have the option to create a calculated field using the Dateadjust function in Monarch.

                     

                    The basic idea is that you set the Known Date (the "around 1900" date) and then add the number of days you get from the Excel serial number. Format the result as a date.

                     

                    There are some odd things to consider.

                     

                    If you set the comparison date to 01/01/1900 in Monarch that gets the serial value 1 . (In Monarch 13.4 I see a Function for DateSerial1900() so you check the value by entering the date .i.e. DateSerial1900({01/01/1900}) will return 1. There is also a function for 1904 based dates)

                     

                    Note the { } brackets around the date. They are important but easily forgotten.

                     

                    For some reason of mathematical logic that currently escapes me if I create a formula with that start date and add the excel serial number the result date is 2 days wrong. I would have expected 1 day since in effect the function is adding the two numbers together and deriving a new date based on that.

                     

                    Interestingly in Monarch 12/31/1899 will return a serial of 0 and 12/30/1899 a serial  of -1. As presented in the link above Excel does not (or did not) "do" negative serial numbers.

                     

                    Anyway, whatever is going on, if you use 12/30/1899 as the basis date this calculation works.

                     

                    DateAdjust({12/30/1899},0,0,val(trim([Field 1])))

                     

                    Field 1 here is your Comment field.

                     

                    If there is text in the field the result is the basis date (12/30/1899)

                     

                    If only a number the Trim() function removes spaces as a precaution, VAL() converts the Characters to a Number of the calculation and the DateAdjust adds the required number of days. The calculated field needs to be a Date/Time type.

                     

                    I think the Mathematical calculation logic revolves around included or excluded days in date calculations. As long as the result can be shown to be consistent and correct it doesn't much matter how it is arrived at mathematically!

                     

                    From your initial message I assume that you are comfortable creating your New Comment field with the required text and adding the date to it - you just needed to get the date.

                     

                    Is that correct?

                     

                    If not I can help with the full formula for New Comment.

                     

                    Hopefully this idea will work for you.

                     

                    Grant