Solved

DateDiff different than MS Excel

Posted on 2006-11-16
2
264 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

615 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