Hi,
I attached my spreadsheet. I made a conditional formatting that when Column M is Expired it will highlight the row as red but if it says Pending then it will highlight the row blue. When I try to copy the formula from the 1st rows and drag it down to the below rows. The Status column will say Expired and highlight the entire rows red.
Is there a way to fix this that if valid until column, valid from column and redeemed date is blank then status will just be blank?
voucher.xlsx
It's because the condition for the second IF statement is TRUE, ie I16<=Today, J16<=Today, and L16 is blank (in this instance Excel treats the blank cells of I16 and J16 as 0, which is less than 41794, which is the numeric value for today's date.
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
Is there a work around then so that it won't highlight the red rows
Formula:
Please Login or Register to view this content.
I haven't tested that the third and forth IF statements need a similar addition, but I don't think they will.
your conditional format needs to apply to $M$14:$M$500
also i think your formula isn't working properlyas gak pointed out
probably do a isblank() check before expired
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
i just used Gak's formula from above post instead of using isblank
the light blue conditional format was " " instead of "" and related to I14?
should just rely purely on column M results
to begin with, try using this, instead of the formula you have in M...
=IF(I14<=TODAY(),IF(J14>=TODAY(),IF(L14="","Pending",IF(L14<>TODAY(),"Redeemed","Expired")),""))
Also, I would put TODAY() in its own cell and then reference it - TODAY() is a dynamic function and recalcs with any workbook change, it could slow things down for you if you have enough of them
edit: I see that your last CF rule tests for " ".......remove the space
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much for all your help.
I used humdingaling spreadsheet and it is fixed.
Thank you for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks