Hide the date value

Hi Experts,

I need Experts help. How to hide the date value at Column L and N and only displayed once we typed “X” at column B: J?

Project-Report-test.xls
ThevaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonkeyOteCommented:
Some of the dates in L appear to be manual entry so you not entirely clear but in general you can use a pre-emptive COUNTIF test, ie:

=IF(COUNTIF(B4:J4,"x"),calculation,"")

Does that help ?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi Theva,

use an IF statement around your date calculation.

=IF(COUNTIF(B4:J4,"x")>0,IF(AND(O3<N3,O3<>""),O3+1,N3+1),"")

see attached.

cheers, teylyn

Copy-of-Project-Report-test.xls
0
sandeshjCommented:
As I'm unable to upload the XLS, I'm giving you the steps here...

Click on L4
Go to Format-> Conditional Formatting
For Condition 1 Choose "Formula Is" and give this "=IF(COUNTA(B4:J4)=0,TRUE,FALSE)" (without the quotes)
Click on Format and Choose Font color as White

For Condition 2 Choose "Formula Is" and give this "=IF(COUNTA(B4:J4)>0,TRUE,FALSE)" (without the quotes)
Click on Format and Choose Font color as Blue

Click OK.

Use the FormatPainter icon and apply same on all the date columns of Column L.

This way you can hide the dates(atleast from the view)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ThevaAuthor Commented:
Hi teylyn,

Have checked the file, it shows #Value! at Column O,P and Q. How to prevent this?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Theva,

the formula in column P needs to be changed to

=IF(COUNT(L3,N3)=2,IF(O3<>"",O3-L3,N3-L3),"")

and applied to all the pertinent cells.


See attached

Copy-of-Project-Report-test1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThevaAuthor Commented:
Hi,

Thanks for helping me.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the grade, Theva.

I'd strongly advise against using the conditional formatting solution you accepted as an assist, because the values will still be in the cells and will be calculated (wrongly) into your results, even if they are not immediately visible in the sheet.

cheers, teylyn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.