8 Replies Latest reply: May 15, 2014 10:02 AM by Data Kruncher RSS

    Time Span and Excel Export

    Patt _

      I am trying to export a Time Span column (nn:nn:nn) to Excel, but every time it gets put just into seconds in Excel upon export:

       

      Original in EZ-Pickin's: 03:18:07

      Exported to Excel: 11887 (which is equivalent to (36060) + (18*60) + 7)

       

      Now I can get it to copy and paste directly across to Excel without loss of the Time Span, from [URL="http://www.monarchforums.com/showthread.php?t=1185&highlight=timespanexcel"]http://www.monarchforums.com/showthread.php?t=1185&highlight=timespanexcel[/URL], but I am still unable to export it that way.  I have tried fiddling with all sorts of the Clipboard and Export options but to no avail.

       

      I am on Monarch 9.01 and Excel 2007.

       

      Any help would be greatly appreciated.

       

      Thanks.

        • Time Span and Excel Export
          Data Kruncher

          Hi Patt, and welcome to the forum. Nice to see another member from the same part of the world around here.

           

          The solution to your challenge is similar in nature to a [URL="http://www.monarchforums.com/showthread.php?t=2629"]recent post by JoeMcG[/URL].

           

          In order to export your time value literally, without having Excel convert it to the number of seconds after midnight, you need to create a character type calculated field and export that field to Excel. To do so, you need a simple formula like:

           

          [SIZE=2]DtoC(TimeField)[/SIZE][/code]

           

          Edit: Hopefully this works for you with Excel 2007 as it does for me with Excel 2003.

           

          HTH,

          Kruncher

            • Time Span and Excel Export
              Patt _

              Thanks for the response Kruncher.  However, I'm actually not using a Date field, I'm using a Numeric Time Span field.  I'm now looking at trying to find some sort of conversion from TimeSpan to Character to export that retains the "nn:nn:nn" formatting ...

               

              Edit:  just testing out SecondsToChar(), and it seems to be working, though I'll have to strip off the A.M. portion ... I'll update again when I resolve this for good.

               

              Patt

                • Time Span and Excel Export
                  Data Kruncher

                  Ah, of course. I have no regular use for Numeric Time Span fields so I always forget about that combination. Sorry. :o

                   

                  Since you've found the hard part of your solution, the only thing left is stripping off the AM/PM, and this'll do it easily:

                   

                  [SIZE=2]

                  LSplit(secondstochar(NTimeSpan),2," ",1)

                  /SIZE[/CODE]

                    • Time Span and Excel Export
                      Patt _

                      Perfect ... thanks for the help.  I had been using a Substr to do the same thing, but your solution is more elegant. 

                       

                      If you make it across the Strait I'll buy you a beer!

                        • Time Span and Excel Export
                          Data Kruncher

                          I've got family all up and down the Island, but lately only make it over a couple of times a year. But I look forward to it nonetheless!

                           

                          I'm trying to get together with an Excel guy up in Nanaimo too. He offered lunch. If we all get together, I'm covered! :cool:

                            • Time Span and Excel Export
                              Patt _

                              Well, this issue is still, unfortunately an issue.  When the seconds are less than 3600, the time, which I'd expect to see as 00:55:30 for example, shows up as 12:55:30.  Conversely, if the seconds over 43200, then it loops over into PM values.  I'm working on another solution right now, and will post it when I come up with it.  I'm close ...

                              • Time Span and Excel Export
                                Patt _

                                OK, so I'm now using the following code, which works in damn near every scenario I can think up.  From a very small number like 7 seconds (0:00:07) to a large number like 60000 seconds (16:40:00) to a huge number 500000 seconds (138:53:20).  Not only that, but it ensures that the hours and seconds are padded with zeros if necessary.  ElapsedTime is my field:

                                 

                                [SIZE=2]

                                trim(str(Int(ElapsedTime/3600))) + ":" +

                                str(int(mod(ElapsedTime,3600)/60),2,0,"0") + ":" +

                                str(mod(ElapsedTime,60),2,0,"0")[/SIZE][/CODE]