August 31, 2010

Finding Date Difference in Excel and by using Macro

Date Difference in Excel

We can use Days360 function to find the days difference

=days360(startdate, enddate, true)


A1 B1 C1
3/20/2010 3/26/2010

Type the any one of the following in C1 

To find the days difference, use

To find months difference, use Month(B1)-Month(A1)

To find years difference, use Year(B1)-Year(A1)

Then the sheet A1 and B1 contains the following values with the Date format
A1 B1 C1
3/20/2010 3/26/2010 6

Note: It will return integer value so don't format the resultant(C1) cell to Date.

Date difference by using macro

We can use the datediff function to find the days, months, years difference between two dates

Cell.Value = datediff(interval, Range("startdate_cell").Value, Range("enddate_cell).Value)

interval can be any one of the following
d - days, m - months, y - years, ym - months excluding years, yd - days excluding years, md - days excluding years and months

Range("C1").Value = datediff("d", Range("A1").Value, Range("B1").Value)


Excel Macros and Vba reference links


While Loop Reference

Date and Time Reference

Selecting cells/ranges

Vba for excel

User Defined Functions

Strings Reference

Using Ranges

Using Sheets

Searching and auto filing using macros in excel with in sheets


Sheet1 contains Employee details as follows
(Employee Name, Project, Skills, Role, Start Date, End Date)

Sheet2 contains the new modules/projects which need some more man power with these details
(Project, Skills, Role, Start Date, No. of Persons Required, No. of Persons available, Gap, Names Available)

Here is the Sheet1 in excel

Employee name
Start Date
End Date


Sheet2 contains as follows

Project Name Skills Role Start Date No. Of Persons Required No. Of Persons Exist Gap Names Available
P5 S1 R2 16-Oct-10 5 3 2  
P1 S2 R1 2-Feb-10 6 6 0  
P6 S1 R1 1-Jan-11 2 1 1  
P3 S2 R2 15-Mar-10 4 3 1  
P7 S2 R3 21-Aug-10 5 3 2  
P6 S2 R2 1-Jan-11 7 3 4  

Now, we can write a macro which will fill the names available column with the specified skills and roles

Macro Code:

'Macro Code Starts Here

Sub Find_Gap()
    Dim names As String
    Dim startDate As Date
    Dim skillsVal As String
    Dim roleVal As String
    Dim currentCell As Range
    'Searching or Finding Value should be in Sheet2
    'Gap column should be in 'G' Column
    Set currentCell = Range("G2")
    names = ""
    'MsgBox (currentCell)
        names = ""
        gapVal = ActiveCell.Value
    If gapVal > 0 Then
        'MsgBox (gapVal)
        'startdate column should be in 'D' Column i.e D-G=-3
        startDate = ActiveCell.Offset(0, -3).Value
        'Role column should be in 'C' Column i.e D-G=-4
        roleVal = ActiveCell.Offset(0, -4).Value
        'Skills column should be in 'B' Column i.e C-G=-5
        skillsVal = ActiveCell.Offset(0, -5).Value
        'MsgBox (startDate & skillsVal & roleVal)
        names = SearchPeople(startDate, skillsVal, roleVal)
    End If
    'MsgBox (names)
    If names <> "" Then
        currentCell.Offset(0, 1).Value = names
        currentCell.Offset(0, 1).Value = "-NA-"
    End If
    'Name Available Column should be next to Gap 'G' Column
    Set currentCell = currentCell.Offset(1, 0)
    'Gap column Should be in 'G' Column
    Loop Until IsEmpty(ActiveCell.Offset(0, -6).Value)
End Sub

Function SearchPeople(startDate As Date, skillsVal As String, roleVal As String) As String
    'Employee Details should be in Sheet1
    Dim names As String
    names = ""
    'Employee Name should be in 'A' Column
    'StartDate should be in 'E' Column and EndDate Should be in 'F' Column
        If ActiveCell.Offset(0, 4).Value <> Null Or ActiveCell.Offset(0, 5).Value < startDate Then
            'Skills column should be in 'C' Column and Roles in 'D' Column
            If ActiveCell.Offset(0, 2).Value = skillsVal And ActiveCell.Offset(0, 3).Value = roleVal Then
                If names <> "" Then
                    names = names & "," & ActiveCell.Value
                    names = ActiveCell.Value
                End If
            End If
        End If
        'MsgBox (names)
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Value)
    SearchPeople = names
End Function

'Macro Code Ends Here

When you run the macro, the Names Available column will be filled as follows
Project Name Skills Role Start Date No. Of Persons Required No. Of Persons Exist Gap Names Available
P5 S1 R2 16-Oct-10 5 3 2 -NA-
P1 S2 R1 2-Feb-10 6 6 0 -NA-
P6 S1 R1 1-Jan-11 2 1 1 Abc
P3 S2 R2 15-Mar-10 4 3 1 -NA-
P7 S2 R3 21-Aug-10 5 3 2 Ghi
P6 S2 R2 1-Jan-11 7 3 4 Def,PSR

You can edit the macro according to your requirements.

August 27, 2010

Formatting table automatically in excel using macro and/or Visualbasic Editor

How to use macros in MicroSoft Excel 2007?

Follow these steps
  1. We need to enable Developer Toolbar with the following steps
    • Click the Office Button
    • Click Excel Options
    • Click Popular, select "Show Developer tab in the Ribbon" Checkbox
    Note: For reference you can use this link  
  2. Edit Macro settings in Excel by doing the following steps
    • On the Developer tab, in the Code group, click Macro Security
      Note: If the Developer tab is not displayed, do step 1
    • In the Macro Settings category, under Macro Settings, click the option Disable all macros with Notification 
    Note: For reference you can use this link 
  3. Save the excel workbook
  4. Select Developer Ribbon Tab and Click on Visual Basic
  5. Microsoft Visual Basic Editor will be opened
  6. Click on Insert menu and then click on Module
  7. Copy and paste the following code
  8. Enter your data as a table format
  9. Select Developer Ribbon Tab and Click on Macros
  10. Now you can see one macro with the name Table_xls_Format
  11. Click on Run
  12. See the data now. It will be in table format with proper header and data alignment
  13. You can change the macro code by clicking edit button in macro window.
Macro Code:

Sub Table_Xls_Format()

    'Selecting Header Row
    Dim header_row As Range
    Set header_row = Range("a1", Range("a1").End(xlToRight))
    'Heading Bold, Aqua Color
    header_row.Font.Bold = True
    header_row.HorizontalAlignment = xlCenter
    header_row.Font.ColorIndex = 42
    header_row.Font.Italic = False
    header_row.Font.Underline = False
    'Selecting data

    Dim total_data As Range
    Set total_data = Range("A1").CurrentRegion

    total_data.Font.Bold = False
    total_data.Font.Italic = False
    total_data.Font.Underline = False
    'Font:Couriernew , 10
    total_data.Font.Name = "Courier New"
    total_data.Font.Size = 10
    'Full Border
    total_data.Borders.LineStyle = 1
    'Autofit Column Width
    'Autosave the workbook
    ActiveWorkbook.Saved = True

End Sub

August 26, 2010

PL/SQL Developer Oracle Database Connectivity to access

To access our local Oracle in PL/SQL Developer, we need to add an entry in TNSNAMES.ORA file.

You can find TNSNAMES.ORA file in oracle installed folder.

 Example path:


Add and entry in the file as follows



    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))





 If you are facing any problems, just check the following tnsping in command prompt.


  • Ping your ip address as in the following example.

C:\Documents and Settings\test>tnsping


TNS Ping Utility for 32-bit Windows: Version - Production on 26-AUG-2

010 09:27:37

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used HOSTNAME adapter to resolve the alias


OK (30 msec)

  • Open PL/SQL Developer and login with your username, password and give database as HOST_XE which you specified in the TNSNAMES.ORA file.

If any problem, please let me know.

August 25, 2010

Running job in regular intervals or daily in Linux to delete files or modifying files

Running scripts daily to perform some common tasks in Linux
Be in root mode, super user mode
1) Create a script with the name or any other name whatever you like
2) Do any one of the following .. but please test it with some other file extension like .yourname or something .. create your own unique files and do the testing before executing on original files which may be important.
   find /folderpath -type f -iname "*.fileextension" | xargs -i '{}' rm -rf {}
   find /folderpath -type -f -iname "*.fileextension"  -exec /bin/rm -rf {} \;
3) chmod +x
4) crontab -e
    i) press i to go into insert mode
    ii) enter 30 20 * * 1-5 /bin/bash /fullpath        
                      //30 min 20 hour i.e 8:30pm on 1-5(mon-fri) on *(every month) on *(every year)
    iii) shift+ZZ to save the file
5) Try this and let me know if any modifications needed

For further details, contact me

SQL query : Search sub string in column value string with delimeter

Input: String with proper delimeter as table column value
    ex:- /usr/tmp, /usr/tmp/psr, /usr/psr/dir1, /usr/psr/psr/dir1, /usr/lib
Output: substring between delimeter(,)
    1) This is SQL Query not PL/SQL program, its just a query
    2) User can search for only one string at a time
    3) User can change the search occurance i.e. need first occurance of the substring or nth occurance

SQL Query:

select substr(
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1),
        (instr(value1, ',', (instr(concat(',',concat(value1,',')),'psr',1,2)),1)+1)-
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1)
as result
from check1

 Detailed Explanation:

1. Modify original string in this ",string," format
2. Search for the substring and find index of previous delimeter of the substring
3. Find the length of the substring

select substr(                        // value1 is column name
        // 1.Concatenating delimeter(,) before and after string

        // 2. Finding substring starting index
        (instr(concat(',',concat(value1,',')), ','
            , (instr(concat(',',concat(value1,',')),'psr',1,2)   
// Finding substring 'psr' 2nd occurance, user can change value 2 from (1 to n)th occurance
            - length(concat(',',concat(value1,','))))           
// Searching in reverse direction to find previous delimeter(,) index from substring
            ,1)                                    // Delimeter(,) index will be our starting of substring
            +1)                                    // Not including delimeter(,) in substring display

         // 3. Finding length of the substring
         // Finding next delimeter(,) index from substring
         ,(instr(value1, ',', (instr(concat(',',concat(value1,',')),'psr',1,2)) ,1)+1)-
        // Finding length between two delimeters(,) i.e. length of substring
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1)
as result                            // Displaying column as result
from check1                            // Table name check1

Featured Post

Java Introdcution

Please send your review and feedback to