?
Solved

Excel to format date column

Posted on 2016-09-01
17
Medium Priority
?
95 Views
Last Modified: 2016-09-01
I had this question after viewing Datatable to display only time of datetime field.

Hello,
is there a way to format date and time columns in excel .
Please refer my earlier question to have a code view
0
Comment
Question by:RIAS
17 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41779373
If you look at the format options for "Date" you will find a few options which display the date as well as the time.
0
 

Author Comment

by:RIAS
ID: 41779375
This is the vb.net code I am using to export the datagridview datatable to excel
 Function ExportToExcel(ByVal intCreateNew As Integer, ByVal dtGridData As DataTable, ByVal FilePath As String, ByVal StrSheetname As String, Optional ByVal xlApp As Excel.Application = Nothing) As String
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Application.DoEvents()
        Try
            FrmMain.Cursor = Cursors.WaitCursor
            xlApp.DisplayAlerts = False
            If intCreateNew = 0 Then

                xlApp.Workbooks.Add()
            End If
            If IsNothing(FilePath) = True Then
                xlWorkSheet = Nothing
                xlWorkBook = Nothing
                xlApp = Nothing
                FrmMain.Cursor = Cursors.Default
                Return "Cancelled"
                Exit Function
            End If
            xlApp.Workbooks(1).SaveAs(FilePath)
            Application.DoEvents()
            xlWorkBook = xlApp.Workbooks.Open(FilePath)
            Application.DoEvents()
            If intCreateNew = 0 Then
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            Else
                xlWorkBook.Worksheets.Add(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            End If
            xlWorkSheet.Name = StrSheetname
            With xlWorkSheet.PageSetup
                .PrintGridlines = True
                .CenterHeader = StrSheetname
                .Zoom = False
            End With
            Dim dtRowCount As Integer = dtGridData.Rows.Count
            Dim dtColCount As Integer = dtGridData.Columns.Count

            Dim objXlColHeaderData(1, dtGridData.Columns.Count) As Object
            Application.DoEvents()
            For i As Integer = 0 To dtColCount - 1
                objXlColHeaderData(0, i) = dtGridData.Columns(i).ColumnName
            Next
            Dim objXlData(dtRowCount, dtColCount) As Object
            For iRow As Integer = 0 To dtRowCount - 1
                Application.DoEvents()
                For iCol As Integer = 0 To dtColCount - 1
                    Application.DoEvents()
                    If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then
                        objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
                    Else
                        objXlData(iRow, iCol) = ""
                    End If
                Next
            Next
            Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")


            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            xlRange.Value2 = objXlColHeaderData
            xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, dtColCount)).Font.Bold = True
            xlRange = xlWorkSheet.Range("A2")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            ' xlRange = xlRange.Resize(dtRowCount + 1, dtColCount + 1)
            xlRange.Value2 = objXlData
            With xlWorkSheet
                .Range(.Cells(1, 1), .Cells(1, 1)).Select()
            End With
            With xlWorkSheet.Application.ActiveWindow
                .SplitColumn = 0
                .SplitRow = 1
            End With
            xlWorkSheet.Cells.EntireColumn.AutoFit()
            xlWorkSheet.Application.ActiveWindow.FreezePanes = True
            Application.DoEvents()
            xlWorkBook.Save()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "ErrorIn ExportToExcel", MessageBoxButtons.OK, MessageBoxIcon.Error)
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            FrmMain.Cursor = Cursors.Default
        Finally
            FrmMain.Cursor = Cursors.Default
        End Try
        Return "True"
    End Function

Open in new window

0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41779386
Hi,

pls try

xlWorkSheet.Range("B:B").NumberFormat = "dd-MM-yyyy hh:mm;@"

Open in new window

Regards
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:RIAS
ID: 41779387
Will try and get back mate!
0
 

Author Comment

by:RIAS
ID: 41779398
Rgonzo1971,
Thanks it worked mate! Is it possible to set certain columns to just time and not display date?
I have two columns which need to display only time. Please refer the above code
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41779400
then try

xlWorkSheet.Range("B:B").NumberFormat = "hh:mm;@"
0
 

Author Comment

by:RIAS
ID: 41779402
Rgonzo1971,
Thanks but need it just for 2 columns and the rest should be date.
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41779405
Which columns?
0
 

Author Comment

by:RIAS
ID: 41779406
Columns 3 and 4
0
 

Author Comment

by:RIAS
ID: 41779415
xlWorkSheet.Range("B:B").NumberFormat = "hh:mm;@"

Still gives me datetime
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41779431
then try

xlWorkSheet.Range("C:D").NumberFormat = "hh:mm;@"
1
 

Author Comment

by:RIAS
ID: 41779443
Trying...
0
 

Author Comment

by:RIAS
ID: 41779462
nope didnt work
xlWorkSheet.Range("C:D").NumberFormat = "hh:mm;@"
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41779464
it probably means that the dates are not recognized as such in excel
0
 

Author Comment

by:RIAS
ID: 41779465
It looks like its not working at all.
   xlWorkSheet.Range("B:B").NumberFormat = "DD-MMM-yyyy;@"
Still giving me
08/11/2016 00:00
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 41779718
0
 

Author Comment

by:RIAS
ID: 41779825
This was the related question on excel selection
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question