Solved

Excel to format date column

Posted on 2016-09-01
17
37 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 48

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 48

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 48

Expert Comment

by:Rgonzo1971
ID: 41779405
Which columns?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 48

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 48

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 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

12 Experts available now in Live!

Get 1:1 Help Now