• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

DateDiff different than MS Excel

I am using the following routine:

Microsoft.VisualBasic.DateAndTime.DateDiff(Interval, startDate, endDate, Microsoft.VisualBasic.FirstDayOfWeek.System, Microsoft.VisualBasic.FirstWeekOfYear.System);

This gives me the difference in dates. I use the following dates:

When I use the function with startDate = 11/09/2006 and endDate = 2/09/2007 the result is 92 days.
When I use the function with startDate = 11/16/2006 and endDate = 2/09/2007 the result is 84 days.

However, in MS-Excel, when I take the difference between the sets of dates for the first one the difference is 92 days.
for the second one the difference is 84 days.  I tried simply subtracting the dates in C# as well and the result is still diffferent for the second set.

Thanks
0
James Cochrane
Asked:
James Cochrane
  • 2
1 Solution
 
Fernando SotoRetiredCommented:
Hi techhound;

I just tried it and these are my results.

C# code that I used.

            DateTime startDate;
            DateTime endDate;
            DateTime.TryParse("11/09/2006", out startDate);
            DateTime.TryParse("2/09/2007", out endDate);

            long days1 = DateAndTime.DateDiff(DateInterval.Day, startDate,
                endDate, FirstDayOfWeek.System, FirstWeekOfYear.System);

            DateTime.TryParse("11/16/2006", out startDate);
            DateTime.TryParse("2/09/2007", out endDate);

            long days2 = DateAndTime.DateDiff(DateInterval.Day, startDate,
                endDate, FirstDayOfWeek.System, FirstWeekOfYear.System);

            MessageBox.Show(days1.ToString() + "\n" + days2.ToString());

Message box displayed:
        days1 = 92
        days2 = 85

Using Excel 2003 using the Formula =DAYS360(C8,D8)

        11/9/2006   2/9/2007  = 90 days
        11/16/2006 2/9/2007  = 83

The reason for the difference is the following:

In Excel they return the number of days between two dates based on 360 days in the year. That is 12 months x 30 days per month.

In Visual C# the number of days are calculated and therefore accurate.

Fernando
0
 
Fernando SotoRetiredCommented:
A link to Excel DAYS360 function from Microsoft.

     http://office.microsoft.com/en-us/excel/HP052090471033.aspx

Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now