Date Calculation According to the Given Criteria

Hi guys ,

i have a data in excel

In cell B6 , I have an amount . and in Cell C6 I have inserted the date at which that amount has been kept . Now what i want is I want the Data from D6 To O6 to calculate as to how many Days that 100000.00 Rs or Dollar have been kept each month . Each month has its own Days like January has 31 Days . So i want each Days to appear accurately. Like for example if i insert the 4-8-2009 from January till August 4-8-2009 , how many days that amount has been kept each month .

Waiting For the solution

Excel attached in this Mail

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Whynot just subtract the dates?

here is a sheet which i think is what you are after?

Note the yellow cells now contain formulas to calculate the days!

Thank you . You Solved my Query . Thanks .

Please elaborate the formula .

I am not good in date formula can you please clear my concept in this . U have used the if function and match the date for each month and if the result is true it will give me the result and if the value is false it will follow another date formula .

=IF($C$6>DATE(YEAR(D5),MONTH(D5)+1,0),D7,IF(MONTH(D5)=MONTH($C$6),DAY($C$6),0))

Please explain this formula like this . as best i can read the above formula to my knowledge

IF Value of Cell C6 is greater then Date now i m lost at the bold formula above

Please Login or Register  to view this content.

Please explain this formula like this . as best i can read the above formula to my knowledge

ok

The d5-o5 contain the dates for the 1st of each month

so formula basically says, if the date is past the last date of the month Date(2009,5,0) is the last day of April, then use the days in the month stored in cell d7.

If the date is the same month as c6 use the day portion of that month, in this case 11

Otherwise use 0

Yes the green 0 can be replaced with ""

Please Login or Register  to view this content.

Since explanation wasn't clear, I think this is what you meant!