Excel Tips (2) with Sanjay Bakshi

Now day’s people are very used to GUI (Graphical User Interface) most of the time they use Mouse but developer still believes writing formulas. As an IT person I also feel writing formula’s rather than going to search options etc.

We will showcase some formula tips which help user to perform their task if they are not aware about options.

Situation 1

You got one file from client and it contains Column A as Employee Name which is a combination of First Name & Last Name. Now your boss has asked you to create a report with First Name only.So challenge is to "Get the first name". There is already one feature available in Excel but we will create a formula for this.

                                           Answer : =LEFT("Cell_No",FIND(" ","Cell_No")-1)

********************************************************************************************************

Situation 2

Count the number of instances where amount is less than 0.

Answer : =COUNTIF(C3:C9,"<0")

********************************************************************************************************

Situation 3

Count the no of days/Month/Year between two dates

There are multiple ways to handle above situation in Excel, we are going perform same task with Datedif function

Calculate Days

Please follow below steps
1) Create one Cell with Start Date
2) Create one Cell with End Date
3) In third Cell add =datedif(start_date,end_date,"D")

Calculate Month

Please follow below steps
1) Create one Cell with Start Date
2) Create one Cell with End Date
3) In third Cell add =datedif(start_date,end_date,"M")

Calculate Year

Please follow below steps
1) Create one Cell with Start Date
2) Create one Cell with End Date
3) In third Cell add =datedif(start_date,end_date,"Y")

********************************************************************************************************

Situation 4

You have created one training plan with start date and end date and training manager has asked, please let me know in how many working days you will cover the training program? If you are not ready with your an answer the below function will definitely save you from trouble.

Please follow below steps to answer above query
1) Enter Training Start Date in First Cell
2) Enter Training End Date in Second Cell
3) Enter =NETWORKDAYS(Training_Start_Date,Training_End_Date)

Training Start Date	Training End Date	Work Days
1/1/2004	5/31/2014	2717

 
********************************************************************************************************

Situation 5

When you have given the above answer all of sudden Training Manager has tweak the situation and informed "Oh Sorry I Forget Every 1st October we will have holiday) so now tell me how many working days required"

So now as per situation you have to minus all the days for 1stOct.Not to worry NETWORKDAYS is having one parameter where you can define optional holiday range.

Please follow below steps to answer above query
1) Enter Training Start Date in First Cell
2) Enter Training End Date in Second Cell
3) Enter =NETWORKDAYS(Training_Start_Date,Training_End_Date,Optional_Range)

Please do not get confuse that Situation 4 has returned 2717 days and and situation 5 has returned 2710 but holidays are 10

Net work days does not consider Saturday and Sunday.

Start Date	End Date	Net Work Days
1/1/2004	5/31/2014	2710
Holiday List
10/1/2004
10/1/2005
10/1/2006
10/1/2007
10/1/2008
10/1/2009
10/1/2010
10/1/2011
10/1/2012
10/1/2013

Points to Remember

1) First Parameter represents Start Date
2) Second Parameter represents End Date
3) Holidays is an optional parameter or range of one or more dates to exclude from working calendar.

********************************************************************************************************

Keep Learning, Happy Sunday

Sanjay Bakshi
mesanjaybakshi@gmail.com