Solved

Excel to format date column

Posted on 2016-09-01
17
45 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 49

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
 

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 49

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 49

Expert Comment

by:Rgonzo1971
ID: 41779405
Which columns?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 49

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 49

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 69

Accepted Solution

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

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now