Setting cell using excel introp changes date

[AmitMMaheshwari]

I'm trying to enter a date value into an excel file.

For e.g.

"05/07/19" (July 5th of 2019) and the date formate of the cell in the excel is set to "dd-MM-yy" (regional setting of the computer is also the same). But when my code enters the value into excel, value is getting changed to "07-05-19" (May 7th of 2019).

I want it to stay as "05-07-19" and also excel should consider it as a date.

I have tried different ways to set the value into an excel,

const string DATE = "05/07/19";
Application app = new Application();
Workbook workBook = app.Application.Workbooks.Open(FILE_PATH);

Worksheet sheet = workBook.Sheets[1];
Range range = sheet.get_Range("A1");
range.set_Value(XlRangeValueDataType.xlRangeValueDefault, DATE);

Range range2 = app.get_Range("A1", "A2");
range2.Cells[2, 1] = DATE;

Range range3 = app.get_Range("A3", "A3");
range3.Value = DATE;

Range range4 = app.get_Range("A4", "A4");
//Of course, It is not setting the value as a Date but text.
range4.Value = new string[] { DATE };

I want my code to behave as per the formate of the cell. If the input can be fit as a date (based on the cell formate for date), it should be considered as a date, else just a text. (The way excel behaves when the user manually input the data)

Continue reading...