Dates referencing

[rajiv_bishnoi]

I want to find how to reference dates in months. For example, how can I create a formula in Cell E 43 and F 43, which will look up in the tables of debt drawdown "dates" and debt repayments "dates", and check if any of these happened to be in that particular "month", and if yes, the value thereof.

http://www.sendspace.com/file/7r3qkb

If it can be done without VBA, it would be wonderful

Thank you for your help!

regards

Rajiv


Please do not click on "Click here to start download" next to a CD icon... it will download some downloader.exe; just click on the "Click here to start download from sendspace"... ****; this forum doesn't allow any attachment.

 

[Trevor G]

WHat about something like this, I didn't do your download.

Excel Workbook
	C	D	E	F	G	H	I
3	Dates	Value			Month	Month	Total
4	26/03/2012	900			01/03/2012	31/03/2012	18060
5	27/03/2012	870
6	28/03/2012	840
7	29/03/2012	810
8	30/03/2012	780
9	31/03/2012	750
10	01/04/2012	720
11	02/04/2012	690
12	03/04/2012	660
13	04/04/2012	630
14	05/04/2012	600
15	06/04/2012	570
16	07/04/2012	540
17	08/04/2012	510
18	09/04/2012	480
19	10/04/2012	450
20	11/04/2012	420
21	12/04/2012	390
22	13/04/2012	360
23	14/04/2012	330
24	15/04/2012	300
25	16/04/2012	270
26	17/04/2012	240
Sheet1

 

[BiocideJ]

You should be able to use the MONTH function to control an IF statement like so...

=IF(MONTH(date1) = MONTH(date2),truevalue,falsevalue)

just replace date1 and date2 with the cell references for the dates you want to compare.
and truevalue and falsevalue would be either references or manually entered values.

ex: =IF(MONTH(C43) = MONTH(A1), D43, 0)

 

[Red over White]

Am I missing something with Trevor G's table as the intimation of G4 and H4 is for the values between those two dates, which adds up to 4950 (D4:D9) - rather than the whole list which I assume adds up to 18060.

I am trying to use this formula to establish the total value between two dates (say G4 and H4) as in this example, although the dates in 'my' column C are not in chronology order.

Any help would be appreciated

 

[BiocideJ]

Actually, the sum of D4:D26 ends up being only 13110, however, 13110 + 4950 = 18060.

The following correction to the formula in I4 should give accurate results.

=SUMPRODUCT(--($C$4:$C$26>=$G$4)-($C$4:$C$26>$H$4),($D$4:$D$26))

The problem with the previous formula was it was still counting any value greater than 3/1 C4>=G4 (which was all) and also counting any value before 3/31 C4<=H4 (which was the first 6)

This new formula counts all values greater than 3/1 (C4>=G4), but then decrements that count if the date is also after 3/31 (C4>H4) notice the single negative instead of the double negative.

 

[rajiv_bishnoi]

Thanks everybody for your help/efforts. I figured it out by using multiple sumif function... SUMIFS... essentially, for the cash flow dates, created columns for month and year, and then used sumifs to match the month and year to the required month bucket

 

[BiocideJ]

Thanks everybody for your help/efforts. I figured it out by using multiple sumif function... SUMIFS... essentially, for the cash flow dates, created columns for month and year, and then used sumifs to match the month and year to the required month bucket

Excellent. That is definitely an easier way to do it, but since you didn't specify that you had Excel 2007+, the broader solution was given.