Hi, I want get total value of given Date Range.
Ex : May-2011 to Jun-2012 should be answer is 287
Thanks
Hi, I want get total value of given Date Range.
Ex : May-2011 to Jun-2012 should be answer is 287
Thanks
You need the DATEDIF() function. This is a legacy function for which Excel doesn't display the usual intellisense information but it works. This is the syntax:
=DATEDIF([Start Date], [End Date],[Interval])
The Interval is one of these strings:-
"m" = months
"d" = days
"y" = years
Therefore the function your are looking for might be
[F15] = DATEDIF(C13, C18, "m")
That would return a result of 13, if C13 and C18 were dates, which they are not. It could never be 455 even if you were to change "m" in the formula to "d" because even 14 months have only 426 days at the most, and your dates - if they were dates - don't have days at all.
Obviously, you need the DATE() function which has this syntax.
=DATE([Year], [Month],[Day)
Now I ask you to make a little test. In C12 of your sample worksheet enter this formula:
[C12] =DATE(A3,5,C3)
C12 will display 14 May, 2011 in the short form you have set in the Regional Settings of your computer, perhaps 14/05/2011 or 5/15/2011. Now enter =C$12 in H13 and copy the formula to H14. The same date will appear in both cells. Now right-click on H13 and select Format Cells... from the drop-down menu.
Now the display in H13:H14 is the same as C13:C14 but for each of the cells you have a complete date - in fact the same date for both cells, despite their very different appearance - that you can do calculations with.