How do I convert a string based time to hh:mm format in excel using EPPlus?

Question

I need my excel output to have a column with time in format HH:mm.

My code is the following:

ws.Cells[rowNumber, 11].Style.Numberformat.Format = "hh:mm";
string start = row.R_HourStart.ToString("HH:mm:ss");
ws.Cells[rowNumber, 11].Value = start;

But when I open the file in excel, the cell displays "10:10:00".

When I insert text in the formula bar and click enter, the cell text is changed to "10:10", which is what I want.

I've also tried using:

ws.Cells[rowNumber, 11].Style.Numberformat.Format = "hh:mm";
string start = row.R_HourStart.ToString("HH:mm:ss");
ws.Cells[rowNumber, 11].LoadFromText(start);

I get "10:10" displayed in the cell but the formula bar displays today's full date with the hour 10:10.

Additionally, I tried:

ws.Cells[rowNumber, 12].Style.Numberformat.Format = "hh:mm";
ws.Cells[rowNumber, 12].Value = row.R_HourEnd;

Which shows "10:10" in the cell but the formula bar displays the whole date and time as "22/08/2014 10:10:00".

Answer 1

Can you convert your time to a TimeSpan? This worked for me:

var package = new ExcelPackage(new FileInfo("text.xlsx"));
var sheet = package.Workbook.Worksheets.Add("Sheet 1");

var someTime = "10:10:00";
var timeSpan = TimeSpan.Parse(someTime);
sheet.Cells[1, 1].Value = timeSpan;
sheet.Column(1).Style.Numberformat.Format = "hh:mm";

package.Save();

Answer 2

I had the same issue and it was because my DataTable was all strings. Formating worked fine after i've replaced the values with paresed ones.

for (int row = 1; row <= lastRow; row++)
    {
    DateTime dt;
    if (DateTime.TryParse(worksheet.Cells[row, colWithDates]?.Value?.ToString() ?? string.Empty, out dt))
    {
        worksheet.Cells[row, 1].Value = dt;
        worksheet.Cells[row, 1].Style.Numberformat.Format = "yyyy-mm-dd";
    }

    TimeSpan ts;
    if (TimeSpan.TryParse(worksheet.Cells[row, columnWithTimes]?.Value?.ToString() ?? string.Empty, out ts))
    {
        worksheet.Cells[row, 5].Value = ts;
        worksheet.Cells[row, 5].Style.Numberformat.Format = "hh:mm";
    }
}