Formating Date

[Solved/Closed]
Report
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012
-
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012
-
Hello,

I have date in a cell now i want to see the next cell red color if the next cell is not filled up by the next 10 days from 1st cells date.

Thanks
Shiblee

4 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
41
Hi Shiblee,

As you have not mentioned which version of Microsoft Excel you are using, I am providing below the steps applicable to Microsoft Excel 2010. These steps will help you achieve the desired results:

1. Put the date in Cell "A1".
2. Click "B1".
3. Click "Home" from the top Ribbon.
4. Click "Conditional Formatting".
5. Click "Highlight Cell Rules".
6. Click "More Rules".
7. Select "Use a formula to determine which cell to format".
8. In "Format values where the formula is true" box type the following formula:

=ISNUMBER(SEARCH(A1+10,B1))=TRUE

9. Click "Format" button.
10. Click "Fill" tab.
11. Select red color and click "OK".
12. Click "OK" on "Edit Formatting Rule" dialog box.
13. Use Microsoft Excel's Fill Handle to drag the formatting to the subsequent cells.

I have updated a sample file also here:

https://authentification.site/RWc7g/ConditionalFormattingDates.xlsx

Please revert for clarification.
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012

Hi Zohaib

Its not working
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
if with "if the next cell is not filled up by the next 10 days from 1st cells date", you mean filled by any thing then you can try this formula for conditional formatting

=AND(B1="", TODAY()>A1+10)

The formula is saying that if b1 is blank and a1 date is more then 10 days from todays' days.
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012

it worked but would you please let me know how i can do below

i want the cell white again when i fill up the red cell
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It would help you in future if you try to understand what is happening in the formulas
=ISNUMBER(SEARCH(A1+10,B1))=TRUE
Is searching if in B1, you have a value that is is obtained by adding 10 to value of A. In case if finds, you would get a number which is being tested by ISnumber.

=AND(B1="", TODAY() >A1+10)
is testing two things, One that cell B1 is blank, and 2, today's data is more than 10 days from the value in day1
a better formula would be
=AND(A1<>"", B1="", TODAY() >A1+10)
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012

thanx
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
41
Hi Shiblee,

The steps I have suggested and the file I have uploaded was created using Microsoft Excel 2010. In your reply please include which Version of Microsoft Excel you are using so that I can find relevant steps?

Please do write back to us.
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012

i am using 2007
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
41
Hi Shiblee,

I am uploading a pictorial overview of steps 1 through 9, which should work for Microsoft Excel 2007 as well. Please check the image below:

http://s7.postimage.org/pb0j4ms2j/Conditonal_Formatting.jpg

Hope you find this useful.

Thanks & Regards
Zohaib R
#iworkfordell