FAQs, Helpful Tips, and Screenshots

Hello, 

I've come across some very strange behavior, apparently in all reports. (This is CMS R18). I'm using standard reports - in this case the Historical>Agent>Group Summary Monthly report.

I run the report. If I export via Edit>Export All Data and then paste that into Excel, I get an array of columns equal to what's in the report. And from my experience with custom reports, I know that the format of the columns is under my control.

But if I click and drag to select just the values in any column that's showing an amount of time, then Ctrl-C to copy and then paste that into Excel, that single report column pastes in as two columns. One column is the number of seconds, the other is the hh:mm:ss format. This behavior is the same whether I've checked the "Export Time Durations in Seconds" box.

This apparently happens with every column that holds a time value, across what looks to be every report. I can't find any option or parameter that affects it either way. (Admittedly, I don't have access to the CMS server itself, just the CMS Supervisor.)

Why is this happening?

Thanks, 
Steve

------------------------------
Steve Charloff
Manager, Computer Assisted Coding Support
Optum360
------------------------------

This does not appear to be an Excel question. You can copy and paste to Notepad and get the same presentation. It looks like CMS is storing the data in both formats, hh:mm:ss as well as in seconds (out to several decimal places).

If you want the data in Excel, export to a .txt file, select field separator as tab, try text delimiter none, and check all three options (null values, labels and seconds). In Excel, use file open, text files and select the file you exported from CMS. You can declare each fields type as text or general or just let it rip for starters. The data will be imported into Excel and time will show time in seconds.

If you want to depict time as hh:mm:ss, you can export that way; or you can use a calculation. Divide the value in seconds, say 194.464263916/24/2600 (number of seconds/total seconds in a day) to depict as 0:03:14. Please note you will need to format the cell with that value as 'Time' and select 'Type' "37:50:55", which is cumulative time expressed as hours:minutes:seconds instead of time of day.

Hope this helps.

------------------------------
Kathleen DeSantis
Telecommunications Manager
Maimonides Medical Center
Brooklyn NY
------------------------------

Original Message:
Sent: 05-22-2020 05:15 PM
From: Steve Charloff
Subject: Reports - why is Export different from copy/paste

Hello,

I've come across some very strange behavior, apparently in all reports. (This is CMS R18). I'm using standard reports - in this case the Historical>Agent>Group Summary Monthly report.

I run the report. If I export via Edit>Export All Data and then paste that into Excel, I get an array of columns equal to what's in the report. And from my experience with custom reports, I know that the format of the columns is under my control.

But if I click and drag to select just the values in any column that's showing an amount of time, then Ctrl-C to copy and then paste that into Excel, that single report column pastes in as two columns. One column is the number of seconds, the other is the hh:mm:ss format. This behavior is the same whether I've checked the "Export Time Durations in Seconds" box.

This apparently happens with every column that holds a time value, across what looks to be every report. I can't find any option or parameter that affects it either way. (Admittedly, I don't have access to the CMS server itself, just the CMS Supervisor.)

Why is this happening?

Thanks,
Steve

------------------------------
Steve Charloff
Manager, Computer Assisted Coding Support
Optum360
------------------------------