Amazon Great Offers

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

Syntax: 
=days360(startdate, enddate, true)

Example:

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

Type the any one of the following in C1 

To find the days difference, use
 =days360(A1,B1,True)
=B1-A1


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

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

Note:
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

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

References:
 http://office.microsoft.com/en-au/excel-help/calculate-the-difference-between-two-dates-HP010070467.aspx

http://www.cpearson.com/excel/datedif.aspx

Excel Macros and Vba reference links

References:



While Loop Reference
http://www.exceltip.com/st/Using_Loops_in_VBA_in_Microsoft_Excel/628.html

Date and Time Reference
http://www.mvps.org/dmcritchie/excel/datetime.htm

Selecting cells/ranges
http://support.microsoft.com/kb/291308

Vba for excel
http://www.excel-vba.com/excel-vba-contents.htm

User Defined Functions
http://www.vertex42.com/ExcelArticles/user-defined-functions.html
http://www.exceltip.com/show_tip/Excel_Custom_Functions_using_VBA/Writing_Your_First_VBA_Function_in_Excel/631.html

Strings Reference
http://www.mvps.org/dmcritchie/excel/strings.htm

Using Ranges
http://msdn.microsoft.com/en-us/library/aa139976%28office.10%29.aspx

Using Sheets
http://www.mvps.org/dmcritchie/excel/sheets.htm

Searching and auto filing using macros in excel with in sheets

Description:

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
Project
Skills
Role
Start Date
End Date
Abc
P1
S1
R1
2-Feb-10
15-Oct-10
Def
P2
S2
R2
15-Mar-10
20-Sep-10
Ghi
P3
S2
R3


Jkl
P4
S1
R4
15-Jan-09
20-Dec-09
Mno
P3
S1
R1
15-Mar-10
15-Mar-12
PSR
P6
S2
R2
15-Mar-10
12-Dec-10
   

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
    Sheets("Sheet2").Activate
   
    'Gap column should be in 'G' Column
    Set currentCell = Range("G2")
    currentCell.Select
    names = ""
    'MsgBox (currentCell)
   
    Do
        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
    Sheets("Sheet2").Activate
    'MsgBox (names)
    If names <> "" Then
        currentCell.Offset(0, 1).Value = names
    Else
        currentCell.Offset(0, 1).Value = "-NA-"
    End If
   
    'Name Available Column should be next to Gap 'G' Column
    Set currentCell = currentCell.Offset(1, 0)
   
    currentCell.Select
   
    '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
    Sheets("sheet1").Activate
   
    Dim names As String
    names = ""
   
    'Employee Name should be in 'A' Column
    Range("A2").Select
   
    Do
    '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
                Else
                    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 http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx  
  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 http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx 
  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))
  
    header_row.Select
  
    '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.Select
    

    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
    Selection.Columns.AutoFit
  
    '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:

D:\oraclexe\app\oracle\product\10.1.0\server\NETWORK\ADMIN\TNSNAMES.ORA

Add and entry in the file as follows

 HOST_XE =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )
  )

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


TNSPING:

  • Ping your ip address as in the following example.


C:\Documents and Settings\test>tnsping 192.168.101.1


Output:


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


010 09:27:37



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



Used parameter files:


D:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora



Used HOSTNAME adapter to resolve the alias


Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.1)(PORT=1521)))


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 filename.sh 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 {}
   (or)
   find /folderpath -type -f -iname "*.fileextension"  -exec /bin/rm -rf {} \;
3) chmod +x filename.sh
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 psrdotcom@gmail.com

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(,)
Note:
    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(
        concat(',',concat(value1,',')),
        (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
        concat(',',concat(value1,',')),

        // 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 psrdotcom@gmail.com