Search code examples
c#excelcsvdatetime

convert a C# datetime object into Excel date format

I need to convert a C# datetime object into the dreaded Excel date format: https://datapub.cdlib.org/2014/04/10/abandon-all-hope-ye-who-enter-dates-in-excel/

i.e. number of days since 1 Jan 1900 expressed as a floating point number.

Is there any way to do it without resorting to DIY code? I need it in order to create Excel-friendly CSV exports

Googling around I didn't find anything useful except that good blog post

Solution

  • Excel dates use the OLE Automation date format. You can retrieve it with DateTime.ToOADate

    OA Dates are a double whose integer part is the date offset from 30 December 1899 (ie earlier dates are negative) and fractional part is the time divided by 24.

    This type was used a lot in the COM/VB6 days. Nowadays it's needed for Excel and when you need to call COM APIs that expect dates or variants with a date content.