Return days only if between two dates -Excel

Closed
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

I have a start date and an end date that span many years. I need to calculate prorated information about the rest of the row based on the number in each year. Example:

Start Date, End Date, Days for 2009, Days for 2010, Days in 2011
05/04/2007, 09/08/2010, ?, ?, ?
01/12/2009, 12/05/2012, ?, ?, ?

I can create a column to capture all the days between dates using DAYS360, but I am not sure how to capture JUST the days in 2010. Then just the days in 2011, ect...

Thoughts?

2 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Formula in C2 is =IF(OR(B2<DATE(2009,1,1),A2>DATE(2009,12,31)),0,IF(AND(B2<=DATE(2009,12,31),A2>=DATE(2009,1,1)),B2-A2+1,IF(AND(B2<=DATE(2009,12,31),A2<=DATE(2009,1,1)),B2-DATE(2009,1,1)+1,IF(AND(B2>=DATE(2009,12,31),A2<=DATE(2009,1,1)),DATE(2009,12,31)-DATE(2009,1,1)+1,IF(AND(B2>=DATE(2009,12,31),A2>=DATE(2009,1,1)),DATE(2009,12,31)-A2+1,0)))))
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello

Here is an idea
=DATE(2009,1,1) corresponds to jan 1 2009 and =DATE(2009,12,31) corresponds to dec 31, of year 2009
Find the difference in days with this after comparing with the start and end date of your year. The values 2009 can be changed to cell reference like A1 where the cell entry is 2009
Just to make sure I understand you correctly:

A*** B*** C*** D***E***

ASSUMPTIONS:
A2 STARTDATE (we'll use 1/1/2008 as an example)
B2 ENDDATE (We'll use 12/3/2009 as an example)
C2 =DATE(2009,1,1) (Formula you provided)
D2 =DATE(2009,12,31) (Formula you provided)

Formula in E2 would look like:
=DAYS360(C2-B2)

Is this right, or am I missing something?
0