CODEWITHSUNDEEP
c#exceldatetimedatedatetime-format
GianT971
GianT971

Reputation: 4523

C# to Excel set custom date display and value

Given a certain date, I want to set the value of a cell with a DateTime object, but without the "Time" information. For example, for today the value would just be "29/06/2012" and not "29/06/2012 16:54:36".

Concerning the display, for today's date I want it to be written like this (it is in french, I don't know how it would be in english): "29 juin" and not "29/06/2012".

How can I achieve this?

EDIT : I just took a look at the display formatting I need in Excel, it is "jj mmmm" ("dd mmmm" in C#). But the cell, though taking the value, does not take the formatting. Here is a piece of code:

                 cell.Value = string.Format("{0:dd/MM/yyyy}", DateTime.Now);
                 cell.NumberFormat = "jj mmmm";

I also tried:

                 cell.Value = DateTime.Now;
                 cell.NumberFormat = "jj mmmm";

In that case, the display formatting is OK, but the cell value contains the Time information, which is not OK.

Upvotes: 2

Views: 3953

Answers (4)

Chris Gessler
Chris Gessler

Reputation: 23123

just change DateTime.Now to DateTime.Date and the time will be zero'd out

Upvotes: 0

Nevyn
Nevyn

Reputation: 2683

See this StackOverflow question, and my answer to it. It will allow you to set the custom format for the cell directly in the excel sheet, from there you just need to research what the different date format strings are.

That is only helpful if you are using excel automation though.

Otherwise, formatting the DateTime.ToString output will be better. Once again, researching the different DateStringFormatting options will be helpful to you.

Final option. Directly change the template, not using automation. Goto the cell and manually change the cell format until the display is what you want. This does not change the actual data, just its display.

UPDATE

There are two specific issues to deal with.

Issue one is making sure excel recognizes that this IS a date. To that end, make certain that the date data itself is formatted en-US. Why, because Microsoft is dumb, and doesn't recognize international date formats.

Issue two is the display format for the cell. For a 3 letter abbreviation of the month, use the string d mmm. If you want the full month name, use d-mmmm.

I was testing and noted that excel refused to treat 29/06/2012 16:54:36 as a date, but it accepted 06/29/2012 16:54:36 without an issue. As I said, microsoft is dumb.

Upvotes: 1

Jordan
Jordan

Reputation: 338

// Displays Fri 29 Aug
Console.WriteLine(date1.ToString("ddd d MMM", CultureInfo.CreateSpecificCulture("fr-FR")));

This would be a good resource for you: msdn custom date and time format strings

Upvotes: 0

Lucas_Santos
Lucas_Santos

Reputation: 4740

DateTime has a method called ToShortDateString

Upvotes: 0

Related Questions