IF Formulas with Dates

Closed
orodriguezjr Posts 2 Registration date Tuesday February 25, 2014 Status Member Last seen February 25, 2014 - Feb 25, 2014 at 01:21 PM
orodriguezjr Posts 2 Registration date Tuesday February 25, 2014 Status Member Last seen February 25, 2014 - Feb 25, 2014 at 04:08 PM
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

Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Feb 25, 2014 at 03:05 PM
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
orodriguezjr Posts 2 Registration date Tuesday February 25, 2014 Status Member Last seen February 25, 2014
Feb 25, 2014 at 04:08 PM
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,
0