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.

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

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.

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

How to recover XP (Admin) Password

Lost XP Password, Click this link

If you forgot Admin Password, Click this link

Its really good .. But people try with safe hands ..