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
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 28, 2009 at 11:37 PM
Related:
- Return days only if between two dates -Excel
- Skype last seen days ago - Guide
- Display two columns in data validation list but return only one - Guide
- How to add 90 days to a date in excel - Guide
- Excel marksheet - Guide
- Number to words in excel - Guide
2 responses
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 28, 2009 at 11:37 PM
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)))))
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 16, 2009 at 10:58 PM
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
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?
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?
