Click here to Skip to main content
15,946,529 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hello,
I need to change the date value in datagridview to (dd/MM/yyyy hh:mm AM/PM) instead of (MM/dd/yyyy hh:mm AM/PM), when used this
[ DataGridView1.Columns("The_Date").DefaultCellStyle.Format = "dd'/'MM'/'yyyy hh':'mm tt" ]
just the format changed not the actual value,
I need to export datagridview to excel
I used this one
[ excel.Columns("B:B").numberformat = "dd/MM/yyyy hh:mm AM/PM" ]

When the pc's date is mm/dd/yyyy everything is ok,
But when the pc's date is dd/mm/yyyy the value doesn't view correctly in excel for the days between (1-12) means 1/2/2012 (1st Feb) appears as 2/1/2012 (2nd Jan),

So I need to change the date value in datagridview not just format,
Is there any way to do that?

Thanks,
Posted

Use Column.DefaultCellStyle.Format[^] property or set it in designer[^]

or
C#
dataGridView1.Columns[0].DefaultCellStyle.Format = "MM'/'dd'/'yyyy";

or

You can set the format you want:
C#
dataGridViewCellStyle.Format = "MM/dd/yyyy";
this.date.DefaultCellStyle = dataGridViewCellStyle;
// date being a System.Windows.Forms.DataGridViewTextBoxColumn


you can do like this..

C#
private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    // If the column is the Artist column, check the
    // value.
    if (this.dataGridView1.Columns[e.ColumnIndex].Name == "Artist")
    {
        if (e.Value != null)
        {
            // Check for the string "pink" in the cell.
            string stringValue = (string)e.Value;
            stringValue = stringValue.ToLower();
            if ((stringValue.IndexOf("pink") > -1))
            {
                e.CellStyle.BackColor = Color.Pink;
            }

        }
    }
    else if (this.dataGridView1.Columns[e.ColumnIndex].Name == "Release Date")
    {
        ShortFormDateFormat(e);
    }
}

//Even though the date internaly stores the year as YYYY, using formatting, the
//UI can have the format in YY.  
private static void ShortFormDateFormat(DataGridViewCellFormattingEventArgs formatting)
{
    if (formatting.Value != null)
    {
        try
        {
            System.Text.StringBuilder dateString = new System.Text.StringBuilder();
            DateTime theDate = DateTime.Parse(formatting.Value.ToString());

            dateString.Append(theDate.Month);
            dateString.Append("/");
            dateString.Append(theDate.Day);
            dateString.Append("/");
            dateString.Append(theDate.Year.ToString().Substring(2));
            formatting.Value = dateString.ToString();
            formatting.FormattingApplied = true;
        }
        catch (FormatException)
        {
            // Set to false in case there are other handlers interested trying to
            // format this DataGridViewCellFormattingEventArgs instance.
            formatting.FormattingApplied = false;
        }
    }
}


would you pls go through this link for More Info[^]
 
Share this answer
 
Comments
VJ Reddy 21-May-12 3:30am    
Good answer. 5!
Prasad_Kulkarni 21-May-12 3:32am    
Thank you VJ!
Eman Ayad 21-May-12 16:16pm    
Thank you, I already used that but I need to change the format of the value stored in the datagridview instead of format value just its display as mentioned in my question.
Maciej Los 23-May-12 12:19pm    
Good work, my 5!
Prasad_Kulkarni 23-May-12 23:49pm    
Thank you Isomac
Since the DateFormat is influenced by the CurrentCulture I think in the form load event the CurrentCulture can be set and then the CustomFormat may be set to the DataGridView Column as below
VB
'Set in the Form.Load event
Thread.CurrentThread.CurrentCulture = 
        System.Globalization.CultureInfo.InvariantCulture
        
DataGridView1.Columns("The_Date").DefaultCellStyle.Format  = 
        "dd/MM/yyyy hh:mm tt"
 
Share this answer
 
v2
Comments
Prasad_Kulkarni 21-May-12 3:34am    
Good one VJ! +5!
VJ Reddy 21-May-12 3:38am    
Thank you, Prasad :)
Eman Ayad 22-May-12 2:54am    
Thank you but when I put it this error appeared,
[ 'Thread' is not declared. It may be inaccessible due to its protection level ]
Maciej Los 23-May-12 12:20pm    
Good answer, my 5!
VJ Reddy 23-May-12 12:45pm    
Thank you, losmac :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900