C# Julian Date Parser

Question

I have a cell in a spreadsheet that is a date object in Excel but becomes a double (something like 39820.0 for 1/7/2009) when it comes out of C1's xls class. I read this is a Julian date format. Can someone tell me how to parse it back into a DateTime in C#?

Update: It looks like I might not have a Julian date, but instead the number of days since Dec 30, 1899.

Answer 1

Check out this post: http://dotnet-jeeves.blogspot.com/2006/02/convert-julian-date-to-systemdatetime.html

Answer 2

That number looks like a 'number of days since 1900' value.

Answer 3

There's a JulianCalendar class in System.Globalization; Here's how you would use it:

            JulianCalendar c = new JulianCalendar();
            DateTime time = c.ToDateTime(2009, 1, 7, 0, 0, 0, 0);
            Console.WriteLine(time.ToShortDateString());

EDIT:

If it is in fact days since "1900" here's how you can do it:

public static DateTime DaysSince1900(int days)
{
    return new DateTime(1900, 1, 1).AddDays(days);
}



 DateTime time = DaysSince1900(39820);
 Console.WriteLine(time.ToShortDateString()); //will result in "1/9/2009"

Answer 4

I think Excel is just using the standard OLE Automation DATE type which can be converted with the DateTime.FromOADate method.

This block of code,

using System;

namespace DateFromDouble
{
    class Program
    {
     static void Main(string[] args)
     {
      Console.WriteLine(DateTime.FromOADate(39820.0));
     }
    }
}

outputs:

1/7/2009 12:00:00 AM

Answer 5

Here ya go.

http://dotnetblogger.com/post/2009/02/26/Convert-DateTime-to-Julian-Date-in-C.aspx

Answer 6

When dealing with Excel dates, the date may be the string representation of a date, or it may be an OA date. This is an extension method I wrote a while back to help facilitate the date conversion:

/// <summary>
/// Sometimes the date from Excel is a string, other times it is an OA Date:
/// Excel stores date values as a Double representing the number of days from January 1, 1900.
/// Need to use the FromOADate method which takes a Double and converts to a Date.
/// OA = OLE Automation compatible.
/// </summary>
/// <param name="date">a string to parse into a date</param>
/// <returns>a DateTime value; if the string could not be parsed, returns DateTime.MinValue</returns>
public static DateTime ParseExcelDate( this string date )
{
    DateTime dt;
    if( DateTime.TryParse( date, out dt ) )
    {
        return dt;
    }

    double oaDate;
    if( double.TryParse( date, out oaDate ) )
    {
        return DateTime.FromOADate( oaDate );
    }

    return DateTime.MinValue;
}