Accounts Receivable Aging Formula >120

[krmttlr]

B1 = Todays Date
C4 = Invoice Date
D4 = Due Date =IF(WEEKDAY(C4+30)=7,C4+32,IF(WEEKDAY(C4+30)=1,C4+31,C4+30))
E4 = Days Past Due =IF(B$1-D4<1,"Current",B$1-D4)
F4 = Amount
Column G 1 - 30 =IF(E4<=30,F4,"")
Column H 31 - 60 =IF(AND($E4>31,$E4<=60),$F4,"")
Column I 61 - 90 =IF(AND($E4>60,$E4<=90),$F4,"")
Column J 91 - 120 =IF(AND($E4>90,$E4<=120),$F4,"")
Column K > 120 =IF($E4>120,$F4,"")

If E4 contains "Current" column K >120 picks up the amount rather than 0

If I change the formula in K4 to =IF($E4="current"," ",IF($E4>120,$F4,"")) it works correctly, but why doesn't the original formula work?

thanks for your help.

 

[Andrew Poulsom]

It's because Current is greater than 120 in the sort order.

 

[krmttlr]

If I use =IF(B$1-D4<1," ",B$1-D4) for Days past due, is " " considered greater than 120? Because it does the same thing.

Thanks

 

[Andrew Poulsom]

Yes a space is greater than any number in the sort order.