Net working days - calculations in Excel

Last week I have received a mail from my team members  stating that he was wasting his time to find the working days between two days to calculate the mandays calculations for the project.

I have given the below suggtions to make his job easier

Excel has a date function called NETWORKDAYS; which is a function to find out the working day i.e. Monday to Friday in between two given dates.

For example;

Let us assume - Project start date : 25 Oct 2013  
Project completion date : 31 Jan 2014

and the following are the closed holidays - 5 & 6 Nov; 25 Dec; 1, 14,15,26 Jan

The simple formula would be {=NETWORKDAYS(Startdate, enddate, [holidays])}

The formula will give you the result 65 working days.

There are 71 weekdays in that 6 closed holidays given in the range. Hence the result is 65 working days. 

The same formula may be applied to set of records in the excel database; it eliminates the cumbersome calculations and formulas for the recquired result.

DEAL WITH DATES BEFORE 1900

	B	C	D	E	F	G
5	DEAL WITH DATES BEFORE 1900
4	START	END	START MODIFIED	END MODIFIED	DELTA DAYS	DELTA YEARS
5	2/17/1850	5/15/1960	2/17/2850	5/15/2960	40264	110
6	1/5/1880	6/14/1900	1/5/2880	6/14/2900	7465	20
7	11/13/1600	11/13/1900	11/13/2600	11/13/2900	109573	300
8	8/8/1760	9/10/1880	8/8/2760	9/10/2880	43863	120
9	10/11/1660	12/9/1980	10/11/2660	12/9/2980	116937	320
10	3/6/1670	6/6/1940	3/6/2670	6/6/2940	98708	270
11	7/5/1889	10/11/1990	7/5/2889	10/11/2990	36987	101
12	12/22/1740	4/23/1900	12/22/2740	4/23/2900	58196	159
13	7/20/1650	5/29/1950	7/20/2650	5/29/2950	109521	299
14	12/12/1888	10/28/1970	12/12/2888	10/28/2970	29904	81

If the cell contains a real date,you want to add,1000 years.An easy way to do this is to use the EDATE function and add 12,000 months to the date. =EDATE(a5,12000) returns a date that is 1000 years after a valid date in a5. note that tis function requires the analysis toolpack in versions prior to excel 2007. if you can ensure the analysis tooolpack is installed ,you can use "=date(year(a5)+1000,month(a5),day(a5))"                                                                                                                                                                                                                                                                                                                                    if the cell does not contain a real date,you need to break the date apart,add 1000 years, put the date back together,and convert it to a real date:                                                                                                                                                                                       

*  To get the left portion of the date,use=left(a5,len(a5)-4)                                                                                                                            *   To get the year portion of the date,use=right(a5,4)                                                                                                                                   *   To add 1000 years to the date,use=right(a5,4)+1000                                                                                                                                         *   To put the month,day,year+1000 back together,use     =left(a5,len(a5)-4)&right(a5,4)+1000                                                          *   To convert that rasult back to a true date use =datevalue(left(a4,len(a4)-4)&right(a4,4)+1000)                                                         

You now need to selectively use either the EDATE or the DATEVALUE portion of the formula, depending on whether excel sees the date  in a5 as text.enter this if statement in cell c5:                                                                                        =if(istext(a5),datevalue(left(a5,len(a5)-4&right(a5,4)+1000),edate(a5,12000))                                                                                        copy it to cell c5                                                                                                                                                                                                                                                      in cell e5 calculates the number of elapsed days with=d5-c5,                                                                                                                            in cell f5 calculate the number of years with=dateif(c5,d5,"y")                                                                                                                                                                                                                                                                                                                                                                                                        you can combine the formulas from c5,d5and e5 into a single mege formula:                                                                                                                                         =if(istext(b5),datevalue(left(b5,len(b5)-4)&right(b5,4)+1000),edate(b5,12000))-if(istext(a5),datevalue(left(a5,len(a5)-4)&right(a5,4)+1000),edate(a5,12000))

How do I find a date after 3 months from today?

Question;

My friend had a situation that he want create a sheet that every forth month on the same date of billing the customer needs to be remained for their premium payment.  He want a function to use in his formula since their are 300 customer he need to send advice on next billing.

My Solution is;

Step 1 : Customer are need to be reminded for their premium payment on quarterly basis.  In a column put the billing date.

Step 2 : Create a column to find the date on which the reminder has to be send.

Step 3 : If  D1 has the billing date; the function/formula at E1 is;
=DATE(YEAR(D1),MONTH(D1)+3,DAY(D1))

Step 4:  Copy the formula for all the rows which will give you the next billing date.