locked
Excel changes date format RRS feed

  • Question

  • User-664618749 posted

    Hi, I am automating an Excel worksheet from a C# ASP.NET application. I am writing different values to different cells. However when I try to add a date to a cell, Excel swaps the day and month format from, e.g. '08/09/09' to '09/08/09'.

    Here is the code I am using:

    Range range= (Range)worksheet.get_Range("G17", "G17");
    range.Value2 = "08/09/09";
    range.NumberFormat = "dd-MM-yyyy";
    range.NumberFormatLocal = "dd-MM-yyyy";

    I suspect that I need to change the LCID in the Excel file programatically- is this possible?

    The regional settings are set correctly on the server with the ASP.NET application and Excel file.

    Many thanks for any help- this is driving me crazy!

    Martin


     

    Tuesday, September 8, 2009 12:03 PM

Answers

  • User-159233185 posted

    Hi,

    surround the date with single quotes so that excel leaves the value without change

    like this "'08/09/09'"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 9, 2009 4:33 AM

All replies

  • User-2119480821 posted

    sometimes its hard to handle dateformats in excel.
    there are workarounds, change the cell format to Text and save the date.

    Tuesday, September 8, 2009 12:26 PM
  • User-664618749 posted

     Thanks, can I change the cell format programatically?

    I did some Googling, and found the following:

    range.NumberFormat="@";

     

    However, when I try that, my date is now output as '40034'.

     

    Wednesday, September 9, 2009 4:26 AM
  • User-159233185 posted

    Hi,

    surround the date with single quotes so that excel leaves the value without change

    like this "'08/09/09'"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 9, 2009 4:33 AM
  • User-664618749 posted

    Thanks, that worked! I only needed the first single quote though (it output the second quote into the text).

    range.Value2= "'08/09/09"; 

    Thanks!!

    Wednesday, September 9, 2009 4:45 AM