[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel to format date column

Posted on 2016-09-01
17
Medium Priority
?
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 53

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 53

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 53

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 53

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

649 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