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
shishacu Posts 5 Registration date Wednesday December 19, 2012 Status Member Last seen December 19, 2012 - Dec 19, 2012 at 11:34 PM
Related:
- Formating Date
- Excel date format dd.mm.yyyy - Guide
- Whatsapp date format - Guide
- How to change date format in excel - Guide
- How to change date format in openoffice - Guide
- Conditional formatting if cell contains date - Guide
4 responses
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
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.
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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 19, 2012 at 07:07 AM
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.
=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.
shishacu
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012
Dec 19, 2012 at 10:38 PM
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
i want the cell white again when i fill up the red cell
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 19, 2012 at 11:05 PM
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)
=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)
shishacu
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012
Dec 19, 2012 at 11:34 PM
Dec 19, 2012 at 11:34 PM
thanx
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
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.
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.
shishacu
Posts
5
Registration date
Wednesday December 19, 2012
Status
Member
Last seen
December 19, 2012
Dec 19, 2012 at 10:36 PM
Dec 19, 2012 at 10:36 PM
i am using 2007
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
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
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
Dec 19, 2012 at 06:55 AM
Its not working