Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 13. August 2010 12:37

Today is Friday the 13th, the day when more accidents happen because Paraskevidekatriaphobics are concerned about accidents. Or is it the day when less accidents take place, because people stay home to avoid accidents? Not altogether clear, it seems.

Whether safe or dangerous, how often do these Friday the 13th take place, exactly? Are there years without it, or with more than one? That’s a question which should have a clearer answer. Let’s try to figure out the probability to observe N such days in a year picked at random.

First, note that if you knew what the first day of that year was, you could easily verify if the 13th day for each month was indeed a Friday. Would that be sufficient? Not quite – you would also need to know whether the year was a leap year, these years which happen every 4 years and have an extra day, February the 29th.

OuroborosImagine that this year started a Monday. What would next year start with? If we are in a regular year, 365 days = 52 x 7 + 1; in other words, 52 weeks will elapse, the last day of the year will also be a Monday, and next year will start a Tuesday. If this is a leap year, next year will start on a Wednesday.

Why do I care? Because now we can show that every 28 years, the same cycle of Friday the 13th will take place again. Every four consecutive years, the start day shifts by 5 positions (3 “regular” years and one leap year), and because 5 and 7 have no common denominator, after 7 4-year periods, we will be back to starting an identical 28-years cycle, where each day of the week will appear 4 times as first day of the year.

More...

by Mathias 4. September 2008 18:19

 [Edit, Sept 5, 2008: nothing incorrect in the following post; however, if I had Google'd first, I would have found that DateTime date = DateTime.FromOADate(d), where d is a double, does exactly the job...]

The project I am currently working on requires reading some data from an Excel workbook into a .NET calculation engine written in C#. Most of my reads follow this pattern: read a named range into an array of objects, then convert the object to the appropriate .NET type.

public static object[,] GetRangeAsArray(Excel.Worksheet sheet, string rangeName)
{
    Excel.Range range = sheet.get_Range(rangeName, Missing.Value);
    object[,] rangeAsArray = range.Value2 as object[,];
    return rangeAsArray;
}

However, I ran into an issue reading dates. Excel stores dates as doubles, which encode the number of days elapsed since January 0, 1900 (Yes, January 0). As a result, the object stored in the array is a double, and the Convert.ToDateTime(double) method throws an InvalidCastExpression, so standard conversion doesn’t work.
If you look a bit deeper into it (here is a very comprehensive page on the topic), you will discover some interesting idiosyncrasies of the date encoding in Excel. For instance, back in the days, the Excel team knowingly implemented a bug to replicate a known bug of Lotus, for the sake of backwards compatibility.
Here is the quick method I wrote to perform that conversion, addressing these issues:

public static DateTime ConvertToDateTime(double excelDate)
{
    if (excelDate < 1)
    {
        throw new ArgumentException("Excel dates cannot be smaller than 0.");
    }
    DateTime dateOfReference = new DateTime(1900, 1, 1);
    if (excelDate > 60d)
    {
        excelDate = excelDate - 2;
    }
    else
    {
        excelDate = excelDate - 1;
    }
    return dateOfReference.AddDays(excelDate);
}

More...

Comments

Comment RSS