hide & unhide cells values in specific column based on date

[leap out]

hello

I have data from a2 :e so the column e is values should hide the values and show empty or dash (-) if the date is not today and unhide if i would show the numbers whether the date is today or not when I write "not today " in h2 it means less than date (today) & date (today) based on column a

short word when I open the file it should hide all values and when write in h2 not today then show the all the values

date	brand	pur	sell	q			date
23/01/2021	aswe-56	11	1	10			not today
23/01/2021	aswe-57	2	1	1
23/01/2021	aswe-58	33	4	29
23/01/2021	aswe-59	12	4	8

 

[Joe4]

Do you actually want the rows to be hidden, or do you want the rows to appear, but just not show anything, if those conditions are met (so it just shows white space)?

 

[leap out]

hi
no to more clear not hide rows or columns just contains the cells the values should be gone so see my picture above and the expected result in this picture today is 23/02/2021 so the data is normal when I open file every thing is showing the values , but if I open the file tomorrow then the date becomes 24/02/2021 and I have 23/02/2021 should hide the values in column e

date	brand	pur	sell	q			date
23/01/2021	aswe-56	11	1	-
23/01/2021	aswe-57	2	1	-
23/01/2021	aswe-58	33	4	-
23/01/2021	aswe-59	12	4	-

and if I write not today in h2 then show the values

date	brand	pur	sell	q			date
23/01/2021	aswe-56	11	1	10			not today
23/01/2021	aswe-57	2	1	1
23/01/2021	aswe-58	33	4	29
23/01/2021	aswe-59	12	4	8

 

[Joe4]

You should be able to do this with Conditional Formatting to make the text of the cell white, so that the number is not erased, but its color matches the background color so it becomes invisible.

Here are the steps to do that for your example (assuming "q" is in column E and the data starts in cell E2):
1. Select the range E2:E5
2. Go to Conditional Formatting
3. Select "New Rule"
4. Select the "Use a formula to determine which cells to format" option
5. Enter the following formula:

=AND($H$2<>"not today",$A2<>TODAY())

6. Click on the "Format" button
7. Go to the "Font" tab
8. Click on the drop-down "Color" box, and select the White color
9. Click "OK"

This should do what you want.

 

[leap out]

actually I no know how it works I did what you said but still the values in col e are hidden I changed the date in column a to 22/02/2021 to test it to show the values nothing happens and I have at least 2000 rows data I no know if this way is practical just I added a simple sample to understand what I want

 

[Joe4]

actually I no know how it works I did what you said but still the column e is hidden I changed the date in column a to 22/02/2021 to test it to show the values nothing happens and I have at least 2000 rows data I no know if this way is practical

2000 rows is not an issue for Conditional Formatting, and it really is no extra work to apply. Just select from E2 down to the last row of data in column E.

As I understand it, this is what should happen:
1. If the value in cell H2 is "not today", then ALL data in column E should be visible.
2. If cell H2 does NOT say "not today", then only rows with today's date in column A should be visible.

I tested it out, and it appears to be working according to the rules above.
If that is not the case for you, then you need to check a few things:
1. Are the entries in column A really dates, or are they text?
2. If "not today" is in cell H2, make sure that there are not any extra spaces or other characters at the end of it.

 

[leap out]

As I understand it, this is what should happen:
1. If the value in cell H2 is "not today", then ALL data in column E should be visible.
2. If cell H2 does NOT say "not today", then only rows with today's date in column A should be visible.

about point 1 you're right
but about point 2 the date in col a is 23/02/2021 when clear h2 nothing happens no show values ?

 

[leap out]

I fixed the problem is formatting date it should 02/23/2021 not 23/02/2021
many thanks for this solution
but i still have curiosity if there is way do that dynamically without every time i have to reselect a gain to reach the last row

 

[Joe4]

but i still have curiosity if there is way do that dynamically without every time i have to reselect a gain to reach the last row

When you set it up initially, just go down to some row that you will never hit.
There is not reason why you cannot pre-Conditionally Format more rows than you will use (i.e. you could select E2:E5000 to start).

 

[leap out]

@thanks I have no a problem with condition formatting just I see some formulas are relating the condition formatting it's dynamic like E:E without specify the numbers in range indeed you've given me what I'm looking for just curiosity
many thanks buddy