Solved

Excel to format date column

Posted on 2016-09-01
17
51 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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
 

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 70

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

How our DevOps Team Maximize Uptime

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

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

825 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