This project has moved. For the latest updates, please go here.

TimeSpan

Dec 7, 2010 at 4:38 PM

Hello,

What format must I use to write a timespan format?

I tested hh:mm:ss, but when we try to add the rows value excel don't add them.

Best regards,

 

Coordinator
Dec 8, 2010 at 7:00 AM

Hi,

That should work. Make sure you add set value as DateTime and not as a string...

ws.Cells["A1"].Value = DateTime.Now;
ws.Cells["A1"].Style.Numberformat.Format = "hh:mm:ss";

Have a look at the section about styling in the FAQ.

Jan

Dec 8, 2010 at 4:01 PM

I'm setting the value as "TimeSpan" and excel shows fine the value. I think I didn't explain the problem correcly in my post.

When we write with timespan format ("hh:mm:ss"), excel shows fine the value (ex. 12:54:12). But if you put this values in a table and then you setup thecolumn to add all values as a total, excel don't calculate the total of the column. Then when you edit this cell and clic enter excel calculate the total.

Thanks for your help and best regards,

Coordinator
Dec 9, 2010 at 8:01 AM

Ok, I'm not sure I understand you correctly, since you haven't provided any code, but if you try this...

worksheet.Cells["A1"].Value=new TimeSpan(1, 1, 10);

...the value will be converted to a string. The library has no build in support for TimeSpann's, so it will call the ToString() mehod on the object (like all other objects as well).

What you should do is to convert it to a date or a double.

Try this...

worksheet.Cells["A1"] = new DateTime(new TimeSpan(1, 1, 10).Ticks);

...and the value should appear correctly.

Jan

Nov 30, 2012 at 7:36 PM
Edited Nov 30, 2012 at 7:37 PM

FYI since this came high on Google, I found all I had to do was assign the Value and then set the format

worksheet.Cells["A1"].Value=new TimeSpan(1, 1, 10);

worksheet.Cells["A1"].Style.Numberformat.Format = "h:mm AM/PM";// 3:45 PM, best when your TimeSpan represents a time of the day
or
worksheet.Cells["A1"].Style.Numberformat.Format = "h:mm";// 15:45, best when your TimeSpan represents an amount of time elapsed

The nice thing is this does not include any kind of date component that would create nonsense date parts in Excel.
...Never liked Microsoft's post editor...