Date within overlapping year

[rspenta]

Project year start 7/1/2009 7/1/2010
Project year end 6/30/2010 6/30/2011
start date endate year end1 year end2
8/1/2009 7/31/2010
6/1/2009 5/31/2010
7/1/2009 6/30/2010
7/1/2011 6/30/2012
3/1/2008 4/30/2009



I need help to find the number of project days
in the start date end date in the year1 and year2

[Don]

Hi and welcome to the forum!

To get the number of days left in the year, use this formula:

Code: Select all

=DATE(YEAR(A1),12,31)-A1

To get the number of days that have already passed in the year, use this formula:

Code: Select all

=A1-DATE(YEAR(A1),1,1)

This assumes the date used for the calculation is in cell A1.


If you want to get the only the business day count you can use the networkdays() function in Excel.

Number of business days left in the year:

Code: Select all

=NETWORKDAYS(A1,DATE(YEAR(A1),12,31))

Number of business days that have occurred in the year:

Code: Select all

=NETWORKDAYS(DATE(YEAR(A1),1,1),A1)

Add another argument to this function, the "holidays" argument, in order to account for holidays that the employees may have during the year.

hope this helps