Converting number to date produces different results for c# and Excel

Question

We have started receiving csv (text) files with dates being represent as integers. I opened up the file in Excel and formatted the number as a date. (Highlight column, right-click, select format cell, etc....)

In a C# .NET 6.0 console program, I wanted to verify/duplicate the Excel date using the following code:

    DateOnly d1 = new DateOnly(1900, 1,1);
    DateOnly d2 = d1.AddDays(45108); //45114, 45126, 45108
    Console.WriteLine(d2.ToString());

My C# program gives a date that is 2 days later than Excel.

int	Excel	C#
45114	7/7/2023	7/9/2023
45126	7/19/2023	7/21/2023
45108	7/1/2023	7/3/2023

Which is correct: C# or Excel? I need to code the conversion in c# for internal use. I would like to understand the discrepancy.

DateTime.FromOADate should give you the same as Excel. Excel and Net use different startdates to count days from. — Ralf, Aug 01 '23 at 20:02
David and Ralf, thank you both. FromOADate will work, and the article link helped explain the discrepancy. — BarnumBailey, Aug 02 '23 at 14:08

score 0 · Answer 1 · answered Aug 01 '23 at 20:20

Found this article about Excel:

https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system

So I tried the following:

using System;
                    
public class Program
{
    public static void Main()
    {

        DateOnly base_date;
        
        // https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
        
        // int      Excel       C#
        // 45114    7/7/2023    7/9/2023
        // 45126    7/19/2023   7/21/2023
        // 45108    7/1/2023    7/3/2023
        
        base_date = new DateOnly(1900, 1,1);

        Console.WriteLine( $"Base Date: {base_date}" );
        
        show_date( base_date, 45114 );
        show_date( base_date, 45126 );
        show_date( base_date, 45108 );
        
        base_date = new DateOnly(1904, 1,1);

        Console.WriteLine( $"Base Date: {base_date}" );
        
        show_date( base_date, 45114 );
        show_date( base_date, 45126 );
        show_date( base_date, 45108 );
        
        
    }
    
    public static void show_date( DateOnly base_date, int days )
    {

        DateOnly comp_date = base_date.AddDays( days );
        
        Console.WriteLine( $"- Days: {days}, computed date: {comp_date}" );
    
    }
    
}

Output:

Base Date: 01/01/1900
- Days: 45114, computed date: 07/09/2023
- Days: 45126, computed date: 07/21/2023
- Days: 45108, computed date: 07/03/2023
Base Date: 01/01/1904
- Days: 45114, computed date: 07/08/2027
- Days: 45126, computed date: 07/20/2027
- Days: 45108, computed date: 07/02/2027

The output still seems off from what you posted though.

Converting number to date produces different results for c# and Excel

1 Answers1