How can I format a column of dates

Quick question for you – how can I format a column of dates – as dates?? I have a date column in my dataset – comes out as integers in Excel… I’m understand how to determine the column needing to be formatted – just don’t understand how to format as a date… can you assist??

Many thanks,

Andrew

Dear Andrew,

Thanks for your consideration.


Two methods:
1. You can use Cells.ImportDataTable to import the data in your dataset. The data column type should be DateTime.

2. You can use Cell.Style.Number or Cell.Style.Custom to set cell number format. Thus the integer value can be shown as a date.

Hello,
I am using the Cells.ImportDataTable to import the data in to my report but my date columns are showing up as large integer values (ie: 38264.64973). Once in excel I can manually change the column’s data type to Date and the correct date shows.

Is there any way to force date columns to display in date format once imported in to excel? I’m using version 2.6.0.0

thank you,
Justin

Hi Justin,

1. You can create a designer file(template) and set data format to columns in that file.

2. You can create a range on columns, and use Range.Style to set date format.

Hi Laurence,

I was having the same problem with date columns and also thought the first method would work. After I fill my DataTable I set my column to a DateTime

DataTable.Columns("DATECREATED").DataType = System.Type.GetType("System.DateTime")

I create a DataView to filter out rows and use Excel.Workseets(0).Cells.ImportDataView(dv,2,0,True)

My date column is still a showing as a large decimal (38056.4138888889). I want to note that I'm looping through the columns to auto fit before I save the excel.

Dim intCol As Byte
For intCol = 0 To 33
Excel.Worksheets(0).AutoFitColumn(intCol)
Next

Excel.Save(strPath, Excel.FileFormatType.Default)

Thanks,

-Randy

FYI: I'm Using 2.7.2.1



Hi Randy,

Following are my approaches to format a column as Date:

1. Using a designer file
Dim excel1 As Excel = New Excel()

'Date.xls is a blank excel file. I format column D as Date number format
excel1.Open(“d:\date.xls”)

Dim cells1 As Cells = excel1.Worksheets(0).Cells
'Create data table
Dim dt As DataTable = New DataTable(“Products”)
dt.Columns.Add(“Product_ID”,Type.GetType(Int32))
dt.Columns.Add(“Product_Name”,Type.GetType(String))
dt.Columns.Add(“Units_In_Stock”,Type.GetType(Int32))
dt.Columns.Add(“Time”, Type.GetType(DateTime))
Dim dr As DataRow = dt.NewRow()
dr(0) = 1
dr(1) = “Aniseed Syrup”
dr(2) = 15
dr(3) = New DateTime(2004, 1, 15)
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 2
dr(1) = “Boston Crab Meat”
dr(2) = 123
dr(3) = DateTime.Now
dt.Rows.Add(dr)
Dim dv As DataView = dt.DefaultView
cells1.ImportDataView(dv, 0, 0, False)
Dim i As Integer
For i = 0 To 3
excel1.Worksheets(0).AutoFitColumn(CType(i, Byte))
Next

excel1.Save(“d:\book1.xls”)

2. Format column at run time

Dim excel1 As Excel = New Excel()

Dim cells1 As Cells = excel1.Worksheets(0).Cells
cells1.Columns[3].Style.Number = 14
'Create data table
Dim dt As DataTable = New DataTable(“Products”)
dt.Columns.Add(“Product_ID”,Type.GetType(Int32))
dt.Columns.Add(“Product_Name”,Type.GetType(String))
dt.Columns.Add(“Units_In_Stock”,Type.GetType(Int32))
dt.Columns.Add(“Time”, Type.GetType(DateTime))
Dim dr As DataRow = dt.NewRow()
dr(0) = 1
dr(1) = “Aniseed Syrup”
dr(2) = 15
dr(3) = New DateTime(2004, 1, 15)
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 2
dr(1) = “Boston Crab Meat”
dr(2) = 123
dr(3) = DateTime.Now
dt.Rows.Add(dr)
Dim dv As DataView = dt.DefaultView
cells1.ImportDataView(dv, 0, 0, False)
Dim i As Integer
For i = 0 To 3
excel1.Worksheets(0).AutoFitColumn(CType(i, Byte))
Next

excel1.Save(“d:\book1.xls”)

Thanks Laurence,

The second method seems to work the best for me. Do you see how I was trying to be more preemptive by making the column in the datatable a DateTime?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

DataTable.Columns("DATECREATED").DataType = System.Type.GetType("System.DateTime")

I was thinking during the import the data column would be outputted as the string format of the date instead of masking the large decimal column with Excel formatting.

-Randy