Solved

DateDiff different than MS Excel

Posted on 2006-11-16
2
263 Views
Last Modified: 2012-06-27
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
Comment
Question by:techhound
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 17958734
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 17958778
A link to Excel DAYS360 function from Microsoft.

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

Fernando
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question