Between 2 Dates Problem

[TRI0N]

Hey all you all helped me with a formula for Between 2 dates. However this formula is removing the last day of that date range. Here is the Formula:

=SUMPRODUCT(--('Transaction DB'!$J:$J>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--('Transaction DB'!$J:$J<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)),--('Transaction DB'!$D:$D=B41),'Transaction DB'!$E:$E)

It will not work for a single day either. For instance

From: 01/01/2009 To: 01/01/2009 = Rusults in 0 Returned Records.

From 01/01/2009 To: 03/31/2009 = Results is missing day 03/31/2009 Data.

Thanks and hope there is an easy fix for this.

[NBVC]

Should work... can you attach sample workbook?

[TRI0N]

Unfortuanlly I can't. Its government property. The formula works but if the Start and End Date are the same it results to 0 and if between dates the ending is not added to the results. It would seam silly to have users enter 04/01/2009 just to have it ad in 03/31/2009. Its just not adding the ending day to its function.

[teylyn]

create a dummy workbook that includes enough dummy data to reproduce the problem and upload that.

or

check the data in your original file

is ('Transaction DB'!$D:$D=B41) TRUE?

[NBVC]

Well, you have the >= and <= operators so it should theoretically work... So unless something is wrong with the actual date entries in those ranges or something is not right in D:D or E:E then it is hard to tell without seeing it.

Can you post the workbook, first deleting unnecessary columns and changing around names, etc that shouldn't be seen?

[TRI0N]

Okay making Dummy Book. Need to remove, DNS Connections, Network Data Paths, Etc.. Give me a few.

[TRI0N]

Okay I'm making a Test Book but it takes some time to remove data I don't need to add. While I'm at it the date that the forumla is run against has time in it. Perhaps the solution would be to add time in the formula. Such as Start: 0:00:00 and End 23:59:59? If that is the case then how would I add just that to what I have?

I believe perhaps the formula is automatically making the date times 0:00:00 so the End Date is not added due to this since it will stop adding at 0:00:00 that day.

[TRI0N]

=SUMPRODUCT(--('Transaction DB'!$J:$J>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--('Transaction DB'!$J:$J<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4,23:59:59)),--('Transaction DB'!$D:$D=B41),'Transaction DB'!$E:$E)

Since it should default to 0:00:00 for the start date I don't need to add a start time. But would the above for End Time be correct?

[NBVC]

Try:

=SUMPRODUCT(--(INT('Transaction DB'!$J:$J)>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--(INT('Transaction DB'!$J:$J)<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4,23:59:59)),--('Transaction DB'!$D:$D=B41),'Transaction DB'!$E:$E)

[TRI0N]

Okay that can't be right. Does not see the time correctly. Hummm mind is going bonkers.

[NBVC]

It's looking only at the date portion that's with the INT() function, since you are trying to equate to the DATE() function.

[TRI0N]

No I mean the formula is not valid it see the Time in the end date as a range on the current page.

[NBVC]

I hadn't noticed you add that 23:59:59 in there...

try

=SUMPRODUCT(--(INT('Transaction DB'!$J:$J)>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--(INT('Transaction DB'!$J:$J)<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)),--('Transaction DB'!$D:$D=B41),'Transaction DB'!$E:$E)

[TRI0N]

Demo File Uploaded. Sorry for the delay.. There was a lot to remove before I could do this.

[TRI0N]

Play with D41 on Rounds Detailed to get results of 34.

[NBVC]

I get 34 with this:

=SUMPRODUCT(--('Transaction DB'!$J:$J>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--('Transaction DB'!$J:$J<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)+TIME(23,59,59)),--('Transaction DB'!$D:$D=B41),'Transaction DB'!$E:$E)

[TRI0N]

Rock On! Will impliment that.. Was like going nuts on this.. Overlooked the Time Default to date and that explains the day missing. But adding time was like OMG.. Doh!

[NBVC]

Great!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[DonkeyOte]

You should really be looking to use SUMIFS rather than SUMPRODUCT - it will be significantly more efficient

[NBVC]

Then:

=SUMIFS('Transaction DB'!$E:$E,'Transaction DB'!$J:$J,">="&DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2),'Transaction DB'!$J:$J,"<="&DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)+TIME(23,59,59),'Transaction DB'!$D:$D,B41)