Solved

How to calculate NETWORKDAYS in C#

Posted on 2009-07-01
3
1,764 Views
Last Modified: 2012-05-07
I use this project mainly as a learning exercise for DataSet and LINQ.

The background is that I have a report in Excel html format that I read into a .NET 3.5 C# Windows Forms Application. I then display the data in a DataGridView and I also want to calculate some statistics on the data. To do so, I need to add a new column showing the elapsed number of working days between two dates.

In my DataSet, I have two columns, "Open Date" and "Close Date". In Excel I was able to use a formula like "=NETWORDAYS(B7:C7, Z2:Z50) to calculate the number of working days elapsed between the two dates.

Is there an easy way of achieving the same thing in my C# code, adding a new column to my DataTable?

The code needs a bit more polishing, eg. adding some error trapping etc but it shows what I have at the moment.
private void GetDataFromExcel(string fileName)

		{

			String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=HTML Import;";
 

			OleDbConnection dbConnection = new OleDbConnection(connectionString);

			dbConnection.Open();
 

			string select = @"SELECT * FROM [Table]";
 

			OleDbCommand selectCommand = new OleDbCommand(select, dbConnection);
 

			OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter();
 

			dbDataAdapter.SelectCommand = selectCommand;
 

			DataSet ds = new DataSet();
 

			dbDataAdapter.Fill(ds, "XLData");
 

			mainDataGridView.DataSource = ds.Tables[0];
 

			dbConnection.Close();
 

		}

Open in new window

0
Comment
Question by:Sharp2b
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 500 total points
ID: 24759034
There's nothing built-in, but here's a method that calculates workdays between two dates and allows you to pass in a list of excluded dates (e.g. holidays).
private int CountWorkDays( DateTime startDate, DateTime endDate, List<DateTime> excludedDates )

{

    int dayCount = 0;

    int inc = 1;

    bool endDateIsInPast = startDate > endDate;

    DateTime tmpDate = startDate;

    DateTime finiDate = endDate;
 

    if( endDateIsInPast )

    {

        // Swap dates around

        tmpDate = endDate;

        finiDate = startDate;
 

        // Set increment value to -1, so it DayCount decrements rather 

        // than increments

        inc = -1;

    }
 

    while( tmpDate <= finiDate )

    {

        if( !excludedDates.Contains( tmpDate ) )

        {

            dayCount += inc;

        }
 

        // Move onto next day

        tmpDate = tmpDate.AddDays( 1 );

    }
 

    return dayCount;

}

Open in new window

0
 

Author Comment

by:Sharp2b
ID: 24760883
What a great start of the day!

Thank you so much!

While getting ready to go to work this morning, I was thinking about how to implement a function like this as I figured there was no pre-built function. My thoughts was in line with this but one thing I was not immediately aware of was the Contains() method. I would probably have found it eventually but now you probably saved me at least 1/2 day of work. Since this is more of a "hobby" project, I can't spend too much time on it, at least not during the day. ;-) But now there is a good chance that I will be able to finish it off.
0
 

Author Closing Comment

by:Sharp2b
ID: 31598697
Exactly what I was looking for. Nice, short and easy to follow. It was also a good example to learn from as I still see myself as a beginner in this area. Hence it's also valuable to me for future projects.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

23 Experts available now in Live!

Get 1:1 Help Now