IF Formulas with Dates [Closed]

Report
Posts
2
Registration date
Tuesday February 25, 2014
Status
Member
Last seen
February 25, 2014
-
Posts
2
Registration date
Tuesday February 25, 2014
Status
Member
Last seen
February 25, 2014
-
Hi all,

I have a large spreadsheet that contains revenue information tied to sales deals.

Column A contains the expected deal closing date and column D the deal amount. I'm trying to classify the amounts into Quarterly Revenue columns (R,S,T,U). To do this I generated the following formula:

=(IF(AND(($A13>=R$2),($A13<=R$3)),$D13,0))

ISSUE:

As I copy the formula from column R (Q1) to column S (Q2), the formula returns a true value and copies the amount on the next quarter column, as well. My assumption is that the way Excel is processing the formula is that it processes these conditions (greater than or equal AND less than or equal) and treats them as OR, thus if the A column value meets either, it considers them as TRUE.

I hope this explanation is clear and I'd truly appreciate some help on it.

Thanks

2 replies

Posts
1843
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 29, 2020
136
orodriguezjr, good afternoon.

I'm not sure if I understood well your doubt.

The logic function "AND" consider TRUE as a result, ONLY if ALL the internal conditions are checked as TRUE.

Your formula:
=(IF(AND(($A13>=R$2),($A13<=R$3)),$D13,0))

Suppose:
A13 = 10
R2 = 8
R3 = 12

Your formula accept a interval as TRUE

a) =(IF(AND(($A13>=R$2),($A13<=R$3)),$D13,0))
.......................10 >= 8.........10 <= 12
........................TRUE...............TRUE

then the CELL receives a D13 VALUE

A13 can be any value thru 8 to 12

Is this what you want?
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2
Registration date
Tuesday February 25, 2014
Status
Member
Last seen
February 25, 2014

Hi, thanks for your prompt replay. I finally solved the problem, although I had to solve it in stages using the formula assistant from Excel. First I made sure the AND function was correct and then I added in the IF function.

As it turned out, the problem was not with the formula but with the Date format. What I found out is that if the date formats you're trying to compare are different (in this case one format was in Spanish and the other in US English) then you will either get a VALUE error or a wrong comparison. A 04/01/14 date, for example would read Jan 1 in Spanish and April 1 in US English.

Thanks for your time and interest.

Regards,