formate dates in vb.net

  • Thread starter Thread starter pp
  • Start date Start date
P

pp

I have this kind of format dates "02-01-2001 16:00" in a excel
spreadsheet (dd-mm-aaaa h:mm).

In vb.net I have this code:
Dim tempo As string
tempo=oExcelWs.Cells(i, 1).Value

and I get this date: tempo = "01-02-2001 16:00", the code have switched
the month whit the day.

Can anyone help me?
Thanks
Paulo Praça
 
Are you sure that the month and day got switched?

If you format that cell as something unambiguous ("mmmm dd, yyyy" in English),
maybe you'll see that the .value is actually correct.
 
Dave Peterson wrote...
Are you sure that the month and day got switched?

If you format that cell as something unambiguous ("mmmm dd, yyyy" in English),
maybe you'll see that the .value is actually correct.
Click to expand...

In order to check this, I don't think using different text conversions
alone are the way to go. Better to use something like

Dim x As Variant, t As String
x = oExcelWs.Cells(i, 1).Value
t = oExcelWs.Cells(i, 1).Text
MsgBox Day(x) & " " & Month(x) & Chr(13) & t & Chr(13) & CStr(x) & _
Format(x, "dd-mm-yyyy hh:mm")
 
I don't understand why you think that formatting the cell in an unambiguous date
format wouldn't show the real value in the cell?

(I am assuming that the cell contains a real date/time--not text.)

And to the OP, maybe using .value2 would resolve the problem (or at least
isolate where the problem really was):

With ActiveCell
debug.print .Value & vbLf & .Value2
End With

I put a time/date in the activecell and saw this in the immediate window:

03/11/2005 15:35:00
38422.6493055556
(with my USA settings (mm/dd/yyyy))

That top value may be ambiguous (March 11 or November 3), but the bottom
(.value2) is the underlying value in the cell.
 
Dave Peterson said:
I don't understand why you think that formatting the cell in an unambiguous
date format wouldn't show the real value in the cell?

(I am assuming that the cell contains a real date/time--not text.)
Click to expand...
....

The OPs problem is complicated by the fact that he's assigning a cell's
value to a string. That means the cell value could hold something
unexpected, or the conversion to string (if the cell value is a date/time)
could be wrong. VBA seems to use system setting for default date/time
formats, but I don't know whether VB.Net does.

Whether the cell's value is a date/time number or a string representation of
a date/time, it can be assigned to a variant with no implicit conversion -
the VBA variable will have the same value the cell has. Passed either a date
variant or a string variant representing a date, VBA's Day and Month
functions will return day of month and month of year.
 
I missed the declaration as string portion. I don't think I'd do it that way
(from someone who knows nothing about vb.net).

Thanks for the clarification.
 
Back
Top