Search : in
By :

Return days only if between two dates -Excel

Last answer on Sep 29, 2009 4:37:19 am BST MeZanna, on Sep 15, 2009 7:51:35 pm BST 
 Report this message to moderators

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?

Configuration: Windows XP

Best answers for « return days only if between two dates Excel » in :
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL + ; in the chosen cell. To insert current time, press CTRL+...
Delete duplicates in an Excel column ShowDelete duplicates in an Excel column To remove duplicates in an Excel column: Click on the Data menu Filter Advanced Filter In this menu, select the column where the duplicates Check the box "Extract without duplication"...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

Excelguru, on Sep 17, 2009 3:58:27 am BST

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 A wise man once said, 'I complained that I had no shoes unti­l I met a man who had no feet.'
I am interested in financial Modelling and custom excel deve­lopment with excel macros.

   
Reply to Excelguru

2

MeZanna, on Sep 24, 2009 7:12:56 am BST

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?

   
Reply to MeZanna

3

 Excelguru, on Sep 29, 2009 4:37:19 am BST

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)))))
A wise man once said, 'I complained that I had no shoes until I met a man who had no feet.'
I am interested in financial Modelling and custom excel development with excel macros.

   
Reply to Excelguru
Ask your question Reply
They need your help