Wrong DateTime format when copy/paste from RadGridView into Excel

Question

I have a weird issue when copying rows from a Telerik's RadGridView into excel, the problem has to do with a DateTime column.

The column in RadGridView has the correct format:

06/17/2016 02:30 PM

But when the user copies the rows and pastes them into excel the format gets screwed up:

2016-06-16 14:30 PM //24hr format with PM period lol

I have noticed that the problem happens only with values whose hour is greater than 12, for example if the value is 06/17/2016 08:54 AM it will get pasted into excel with the correct format.

So thinking that it has to do with the format i manually set the format string of the column with the following code:

dgv_history.Columns["Repair Date"].FormatString = "{0:MM/dd/yyyy HH:mm}";

I also tried

dgv_history.Columns["Repair Date"].FormatString = "{0:MM/dd/yyyy hh:mm tt}";

But these lines of code only change how the date is displayed in the RadGridView, if i try to copy/paste them into excel the problem persists.

This is how it looks like once pasted into excel:

2016-06-16 13:57 PM
2016-06-16 13:31 PM
6/16/2016 12:55
6/16/2016 12:33
6/16/2016 12:22
6/16/2016 11:48
6/16/2016 11:19

What can i do to solve this problem?, is it a bug?, i have been searching over their documentation but i still can't find anything related.

Thank you

Update 06/20/2016 12:15

Seems that this is a normal functionality of the RadGridView, as seen in this demo page http://docs.telerik.com/devtools/winforms/gridview/copy-paste-cut the result is the same as the one described in this question.

Answer 1

I don't have a real answer to the question, but have a look to:

http://docs.telerik.com/devtools/wpf/controls/radgridview/clipboard/copying.html

You have the possibility to override the copied values (and you can check with the debugger what telerik is writing to the clipboard).

Sorry for adding this as answer, but I'm not yet allowed to post a comment.

Cheers

Thomas

Answer 2

I experienced a similar problem when creating time cards years ago - no body likes to use that 24 hour time format conversion - and the 12 hour am/pm can throw people who aren't paying attention especially when copy/pasting from a 24 hour to double 12 hour format - converting data entry time into calculate-able formula in a basic formula example - once I incorporated a 12- into the formula where I started the formula: =IF(C8>D8,12-C8+D8,D8-C8 and then multiplied it back to a negative 1 later it solved itself.

Answer 3

Check if the Datetime settings on the client pc's regional settings are correct. Just as a suggestion if nothing else works

Answer 4

I think the datetime that RadGridView generated is always like "2016-06-16 13:57 PM" however excel formats it by its builtin functionality. It is possibly caused by missing "PM" so that Excel added it and also convert it to the default datetime format. You can just change the cell property to "Text" before copying that should always treat your data as a string.

Answer 5

I was able to solve the problem, but i had to manipulate the data object being added to the clipboard.

As it turns the only problem is the period being appended at the end of the DateTime value, so in order to fix it i removed all periods in all datetime values using the following code.

DataObject dataobject = dgv_history.GetClipboardContent();

//Gets the data object as a CSV string
string dos = dataobject.GetText(TextDataFormat.CommaSeparatedValue);

//Replaces all the occurrences of AM & PM with an empty string 
dos = dos.Replace(" AM", string.Empty);
dos = dos.Replace(" PM", string.Empty);

//Sets the modified CSV string as the DataObject
dataobject = new DataObject(dos);

//Adds the DataObject to the clipboard
Clipboard.SetDataObject(dataobject, true);

By doing this, the date time gets pasted as a nicely formatted 24 hr format, and gets detected as such by excel.

There was another temporary solution i was able to create, but involves our SSRS server. I created a simple report with the same parameters used for the RadGridView, the report output can be easily downloaded as an excel spreadsheet without having to worry of the data being manipulated or translated by a WinFroms control.

Thanks everyone for your time and answers.