Previous Topic Next Topic

Formating Date

Solved/Closed
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012 - Dec 19, 2012 at 02:25 AM
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012 - Dec 19, 2012 at 11:34 PM
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 responses

Answer 1 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 19, 2012 at 04:35 AM
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.
0
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012
Dec 19, 2012 at 06:55 AM
Hi Zohaib

Its not working
0
Answer 2 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 19, 2012 at 07:07 AM
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.
0
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012
Dec 19, 2012 at 10:38 PM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 19, 2012 at 11:05 PM
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)
0
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012
Dec 19, 2012 at 11:34 PM
thanx
0
Answer 3 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 19, 2012 at 09:48 PM
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.
0
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012
Dec 19, 2012 at 10:36 PM
i am using 2007
0
Answer 4 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 19, 2012 at 11:13 PM
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
0

Similar discussions

How do i change date format in (dd.mm.yyyy)
Talha -
Catherine -

6 responses
Excel format
D -
sheryljohnson -

1 response
Change Date Format - dd/mm/yyyy HH:MM:SS to mm/dd/yyyy HH:MM:SS
asmfiaz -
Mazzaropi -

2 responses
enter date in dd-mm-yyyy
sam -
fdibbins -

2 responses
Accidental formating
Min Thu Moe Moe -
BunoCS -

1 response