How to reference cell with date in formula

[4lilprinces]

Hi,

I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")

It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:

A2 contains 9/9/2011
B2 contains 9/22/2011

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")

but it doesn't work. Can anyone help me figure out how to save some time and make this work the way I want it to?

Thanks so much!
llj

[Analyst_Jim]

You could try ">=Text(A2,"m/d/yyyy")" and "<=Text(B2,"m/d/yyyy")" and see if that works. It may not though with the multiple quotes. Do you have an example I could play with to see which way to go?

Jim

[4lilprinces]

That didn't quite work - I think you're right about the quotes.

I'm attaching a modified version of my spreadsheet (I only wish these were all my monthly bills!) but it should be enough for you to see what's going on. The paydays are all listed on the right side of the "Summary" worksheet. For the formula, I enter the most recent payday to start, then the upcoming payday minus one day to calculate the pay period. Ideally, I would like to be able to enter (DATE1) and (DATE2-1).

I really appreciate your help. I keep thinking there has to be a simple solution to this. Lets hope I'm right.

[ChemistB]

You need to put the cell reference outside the quotes and concatenate like this

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">="& $A$2,NextPaymentDueDate,"<=" &B2)
Does that work for you?

[4lilprinces]

Thanks so much!! It worked! I had to tweak it just a little - here's what I ended up with:

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">="&A1,NextPaymentDueDate,"<="&B1)

Again, THANK YOU!!

[rcm]

tRY THIS ... I added an asterisk to mark the starting date, play with it..

[rcm]

there was an error in the last macro...