Date Formatting in ClosedXML

Question

I have date's in an excel sheet that I am reading from. What I like to do is to force the date to be in mm/dd/yyyy Is there a way to do this in Closed XML.

I took at a look https://closedxml.codeplex.com/documentation but could not find how to force or update the date to a certain format.

Should be something like `cell.Style.DateFormat.SetFormat("mm/dd/yyyy");` — Raidri, Jul 30 '15 at 09:29

Smit Patel · Answer 1 · 2015-10-09T05:48:36.270

22

If you want to do it on Specific cell then you have to use

worksheet.Cell(row,column).Style.NumberFormat.Format = "mm/dd/yyyy";

and

If you want to do it on multiple cells then you have to use

worksheet.Range(row,column,row,column).Style.NumberFormat.Format = "mm/dd/yyyy";

edited Oct 09 '15 at 05:48

answered Oct 08 '15 at 10:54

Smit Patel

2,992
1
26
44

Is there a way to add this on the entire cell for all ranges? – RetroCoder Oct 21 '15 at 23:49
2

Surely, You can apply it by using a for loop.same code juss in a loop. Nothing else – Smit Patel Oct 22 '15 at 12:30
I was thinking you can do this in the column header or header cell and it would just work for all ranges. Example: if it where in A cell. It would do it for A1 through A infinity. Exporting Excel in html would look like a tag. – RetroCoder Oct 22 '15 at 17:57
5

@RetroCoder No, doing apply on A1 cell it's work on only A1, and if you want to do it though A infinity - **worksheet.Colum(1).Style.NumberFormat.Format = "mm/dd/yyyy"** Same for Row. – Smit Patel Oct 22 '15 at 18:14
Ok, well you are the expert and I'll defer to your judgement then. Cheers. – RetroCoder Oct 22 '15 at 19:04
Yes, @Nate Pet is this a valid answer? – Smit Patel Jul 26 '16 at 04:21
When I set "mm/dd/yyyy", in export is displayed with dots "mm.dd.yyyy". Why? – David Pantea Sep 13 '21 at 13:56
It is `SetFormat` now – tozlu Sep 08 '22 at 07:20

Sathish · Answer 2 · 2023-02-08T11:22:46.333

Cell value should come as a datetime datatype. Specific cells or between the range.

Style.DateFormat or Style.NumberFormat both will works. Use the below code:

ws.Range(firstCellAddress,lastCellAddress).SetDataType(XLDataType.DateTime);
ws.Range(firstCellAddress,lastCellAddress).Style.DateFormat.Format = "mm/dd/yyyy"; // "dd/mm/yyyy" or whatever format needed"

ws.Range("A","C").SetDataType(XLDataType.DateTime);
ws.Range("A","C").Style.DateFormat.Format = "mm/dd/yyyy";

or

ws.Range(rangeAddress).SetDataType(XLDataType.DateTime);
ws.Range(rangeAddress).Style.DateFormat.Format = "mm/dd/yyyy";

ws.Range("A:D").SetDataType(XLDataType.DateTime);
ws.Range("A:D").Style.DateFormat.Format = "mm/dd/yyyy";

score 0 · Answer 3 · answered Jun 19 '20 at 01:59

I know the question has already been aswered... but since I still lost some hours after reading this, i'd like to leave this for my future me:

Make sure you are not clearing all formats after setting the cell format. I had this code at the bottom of the method...

var range = worksheet.Range(firstCell.Address, lastCell.Address);
range.Clear(XLClearOptions.AllFormats);

Date Formatting in ClosedXML

3 Answers3

Linked