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")
********************************************************************************************************
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.
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.
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)
********************************************************************************************************
Situation 5Please 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 |
********************************************************************************************************
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
