subtracting dates

entry date exit date

8-10-2009 10-5-09

I need to know the difference between the exit day and the current month


10Q in advance

In your example above, what do you expect the answer to be?

The Exit Day and the current month are the same, so it's unclear.

This formula is a bit of a trick. You put the month start date in a fixed cell (in this case C1), then use this SUMPRODUCT() formula:

Please Login or Register  to view this content.

In C2 use:
=SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=$C$1) * (ROW(INDIRECT($A2&":"&$B2))<DATE(YEAR(C$1),MONTH(C$1)+1,1)))

...and copy it down.


When adding/subtracting dates, people disagree about what the answer should be, some think the dates should be inclusive of the start date, other say no. That's why I asked you for some sample answers. Anyway, you may decide you need to subtract 1 from the answers. Up to you.

JB, why the need for (Volatile) SUMPRODUCT ?

With month specified in C1

=MIN(DATE(YEAR(C$1),MONTH(C$1)+1,0),B2+1)-MAX(C$1-DAY(C$1)+1,A2)
copied down

if you know categorically that C1 is always first of month you can remove the -DAY+1 check.

As I said, then, my original formula... then -1.
=SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=$C$1) * (ROW(INDIRECT($A2&":"&$B2))<DATE(YEAR(C$1),MONTH(C$1)+1,1))) - 1

Or Don's.... -1:
=MIN(DATE(YEAR(C$1),MONTH(C$1)+1,0),B2+1)-MAX(C$1-DAY(C$1)+1,A2) - 1

===========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)