Return days only if between two dates -Excel

Closed
MeZanna - Sep 15, 2009 at 02:51 PM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 28, 2009 at 11:37 PM
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

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 28, 2009 at 11:37 PM
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)))))
6
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 16, 2009 at 10:58 PM
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
0
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