Solved

DateDiff different than MS Excel

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

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 62

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now