Excel - Calculate Working Days

I needed to calculate Number of Working dates between to dates. If I subtract two dates I get total number of days.. but I had no clue on how to remove regular weekend holidays from that.

I searched on the net and found a solution, however they do not explain me where to start with and how to implement it. I being a Java guy.. I dont know how to work with Functions and VBScripting. I had to search multiple sites to get the complete information and finally arrived at the solution.

For all those ppl out there who are like me.. and need this useful script.. Simply follow the below steps to calculate number of working days between 2 dates.

1. Open the Excel Document where you want this Formula created.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
   
3. On the Insert menu in the Microsoft Visual Basic window, click Module.
4. In the Module window, type the following code for the Date function...
   

   Public Function CountWorkingDays(ByVal varStartDate As Variant, ByVal varEndDate As Variant)
   Dim intCounter As Integer, intTotal As Integer
   Dim dtmCurrent As Date
   intTotal = 0
   
   If IsEmpty(varEndDate) Or IsEmpty(varStartDate) Then
   'Do Nothing
   Else
   
   For intCounter = 0 To (varEndDate - varStartDate)
   
   dtmCurrent = (varStartDate + intCounter)
   
   If (Weekday(dtmCurrent) = 1) Or (Weekday(dtmCurrent) = 7) Then
   'Do Nothing
   
   Else
   
   'Add the count
   intTotal = intTotal + 1
   
   End If
   
   Next intCounter
   
   End If
   
   CountWorkingDays = intTotal
   
   End Function
   

   
   
5. Save the Module and Close the Microsoft Visual Basic window.
   
6. Now in your Worksheet if you have 2 dates in 2 Cells, say D1 and F1 then your formula would be "=CountWorkingDays(D1, F1)"
   

Excel - Age Formula

Formula to calculate Age using a Date.

=INT((TODAY()-Date of Birth)/365.25)

DOS Date Formatting

One of my collegue was writing a DOS script that creates directories with date and archives files. The problem she faced is with the DATE command in DOS. DATE command gives date in mm/dd/yyyy format. If we use the same string for creating directory MKDIR command creates 3 directories with mm, dd and yyyy as nested directories.

C:\>echo %DATE%
Wed 03/26/2008

After some search we finally used the Substring methods in DOS to arrive with DATE format mm-dd-yyyy.

C:\>echo %date:~4,2%-%date:~7,2%-%date:~10,4%
03-26-2008

Explanation (%date:~4,2%)
% is used to enclose the expression
:~ is used to start the substring operation
4 is start index
2 is no of characters to extract

PC - Open Ports

Today I found the following command very very useful. I have a DB2 database running locally and I have no problem connecting to it using DB2 Control Center. But when I use Aquadata studio.. ADS requires the port number to connect to... which I dont know and DB2 Control Center does not tell.

So I needed a command to see what all ports my PC had opened. And the following command came to my rescue.

netstat -an | find /i "listening"