1 Reply Latest reply: May 15, 2014 10:10 AM by Data Kruncher RSS

    Help with date conversion

    Maurice _

      Need some help with converting this numeric data format from excel (40542.996794) into mm/dd/yyyy or short date and time using Monarch 10.5.

       

      Thank you,

       

      M

        • Help with date conversion
          Data Kruncher

          Hi Maurice,

           

          This turned out to be a very interesting challenge. You've probably spent some time looking at the conversion functions that Monarch offers, and if I'm correct (big IF), there isn't currently one that handles that specific conversion.

           

          But we can take advantage of the fact that Monarch allows us to add numeric values to a date to determine a new date value. In this case, we know the additional, or offset, value, but we don't really have a base date. So to that end, I had an idea that seems to work.

           

          If you name your numeric value ExcelDate, for instance, create a new Date type calculated field, and use this formula:

          CtoD("0","m/d/y")+ExcelDate[/CODE]

           

          Monarch creates the "base date" and then adds the number of days required. Depending upon how the ExcelDate value was calculated originally, or what settings were applied in Excel at the time, you may need to make a minor adjustment by adding a small value to the formula (+1 perhaps).

           

          HTH,

          Kruncher