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.)

No clue what will happen if you feed it in like that, though. (I left Excel behind at work ;-)

Edit: disambiguate month & day