Forum Discussion
Formula Assistance
I would like to point out that without minimal information from the beginning about the Excel version, operating system and storage medium, it is a guessing game that not everyone has the time to invest in. Without this minimal information it takes a lot of useless time.
Welcome to your Excel discussion space!
Anyway…
It sounds like you are encountering some limitations with conditional formatting in Excel Online (OneDrive).
Unfortunately, Excel Online does have some differences and limitations compared to the desktop version of Excel.
Conditional formatting in Excel Online is more limited in terms of the types of rules you can create compared to the desktop version. However, you should still be able to achieve your desired outcome with a formula-based rule.
Here is how you can adjust the formula for conditional formatting in Excel Online:
- Select the range of cells in column B where you want the conditional formatting to apply (e.g., B2:B100).
- Click on "Conditional Formatting" in the toolbar.
- In the dropdown menu, select "New Rule".
- Choose "Use a formula to determine which cells to format".
- Enter the following formula in the formula box:
=AND(NOT(ISBLANK(B2)), B2-TODAY()<=2, B2-TODAY()>=0, C2<>"Completed")
- Choose the desired formatting options (e.g., red background color).
- Click "Done" or "Apply".
This adjusted formula should work within the limitations of Excel Online and still achieve the desired result of highlighting cells within 2 days of the Date Required date, unless the corresponding Job Status cell contains the word "Completed".
If you continue to experience issues or if the provided solution doesn't meet your needs due to the limitations of Excel Online, you may need to consider alternative approaches or using the desktop version of Excel for more advanced conditional formatting.
Hi NikolinoDE
Thank you very much for your assistance. I have implemented your clear instructions. The automatic filling of the Date Required column is working however the Conditional Formatting isn't, ie. the cell isn't turning red when I'm within 2 days of the Date Required date being due. Looking into it further and your example I note that I'm using Excel in One Drive and your example appears to be not in One Drive. When I go to Conditional Formatting in Excel OneDrive I only see a few Rule Types (see examples below) so I chose Formula and then put the rule you gave me into the box provided. When I open the example you sent me on my desktop you see a different menu than you do. I'm wondering if this is the problem???? I have to use Excel via One Drive as this is a shared workbook.
I hope you can help me.
Thanks NikolinoDE.
Kind regards
Kim
I see this using Excel via One DriveI see this using Excel via One DriveYou see this
I would like to point out that without minimal information from the beginning about the Excel version, operating system and storage medium, it is a guessing game that not everyone has the time to invest in. Without this minimal information it takes a lot of useless time.
Welcome to your Excel discussion space!
Anyway…
It sounds like you are encountering some limitations with conditional formatting in Excel Online (OneDrive).
Unfortunately, Excel Online does have some differences and limitations compared to the desktop version of Excel.
Conditional formatting in Excel Online is more limited in terms of the types of rules you can create compared to the desktop version. However, you should still be able to achieve your desired outcome with a formula-based rule.
Here is how you can adjust the formula for conditional formatting in Excel Online:
- Select the range of cells in column B where you want the conditional formatting to apply (e.g., B2:B100).
- Click on "Conditional Formatting" in the toolbar.
- In the dropdown menu, select "New Rule".
- Choose "Use a formula to determine which cells to format".
- Enter the following formula in the formula box:
=AND(NOT(ISBLANK(B2)), B2-TODAY()<=2, B2-TODAY()>=0, C2<>"Completed")
- Choose the desired formatting options (e.g., red background color).
- Click "Done" or "Apply".
This adjusted formula should work within the limitations of Excel Online and still achieve the desired result of highlighting cells within 2 days of the Date Required date, unless the corresponding Job Status cell contains the word "Completed".
If you continue to experience issues or if the provided solution doesn't meet your needs due to the limitations of Excel Online, you may need to consider alternative approaches or using the desktop version of Excel for more advanced conditional formatting.
- Thank you NikolinoDE. This was the first time I've used the Community Hub for assistance and it certainly wasn't my intention to waste anyone's time. I honestly thought that Excel would be the same whether it be desktop or online. So my apologies for not providing full information at the time. I'll know for next time. Thank you for providing me with a new solution which I'm pleased to say has worked. I'm very grateful. All the best Kim