How To Convert Excel Date Value to .Net DateTime?

[Gregg Walker]

I'm using excel automation in a c# project.

decimal testDate = Convert.ToDateTime(((Excel.Range)xlWorksheet.Cells[r, (int)SheetColumn.TestDate]).Value2);

The above code fails because the cell value is of type double. It appears the value is an integer value that specifies the number
of days since some epoch date.

How can I convert this double value into a DateTime type?

Thanks,
Gregg Walker

 

[Peter Atherton]

Greg

Excel uses 1/1/1900 as 1 but macs use a 1904 date. Time is
a decimal. 12 Hours are a fraction of a day. I have no
idea how you work in Cx but in VB we just format the
number. See the example below where c is a double.

Sub DateTime()
Dim c As Variant
For Each c In Selection
c.NumberFormat = "dd/mm/yyyy hh:mm;ss"
Next c
End Sub

Regards
Peter

-----Original Message-----
I'm using excel automation in a c# project.

decimal testDate = Convert.ToDateTime(((Excel.Range)

xlWorksheet.Cells[r, (int)SheetColumn.TestDate]).Value2);

The above code fails because the cell value is of type

double. It appears the value is an integer value that
specifies the number

 

[Nick Cranham]

Gregg,
VB/VBA use doubles for dates, it's just a case of formatting the double to
look like a date/time depending on the style chosen.
The integer portion of the double indicates the number of days (since
1/1/1900), whilst the decimal portion indicates time as the fraction of a
day since midnight.

So you would have supply the correct parameter type to Convert.ToDateTime,
whatever it is expecting.

NickHK

Greg

Excel uses 1/1/1900 as 1 but macs use a 1904 date. Time is
a decimal. 12 Hours are a fraction of a day. I have no
idea how you work in Cx but in VB we just format the
number. See the example below where c is a double.

Sub DateTime()
Dim c As Variant
For Each c In Selection
c.NumberFormat = "dd/mm/yyyy hh:mm;ss"
Next c
End Sub

Regards
Peter

-----Original Message-----
I'm using excel automation in a c# project.

decimal testDate = Convert.ToDateTime(((Excel.Range)

xlWorksheet.Cells[r, (int)SheetColumn.TestDate]).Value2);

The above code fails because the cell value is of type

double. It appears the value is an integer value that
specifies the number

of days since some epoch date.

How can I convert this double value into a DateTime type?

Thanks,
Gregg Walker


.

 

[Gregg Walker]

Hi Peter,

I don't need to format the date in the spreadsheet. It already has a number
format "yyyymmdd". What I am trying to do is get the date value in the
cell into a .Net DateTime variable.

I was able to solve by getting the Text property of the cell instead of the
Value2 property.

Here's the code I found to work.

string testDate = Convert.ToString(((Excel.Range)xlWorksheet.Cells[r,
(int)SheetColumn.TestDate]).Text);

DateTime testTime = new DateTime(Convert.ToInt32(testDate.Substring(0,
4)), Convert.ToInt32(testDate.Substring(4, 2)),
Convert.ToInt32(testDate.Substring(6, 2)), 0, 0, 0);

Thanks for your response. You got me thinking in the right direction.

Cheers,
Gregg Walker

Greg

Excel uses 1/1/1900 as 1 but macs use a 1904 date. Time is
a decimal. 12 Hours are a fraction of a day. I have no
idea how you work in Cx but in VB we just format the
number. See the example below where c is a double.

Sub DateTime()
Dim c As Variant
For Each c In Selection
c.NumberFormat = "dd/mm/yyyy hh:mm;ss"
Next c
End Sub

Regards
Peter

-----Original Message-----
I'm using excel automation in a c# project.

decimal testDate = Convert.ToDateTime(((Excel.Range)

xlWorksheet.Cells[r, (int)SheetColumn.TestDate]).Value2);

The above code fails because the cell value is of type

double. It appears the value is an integer value that
specifies the number

of days since some epoch date.

How can I convert this double value into a DateTime type?

Thanks,
Gregg Walker


.

 

[Wei-Dong Xu [MSFT]]

Hi Gregg,

Thanks for posting in the community!

From my understanding to this issue, you are going to obtain the date time
from the Excel and then convert it into one .Net DateTime structure.

I agree with Brad on the days returned from the Value2 property. The value
will be the days from 1900/1/1. You can use the AddDays method of DateTime
to add the days to calculate the correct date. Please note we should minus
2 days for the calculation; if not, the result date will be a later date. I
write one sample code for you
//Code begin ---------------------------------------------------------
...
((oExcel.Range)oSht.Cells[(object)1, (object)1]).Value2 = "12/25/1998";
...
double days = (double)((oExcel.Range)oSht.Cells[(object)1,
(object)1]).Value2;
DateTime DT = new DateTime(1900, 1, 1);
DT = DT.AddDays(Convert.ToInt32(days)-2);
Debug.WriteLine(DT.ToString());
...
//Code end ---------------------------------------------------------

However, if your date format is "dd/mm/yyyy HH:MM:SS", you shouldn't use
Convert.ToInt32() method. Because the afternoon time such as "12/25/1998
14:00:00" will be convert to the Int32 type 36155, the morning time
"12/25/1998 10:00:00" will be converted to the Int32 type 36154, then for
the afternoon time, the last date will be a later date, not correct. For
this scenario, you should use explicit convention to convert the days
count. I create one sample code for you on this as well.
//Code2 begin ---------------------------------------------------------
...
oSht.get_Range("A1", "A1").Value2 = "12/25/1998";
oSht.get_Range("A2", "A2").Value2 = "12/25/1998 10:00:00";
oSht.get_Range("A3", "A3").Value2 = "12/25/1998 14:00:00";
oSht.get_Range("A4", "A4").Value2 = "12/25/1998 15:00:00";
double days = (double)oSht.get_Range("A1", "A1").Value2;
double days2 = (double)oSht.get_Range("A2", "A2").Value2;
double days3 = (double)oSht.get_Range("A3", "A3").Value2;
double days4 = (double)oSht.get_Range("A4", "A4").Value2;

DateTime DT = new DateTime(1900, 1, 1);
DT = DT.AddDays(Convert.ToInt32(days)-2);
Debug.WriteLine(DT.ToString());

DateTime DT2 = new DateTime(1900, 1, 1);
DT2 = DT2.AddDays(Convert.ToInt32(days2)-2);
Debug.WriteLine(DT2.ToString());

DateTime DT3 = new DateTime(1900, 1, 1);
DT3 = DT3.AddDays(Convert.ToInt32(days3)-2);
Debug.WriteLine(DT3.ToString() + " wrong date which is later by one day!");

DateTime DT4 = new DateTime(1900, 1, 1);
//explicit convertion
Int32 iCorrectDate = (int)days4 - 2;
DT4 = DT4.AddDays(iCorrectDate);
Debug.WriteLine(DT4.ToString());
...
//Code2 end ---------------------------------------------------------

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Enjoy a nice day!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Gregg Walker]

Thanks to all. Great help!

Gregg Walker

Hi Gregg,

Thanks for posting in the community!

From my understanding to this issue, you are going to obtain the date time
from the Excel and then convert it into one .Net DateTime structure.

I agree with Brad on the days returned from the Value2 property. The value
will be the days from 1900/1/1. You can use the AddDays method of DateTime
to add the days to calculate the correct date. Please note we should minus
2 days for the calculation; if not, the result date will be a later date. I
write one sample code for you
//Code begin ---------------------------------------------------------
..
((oExcel.Range)oSht.Cells[(object)1, (object)1]).Value2 = "12/25/1998";
..
double days = (double)((oExcel.Range)oSht.Cells[(object)1,
(object)1]).Value2;
DateTime DT = new DateTime(1900, 1, 1);
DT = DT.AddDays(Convert.ToInt32(days)-2);
Debug.WriteLine(DT.ToString());
..
//Code end ---------------------------------------------------------

However, if your date format is "dd/mm/yyyy HH:MM:SS", you shouldn't use
Convert.ToInt32() method. Because the afternoon time such as "12/25/1998
14:00:00" will be convert to the Int32 type 36155, the morning time
"12/25/1998 10:00:00" will be converted to the Int32 type 36154, then for
the afternoon time, the last date will be a later date, not correct. For
this scenario, you should use explicit convention to convert the days
count. I create one sample code for you on this as well.
//Code2 begin ---------------------------------------------------------
..
oSht.get_Range("A1", "A1").Value2 = "12/25/1998";
oSht.get_Range("A2", "A2").Value2 = "12/25/1998 10:00:00";
oSht.get_Range("A3", "A3").Value2 = "12/25/1998 14:00:00";
oSht.get_Range("A4", "A4").Value2 = "12/25/1998 15:00:00";
double days = (double)oSht.get_Range("A1", "A1").Value2;
double days2 = (double)oSht.get_Range("A2", "A2").Value2;
double days3 = (double)oSht.get_Range("A3", "A3").Value2;
double days4 = (double)oSht.get_Range("A4", "A4").Value2;

DateTime DT = new DateTime(1900, 1, 1);
DT = DT.AddDays(Convert.ToInt32(days)-2);
Debug.WriteLine(DT.ToString());

DateTime DT2 = new DateTime(1900, 1, 1);
DT2 = DT2.AddDays(Convert.ToInt32(days2)-2);
Debug.WriteLine(DT2.ToString());

DateTime DT3 = new DateTime(1900, 1, 1);
DT3 = DT3.AddDays(Convert.ToInt32(days3)-2);
Debug.WriteLine(DT3.ToString() + " wrong date which is later by one day!");

DateTime DT4 = new DateTime(1900, 1, 1);
//explicit convertion
Int32 iCorrectDate = (int)days4 - 2;
DT4 = DT4.AddDays(iCorrectDate);
Debug.WriteLine(DT4.ToString());
..
//Code2 end ---------------------------------------------------------

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Enjoy a nice day!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.