Tech2 Touch

Author: Aniruddh

  • Excel Tip To Calculate The Number of Work Days Between Two Dates

    There are many situation where we need to calculate the difference between two dates. We can get the no of days by subtracting the one date by one date. However, that gives the total days between these dates in which includes weekly offs and Holidays. To overcome this, excel provides a dedicated function called NETWORKDAYS.
    The Excel NETWORKDAYS function calculates the difference between two dates excluding weekend days(Saturday and Sunday).  You can even provide the list of holidays in between the two dates two excludes that many days. The holidays are different for different regions. So excel by default will not be able find which dates are Holidays. Because of this we need to give list of Holidays to get the exact working days excluding holidays and weekly offs.
    The Syntax of NETWORKDAYS Excel Function
    =NETWORKDAYS(Start_Date, End Date,[holidays])
    Where
    Start Date is the starting date and End Date is the closing Date or Finishing date.
    Holidays is the the range which contains the dates which falls under Holiday

    Example for NETWORKDAYS Function
    Let us take example in which we have 01-July-2013  in  Cell A1 and 31-July-2013 in cell B1. And the list of Holidays are kept in the range D1:D3. The formula to calculate the exact working days between these two days as mentioned below.
    =NETWORKDAYS(A1,B1,D1:D2) which gives result as 21. We have total 31 days between these days. In that we have 8 weekly offs and two Holidays. So the formula gives 31-8-2=21.

  • Format Cell using EXCEL function

    In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

    Format Cell Values using EXCEL Function

    We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

    Syntax of Excel TEXT Function

    =TEXT(value, format_text)

    Where
    Value is the number which we want to change the format.
    format_text is the required format.

    Examples of converting the format of values in Excel using formula

    We have a value 41479 in cell A1.
    =TEXT(A1,”dd-mmm-yyyy”)
    The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).

    Similarly you can use any format in which you want to display the number. Below are some more example for the same.

    • =TEXT(A1,”#,##0″) this will give output as 41,479.
    • =TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.

    Like this you can use your own formatting using this excel formula. The biggest advantage of this excel function is that you can use this formula inside another formula or function to format the output.

    For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
    =A1&B1 the output will look like this. Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
    =A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.

    You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
    =TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)

    Hope this helps to create your excel templates by using the Formula to Format Cell.

  • Excel If Function – Syntax And Examples

    Excel IF function is one of my favorite function in Excel. It is very useful and very simple to use. It does wonders when you want to create some complicated excel Formulas. You can extend the functionalities of IF function by nesting it or using along with other Excel Functions such as VLOOKUP, AND, OR etc.
    How Excel IF Function Works?
    Excel IF Function  checks for a condition(Criteria) and returns a value if the condition is met or returns another value if the criteria is not met. The criteria can be anything such as comparison, text check or error check etc. You can use nested IF functions upto 98 times in a excel worksheet.

    Syntax of Excel IF Function

    =if(condition, value if the condition is met, value if condition is not met)

    Examples:
    =if(A1>10, “Greater Than 10”, “Less than 10”)

    The Above IF formula returns Greater than 10 if the cell A1 has the value greater than 10 else returns Less than 10.

    Using IF function with AND

    As said earlier, you can use AND function inside IF  while you want a value to be written provides multi conditions were met. AND function returns TRUE if and only if  all the conditions are TRUE.For example, you have a value in cell A1. You want to check whether the number is in between 10 to 15. You can use the IF function as mentioned below.
    IF(AND(A1>10,A1

    Using IF function with OR

    IF and OR is used when you want to check any of the conditions is met. The OR Function returns true if any of the condition is met. For example you have want to check out of range if the number in cell A1 is less than  0 or greater than 100. You can use IF function with OR to calculate this as mentioned below.
    IF(OR(A1>100,A1

    Using Nested IF Function

    Using IF functions inside another IF function is called as nested IF functions. It is useful when you want to dig into the more detailed checks. For example you want to check if the number you have entered is a negative or positive in the cell A1.  First we need to check whether the entered value is number. If it is a number, then we need to check whether it 0. Then we need to check whether it is greater than 0 or less than 0 to find out the value entered is positive or negative. The nested IF function in Excel looks like as mentioned below.
    =IF(ISNUMBER(A1), IF(A1=0,”NIL”,IF(A1>0,”Positive Number”,”Negative Number”)),”It is not a Number”)
    The above uses ISNUMBER function to check for the whether the entered value is number or not.
    To learn how to use IF function with vlookup read my post on how to do vlookup on different ranges based on criteria.

  • Hiding or Unhiding Rows and Columns in Excel using Buttons

    Have you seen a excel spreadsheets with + or – symbols which on clicking unhides or hides rows and columns?  You might think that, it is done using Excel VBA. But this is not done by any VBA code. It is a simple grouping features of Excel. It is very useful when you want to display only the summary and show details with single button click. Lets start learning how to group and ungroup in Excel.
    For example you might have data which contains datewise data along with Weekly summary. You can show only the summary and provide the option to user to view the details using the + or – buttons

    Steps to Group Rows or Columns in Excel

    Step 1: Select the cells you want to group it. You can choose rows or columns according to your requirement. In this example i am selecting the rows from A2 to A5.
    Step 2: Go to Data and click on Group and select Group once again.  Your selected rows or columns are grouped.
    Step 3: There will be  + sign when your details are grouped and showing only summary. Where clicking on + sign will expand the group

    Steps to Ungroup rows or Columns

    Step1: Select the rows or columns you have Group enabled.
    Step2: Go to Data and select Ungroup and choose Ungroup.
    Step3: The Group is removed.

    As mentioned earlier, grouping in excel is very useful for presenting the data in good manner. Advantages of grouping is it shows the summary and it required, the all the details are also there. Unlike simple row/column hiding, in grouping you will will have symbol which indicates that, there is a grouping.

    Hope this Excel tips is useful. If you find this guide useful, then please share it with your friends. Any queries and support needed, then leave a comment below.

  • How to Run PHP Scripts Using XAMPP

    We all know that, XAMPP is a free open source cross platform web server solution stack package, which consist mainly Apache HTTP server, MySQL server database and interpreters for script written in the PHP and Perl Programming languages. It is a good tool for learners of PHP, Perl or mySQL. Here is the simple steps to installing the XAMP and start using this for running your PHP scripts.

    Steps to run PHP Scripts Using XAMPP

    1. As said above, you can download XAMPP for free. You can get your free copy from here.
    2. Open the downloaded file and install the applications.
    3. Once installed, run the XAMPP control panel and start Apache and MySQL servers. Enable MySQL if your program needs database.

    running PHP scripts in XAMPP

    4. Click on the ‘Explore’ button on the XAMPP control panel. The new window opens with file explorer.  Search for the ‘htdocs’ folder.
    4. htdocs folder is the folder Apache server points to. In this folder you need to place the  PHP scripts your want to run.
    5. Open the browser and enter the address http:// localhost/yourphpscript.php, where yourphpscript is the script name of your php code.
    6. You can even create sub folders inside htdocs to place and run php scripts. If you are using folders insider htdocs, then you need to enter the following path in the browser address bar.
    http://localhost/foldername/yourphpscript.php where ‘foldername’ is the name of the folder which contains php scripts  created inside your htdoc folder.
    7. Your browser will execute the script and show the output.