5 Replies Latest reply: May 15, 2014 10:08 AM by SheridanBI _ RSS

    Date Formatting

    SheridanBI _

      I'm having some trouble switching my date format. Right now it is in mm/dd/yyyy, but I need for it to be in mm-yyyy. Do I need to convert out of date form, into character and then back to date in order to make this change? How can I do this? Thanks.

        • Date Formatting
          Olly Bond

          Hello Sheridan,

           

          There are two aspects to this - the input options which you can control in Monarch, say to deal with report data that comes in the form 31/12/1999 or 12/31/1999, and the Windows options that control how all applications show short dates, which are set in the control panel.

           

          In your case, it sounds like you need to adjust your regional settings in your Control Panel so that short dates are shown as MM-YYYY. Please note, that this will only work on your PC. If you export an Excel file, for example, with short dates in one field, and then send that to me, I'll see them as DD/MM/YYYY because that's what I've got my regional settings configured for.

           

          If you want to avoid that, then converting to character using the dtoc() function will be the best bet.

           

          HTH,

           

          Olly

          • Date Formatting
            Data Kruncher

            Hi Sheridan,

             

            If you're not about to perform any calculations on the resultant field, expecting it to be a date type field, then this formula will return the format that you want as a Character type field, where MyDate is your date field:

             

            Trim(Str(Month(MyDate),2,0,"0"))"-"Trim(Str(Year(MyDate)))[/CODE]

             

            If you really do need the field to be date, then Olly's suggestion of altering the PC configuration might be way to go. It seems a bit drastic though.

              • Date Formatting
                SheridanBI _

                I'm sure the control panel option will work however, it would be a fix for all of my Monarch reports and not unique to just this one. In many cases, I will need for the short date to include the day of the month as well. The reason the data must be in date and not character format, is that in the Excel project this is part of, data included in the report is based on certain date ranges that are part of "beginning date" and "ending date" drop down menus on my summary page. If those drop downs are formatted as characters and not recognized as dates, I'll see numbers such as "40179" to signify "01-2010" and "40210" for "02-2010" which would be too difficult for an "n" user to follow.

                  • Date Formatting
                    Olly Bond

                    Hello Sheridan,

                     

                    It's really annoying that you can't set date formats on a field-by-field basis in each model, but have to rely on a Windows-wide setting.

                     

                    To deliver something that makes sense, but is also useable, you could try using the real underlying dates as hidden key fields in your summary, but rendering only the pretty formatted character versions.

                     

                    HTH,

                     

                    Olly

                      • Date Formatting
                        SheridanBI _

                        Thank you both for your help. I've realized the conversions have to be done partly in Monarch and Excel both. I ultimately took the dates as they were originally formatted: mm/dd/yyyy and converted them from date format into character format, and back into date format in one calculated field so that instead of the mm/dd/yyyy format showing the day of each month: 01/01/2010, 01/02/2010, 01/03/2010 etc., every day in every month was formatted as the first day of the month. "01/03/2010" now reads "01/01/2010". The formula was this: 

                        Ctod(left(Dtoc(Date),2)"/1/"right(Dtoc(Date),4),"m/d/y")

                         

                        with "(date)" being the "01/03/2010" in date format. Once in Excel, it was recognized as a date and then I created the custom format of mm-yyyy which Excel recognized as a date as well.

                         

                        Side note: I tried to skip the conversion step in Excel to get mm-yyyy in Monarch by changing "m/d/y" in the formula to "m/y" and it did not work. Thanks again for the suggestions.