IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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
New Does the case matter?
In some of the lines you have

.select()

and in some you have

.Select()

Does it matter?

If not, maybe this thread will help - http://stackoverflow.com/questions/19801598/excel-vba-date-formats

Adding some error checking might tell you what's going on.

Good luck.

Cheers,
Scott.
New 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
New 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
New 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.
New 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
New 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
New 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.)
New 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
New 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
Expand Edited by scoenye Aug. 11, 2015, 10:35:00 PM EDT
New 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
     Trying to format a Excel column as Date from VB.Net - (lincoln) - (10)
         Does the case matter? - (Another Scott) - (1)
             just for grins - (lincoln)
         This worked, but it takes longer - (lincoln)
         How do you export/import? - (scoenye) - (1)
             The batch process has a program that uses a SQL Bulk Insert - (lincoln)
         Well, they won't let me use a varchar - (lincoln) - (4)
             The only way I can reproduce is if the cell contains a string - (scoenye) - (3)
                 StartDate = "01/01/1900" -NT - (lincoln) - (2)
                     The epoch start date is 1900-jan-00 - (scoenye) - (1)
                         That's why I used 1/1/1900 - (lincoln)

I just KNEW the Good Humor Man was behind this...
53 ms