Post #403,886
8/7/15 10:38:29 AM
|
Trying to format a Excel column as Date from VB.Net
Got tasked with writing a quicky program to pull records out of a SQL Server table and stick them into Excel, to be part of a daily batch run. Everything works except for the 2 date fields. A DATETIME value of "08/05/2015" displays as "42221.00" in Excel. Have tried these following statements, all with the same result.
These 2 statements, taken from an existing program, should work (so I've been told): rng = excelApp.Range("S2:S" & lastrow) rng.NumberFormat = "mm/dd/yyyy"
excelSheet.Columns("S2:S" & lastrow).select() excelApp.Selection.NumberFormat = "mm/dd/yyyy"
rng = excelApp.Range("S2:S" & lastrow) rng.Select() rng.NumberFormat = "mm/dd/yyyy"
rng = excelApp.Range("S2:S" & lastrow) rng.Select() rng.NumberFormat = "@"
rng = excelApp.Range("S2:S" & lastrow) rng.Select() rng.NumberFormat = "mm/dd/yyyy:@"
Made a copy of the table and changed the DATETIME columns to be defines as DATE. None of the above statements worked either; still get "42221.00" in Excel. Since this will be part of a daily batch process, no human will open the spreadsheet, highlight the columns, and manually format them as Date.
Still using Google to try to find more options, but if anybody has any thoughts, I'd like to hear them.
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,887
8/7/15 11:56:05 AM
|
Does the case matter?
|
Post #403,888
8/7/15 1:23:04 PM
|
just for grins
I made a copy of the table, changing the two date columns to be NVARCAR (10). Loaded the copy with test data and reran the program.
No effect.
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,895
8/7/15 4:38:37 PM
|
This worked, but it takes longer
and I don't know how large the amount of data returned from the SQL table will be. But at least it looks the way they want it:
Dim intRowsOfData As Integer = dt.Rows.Count Dim j As Integer Dim myDate As Date
'--- Due Date column For j = 2 To intRowsOfData + 1 myDate = CDate(excelSheet.Cells(j, 19).Value) excelSheet.Cells(j, 19).NumberFormat = "@" excelSheet.Cells(j, 19).Value = FormatCellDisplayDate(myDate) Next
'--- Run Date column For j = 2 To intRowsOfData + 1 myDate = CDate(excelSheet.Cells(j, 4).Value) excelSheet.Cells(j, 4).NumberFormat = "@" excelSheet.Cells(j, 4).Value = FormatCellDisplayDate(myDate) Next
Private Function FormatCellDisplayDate(ByVal inDate As Date) As String
Dim strBuf As String Dim iMonth As Int16 Dim iDay As Int16 Dim iYear As Int16
iMonth = inDate.Month iDay = inDate.Day iYear = inDate.Year
If iMonth Less Than 10 Then strBuf = "0" & iMonth.ToString Else strBuf = iMonth.ToString End If strBuf &= "/" If iDay Less Than 10 Then strBuf &= "0" & iDay.ToString Else strBuf &= iDay.ToString End If strBuf &= "/" strBuf &= iYear.ToString
Return strBuf
End Function
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,902
8/7/15 10:02:14 PM
|
How do you export/import?
Is this coming through SSIS?
That smells like the import resulted in a text cell, not a numeric/variant value. i.e. like you get when typing a leading ' in a cell.
|
Post #403,911
8/8/15 10:47:55 PM
|
The batch process has a program that uses a SQL Bulk Insert
command that will fill the table before my program runs; I have no control over that happening.
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,946
8/11/15 1:26:11 PM
|
Well, they won't let me use a varchar
definition for the RUN_DATE column; has to be a DATETIME (it only needs to be a DATE, but corporate policy...) So when it gets into the spreadsheet, Excel stores it as a number. Here's my work-around:
'--- Run Date column For j = 2 To intRowsOfData + 1 If TypeOf excelSheet.Cells(j, 4).Value Is System.Double Then myDate = DateAdd(DateInterval.Day, Convert.ToDouble(excelSheet.Cells(j, 4).Value), StartDate)
' for some weird unknown reason, the previous statement produces a date ' 2 days greater than the actual date value myDate = DateAdd(DateInterval.Day, -2, myDate)
excelSheet.Cells(j, 4).NumberFormat = "@" excelSheet.Cells(j, 4).Value = FormatCellDisplayDate(myDate) Else excelSheet.Cells(j, 4).Value = FormatCellDisplayDate(Today) End If Next
Private Function FormatCellDisplayDate(ByVal inDate As Date) As String
Dim strBuf As String
strBuf = Format(inDate, "MM/dd/yyyy") Return strBuf
End Function
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,955
8/11/15 4:05:01 PM
|
The only way I can reproduce is if the cell contains a string
Caveat: this is straight VBA in Excel, not VB .Net as I don't have VS .Net installed (So this may be some kind of .Net hell?) e.g. Sheet1.Cells(3, 1) = "42227.25" Sheet1.Cells(3, 2) = TypeName(Sheet1.Cells(3, 1).Value) ' Says "double" Sheet1.Cells(3, 1).NumberFormat = "mm/dd/yyyy" Sheet1.Cells(3, 3) = TypeName(Sheet1.Cells(3, 1).Value) ' Says "date"
works as expected: the value displays as a date (08/11/2015). The quotes surrounding 42227.25 can be left off without impact. Same thing when run on a CSV import with the date naked or enclosed in double quotes, expressed as a mm/dd/yyyy or double. However, Sheet1.Cells(3, 1) = "'42227.25" Sheet1.Cells(3, 2) = TypeName(Sheet1.Cells(3, 1).Value) ' Says "string" Sheet1.Cells(3, 1).NumberFormat = "mm/dd/yyyy" ' No effect Sheet1.Cells(3, 3) = TypeName(Sheet1.Cells(3, 1).Value) ' Says "string"
behaves as you describe. (Note the ' in the first statement.) These values are given away by a marker in the cell's upper left corner. I can get around it with Sheet1.Cells(3, 1) = "'42227.25" Sheet1.Cells(3, 1) = CDbl(Sheet1.Cells(3, 1).Value) Sheet1.Cells(3, 1).NumberFormat = "mm/dd/yyyy"
Excel internally stores dates as a floating point number so importing as a double is fine as long as both parties agree on the encoding of the date and time parts (which they do in this case.) myDate = DateAdd(DateInterval.Day, Convert.ToDouble(excelSheet.Cells(j, 4).Value), StartDate)
' for some weird unknown reason, the previous statement produces a date ' 2 days greater than the actual date value
What is in StartDate? The Convert.ToDouble converts a double to double, so should not even be needed (unless something in the preceding If is lying to you.)
|
Post #403,969
8/11/15 8:15:33 PM
|
StartDate = "01/01/1900"
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #403,975
8/11/15 10:31:44 PM
8/11/15 10:35:00 PM
|
The epoch start date is 1900-jan-00
No clue what will happen if you feed it in like that, though. (I left Excel behind at work ;-)
Edit: disambiguate month & day
Edited by scoenye
Aug. 11, 2015, 10:35:00 PM EDT
|
Post #403,991
8/12/15 1:28:48 PM
|
That's why I used 1/1/1900
because I was getting numbers like "42221.00" in Excel. Subtracting that many days from today put it around the 3rd or 4th of January, 1900. Knocking off the 2 additional days made all of the dates in the spreadsheet match the dates in the SQL table.
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|