Formula

G

Guest

I need help with the following formula.
Colum C3 contains the month end date
Column A6 Contains a code
Column H6 contains the start date
Column I6 contains the end date
Colum J6 contains a value.

I have the formula
=IF(AND(I6<$C$3,A6<>207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2)

I need to alter the formula so that if the end date is less than the month
end date then no value will show.
Thanks
 
G

Guest

Possibly this might suffice
=IF(OR($C$3="",I6="",I6<$C$3),"",IF(AND(I6<$C$3,A6<>207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))

The above adds the front IF checks on C3/I6 not being blank,
and your posted reqt for it to return blank if I6 is less than C6, viz.:
=IF(OR($C$3="",I6="",I6<$C$3),"",<your formula>)
 
G

Guest

How about this:

=IF(I6<$C$3,"",IF((A6<>207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))
 
G

Guest

C3 or I6 will never be blank so this doesn't work for me.

It's harmless to include the additional checks on C3/I6 being blank**.
The earlier suggestion (below) should still work fine:since the 3rd check: I6<$C$3 will take care of your reqt
(I'm not sure that you did try it out?)

It could of course, be reduced to just this:
=IF(I6<$C$3,"",<your formula>)

**the precaution is because blank cells are evaluated as zeros in formulas,
and dates are just numbers, so if I6 is blank and C3 contains a date, then
the check I6<$C$3 would misleadingly return TRUE.

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top