Solved

DateDiff different than MS Excel

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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