Well, they won't let me use a varchar

Post #403,946 by lincoln 8/11/15 1:26:11 PM Reply	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 by scoenye 8/11/15 4:05:01 PM Reply	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 by lincoln 8/11/15 8:15:33 PM Reply	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 by scoenye 8/11/15 10:31:44 PM 8/11/15 10:35:00 PM Reply	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 Expand All History
Post #403,991 by lincoln 8/12/15 1:28:48 PM Reply	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

Welcome to IWETHEY!