Convert DateTime format in epplus

Question

I want to read a column from excel into datatable using epplus but whenever i do I get the time in 24 hour format. I want the time to be in 12 hour format as it is in excel.

I have tried changing the date format of excel to "dd/mm/yyyy hh:mm:ss tt" it give me AM/PM but it shows the time in 24 hour format.

worksheet.Cells["" + firstRowCell.Address + ":" + 
Regex.Replace(firstRowCell.Address, "[0-9]", "") + "" + 
worksheet.Dimension.Rows].Style.Numberformat.Format =  "dd/mm/yyyy hh:mm:ss tt";

Answer 1

It sounds like you are reading formatted display value and not the underlying cell value. Maybe this will help you I am not sure Excel saves the date and time as a double of the number of days from 1900-01-01 the decimal part is how it determines hours in the day. This number could also be negative or positive so what I have done in the past to get the correct value is to read the cell value and convert that to a DateTime.

//get the underlying value instead of the display value
var cellValue = worksheet.Cells["" + firstRowCell.Address + ":" + 
Regex.Replace(firstRowCell.Address, "[0-9]", "") + "" + 
worksheet.Dimension.Rows].Value;

var stringValue = $"{cellValue}";

//Now convert the double to a valid DateTime;
DateTime value;
var date = new DateTime(1899, 12, 30);
double doubleValue;

if (double.TryParse(stringValue, out doubleValue) &&
   (doubleValue <= 2958465) &&
   (doubleValue >= -693593))
      value = date.AddDays(doubleValue);

Once you have a valid DateTime value you can use ToString to get a proper 12 hour format

value.ToString("dd/mm/yyyy hh:mm:ss tt")