10 Replies Latest reply: May 15, 2014 9:52 AM by Grant Perkins RSS

    Lost formatting in from Summary Export

    TOMerwick _

      When exporting data (dates & numbers) from the summary window I am losing their intended formats.  However, when I export from the table using the same fields I do not lose the formats.  I.E. a date from the summary window is exported and placed in Excel as '04/02/2004, but when exported in the table it shows up in Excel as 04/02/2004 - a real date!  Has anyone encoutered this problem with summary exports.  Is there a fix.  I am using Monarch V7 Pro.

        • Lost formatting in from Summary Export
          RalphB _

          In Monarch, do you have your date column format set as character or as date?

           

          If you have it set as character you should have no problem exporting out to excel as "character" format of '04/26/2004.  If it is set as "date" format you will get the date format of 4/26/2004 in excel.

          • Lost formatting in from Summary Export
            Nick Osdale-Popa

            It's by design. Yeah, it sucks.

            Originally posted by TOMerwick:

            When exporting data (dates & numbers) from the summary window I am losing their intended formats.  However, when I export from the table using the same fields I do not lose the formats.  I.E. a date from the summary window is exported and placed in Excel as '04/02/2004, but when exported in the table it shows up in Excel as 04/02/2004 - a real date!  Has anyone encoutered this problem with summary exports.  Is there a fix.  I am using Monarch V7 Pro. /b[/quote]

            • Lost formatting in from Summary Export
              Bruce _

              The only fix is to use a vba macro in the receiving program.

               

              As Nick says "It sucks"

               

                :cool:

              • Lost formatting in from Summary Export
                TOMerwick _

                Thanks Nick and Bruce, I love the brutal truth!  Ralph, please note that your reply is only true regarding exporting from tables – not summaries!

                • Lost formatting in from Summary Export
                  RalphB _

                  Thats odd.  I have been exporting summary sheets for over a year now with a date column set as character and each one of them shows up in excel as text!!

                  • Lost formatting in from Summary Export
                    TOMerwick _

                    Exactly what I don't want.  My point, and it does suck, is that when exporting from summaries a date is always "text" no matter how you have it formatted.  Thus Excel sees the date column as text not a date.  That sucks too1

                    • Lost formatting in from Summary Export
                      Grant Perkins

                      What problem does that leave you with Tom?

                       

                      I seem to recall that there was a useful comment on why this works as it does but cn;t seem to find it at the moment.

                       

                      When I run the export to Excel 2000 I get the text output for dates but I can still perform date like calculations in Excel and get the desired result without altering the column format.

                       

                      For example -10  will give the result as a date 10 days earlier that .

                       

                      Subtract one date field from another and the result will be in days.

                       

                      So although the Excel fields may appear to have the wrong format option after the export they still offer many of the attributes expected from date fields.

                       

                      Perhaps there is another way around the problem you are left with.

                       

                      Grant

                       

                       

                      Originally posted by TOMerwick:

                      Exactly what I don't want.  My point, and it does suck, is that when exporting from summaries a date is always "text" no matter how you have it formatted.  Thus Excel sees the date column as text not a date.  That sucks too1 /b[/quote]

                      • Lost formatting in from Summary Export
                        TOMerwick _

                        Grant, it all boils down to the fact that exporting from summaries does not maintain the intended formats from the field properties.  Not to waste any more time on this but to give you an example - try to format a date exported in date format in a Monarch summary to Excel.  I.E. a cell with '4/27/2004 cannot be so easily formatted to 04/27/04 - thats what I want, not text but a real date from the summary....

                        • Lost formatting in from Summary Export
                          Grant Perkins

                          OK, I see what you mean.

                           

                          I'm no Excel expert - Nick O-P gets the kudos for that.

                           

                          Nor am I a programmer so the only approach I would be likely to use would involve a calculated column using something like DATEVALUE() or -0 and formatting the result to requirements.

                           

                          I guess the problem relates to the internal requirements for clear identification of which year is to be used but why the Table and Summary should be different is a puzzle.

                           

                          Somewhere I seem to recall an explanation but still haven't found it.

                           

                          Grant

                           

                          Originally posted by TOMerwick:

                          Grant, it all boils down to the fact that exporting from summaries does not maintain the intended formats from the field properties.  Not to waste any more time on this but to give you an example - try to format a date exported in date format in a Monarch summary to Excel.  I.E. a cell with '4/27/2004 cannot be so easily formatted to 04/27/04 - thats what I want, not text but a real date from the summary.... /b[/quote]

                          • Lost formatting in from Summary Export
                            Grant Perkins

                            OK, found the reference I was looking for  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000080#000002"]HERE[/url].

                             

                            Clearly explains the reasons though of course does little to help your particular problem.

                             

                             

                            Grant

                             

                            [size="1"][ May 19, 2006, 01:11 PM: Message edited by: Todd Niemi ][/size]