Format Date Display, Keep Date Format

[Chris]

I need to format a columns datetime Display, but keep it's value in both the RadGrid And Excel Export using Biff(or someway)

<telerik:GridBoundColumn DataField="Recorded Date2" <br>            FilterControlAltText="Filter Reported Date column" HeaderText="Reported Date" <br>            ReadOnly="True" SortExpression="Recorded Date" UniqueName="Recorded_Date" ItemStyle-HorizontalAlign="Right" DataType="System.DateTime" DataFormatString="{0:MM/dd/yy}"><br>        </telerik:GridBoundColumn>

I want my column to Display 06/18/13 in my Grid.

And I want it to Display 06/18/13 in my Excel Export.

However, I want my DateTime field in Excel to Retain it's DateTime attribute and be sortable.

Tuesday, June 18, 2013 8:00:00 AM	06/18/13
Tuesday, June 18, 2013 8:00:00 AM	Tuesday, June 18, 2013 12:00:00 AM

This is my export before and after.  I modified the  Excel Cell Format of column B2 after export.  I want it to display Tuesday, June 18, 2013 8:00:00 AM instead of Tuesday, June 18, 2013 12:00:00 AM.

[Princy]

Hi Chris,

To obtain date as 06/18/13 in Grid as well as Excel.Please try the code snippet.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true" >
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
     <CommandItemSettings ShowExportToExcelButton="true"  />
       <Columns>
          <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                SortExpression="ShippedDate" DataFormatString="{0:d}">
            </telerik:GridDateTimeColumn>
       </Columns>
   </MasterTableView>
</telerik:RadGrid>

To obtain date time as Tuesday, June 18, 2013 8:00:00 AM. Please try this code snippet.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true" >
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
     <CommandItemSettings ShowExportToExcelButton="true"  />
       <Columns>
          <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                SortExpression="ShippedDate" DataFormatString="{0:f}" PickerType="DateTimePicker">
            </telerik:GridDateTimeColumn>
       </Columns>
   </MasterTableView>
</telerik:RadGrid>

Thanks,
Princy

[Chris]

Princy,
Is it possible to Show date 06/13/2013, but retain the full Date/Time info in Excel?

[Jayesh Goyani]

Hello,

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        dynamic data = new[] {
            new { ShippedDate = DateTime.Now.AddDays(1)},
            new { ShippedDate = DateTime.Now.AddHours(1)},
            new { ShippedDate = DateTime.Now.AddMonths(1)},
            new { ShippedDate = DateTime.Now},
            new { ShippedDate = DateTime.Now}
        };

        RadGrid1.DataSource = data;

    }

    protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.ExportToExcelCommandName)
        {
            foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
            {
                item["ShippedDate"].Text = Convert.ToDateTime(item.GetDataKeyValue("ShippedDate")).ToString();
            }
        }
    }

<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true"
      OnNeedDataSource="RadGrid1_NeedDataSource" onitemcommand="RadGrid1_ItemCommand">
      <MasterTableView AutoGenerateColumns="False" DataKeyNames="ShippedDate"  CommandItemDisplay="Top">
          <CommandItemSettings ShowExportToExcelButton="true" />
          <Columns>
              <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                  SortExpression="ShippedDate" DataFormatString="{0:d}">
              </telerik:GridDateTimeColumn>
          </Columns>
      </MasterTableView>
  </telerik:RadGrid>

Thanks, Jayesh Goyani.

[Chris]

<asp:Button OnClientClick="return gridExport()" ID="Button1" runat="server"
            Text="Export To Excel" UseSubmitBehavior="False" />

This is what I'm using this for my Export button.

How can I intercept this in the C#?

[Princy]

Hi Chris,

PLease try the following code snippet.


ASPX:

<telerik:RadButton ID="rbtIGTExportToExcel"  Text="Export To Excel" OnClick="btnExcel_Click" runat="server" />

C#:

protected void btnExcel_Click(object sender, EventArgs e)
{
 foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
 {
   item["ShippedDate"].Text = Convert.ToDateTime(item["ShippedDate"].Text).ToString("dd-MM-yy hh:mm:ss");
 }
   RadGrid1.MasterTableView.ExportToExcel();
}

Thanks,
Princy