Excel datevalue

Question

How do I convert a date string 2019-06-20T00:00:00+01:00 to an Excel date? DATEVALUE returns #Value when I try this in Excel.

The date string comes from C# DateTime ToShortDateString()

Answer 1

Instead of ToShortDateString() use ToOADate().

Format the cell as Date and you should be all set.

Answer 2

In Excel you could just take everything to the left of the T character. If that date string was in cell A1 then:

=DATEVALUE(LEFT(A1,FIND("T",A1)-1))

Alternatively in VBA if the date string was stored in a variable called sFoo:

CDate(Left$(sFoo,Instr(sFoo,"T")-1))