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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
ThevaAuthor Commented:
Hi teylyn,

Have checked the file, it shows #Value! at Column O,P and Q. How to prevent this?
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.