在相邻单元格中找到的日期之后的十个工作日(不包括节假日)突出显示相邻单元格 [英] Highlighting adjacent cell ten business days (exclude holidays) after the date found in the cell beside it
问题描述
这几天我一直在尝试使用条件格式来解决这个问题，但无济于事
I have been trying to work on this with conditional formatting for a couple of days now to no avail
我已经做到了，每次输入文件编号"时，右侧的灰色单元格会显示它在 dd-Mmm-yy 中输入的日期(例如，文件编号是 C8 日期是在 D8 中输入的).
I have made it so that every time a "FILE NUMBER" is entered the cell to the right in grey shows the date it was entered in dd-Mmm-yy (ex. file number is C8 date is entered in D8).
现在我需要的是一个宏或条件格式，它会在 10 ***business 时以红色突出显示Motion"(B、E...)和FILE NUMBER"(C、F...)单元格/工作日已从在相应灰色单元格(D、G、J...)中输入的初始日期开始.
Now what I need is a macro or conditional formatting that will highlight the "Motion" (B, E...) and "FILE NUMBER" (C, F...) cells in red when 10 ***business/work days have passed from the initial date entered in the corresponding grey cell (D, G, J...).
我想使用 WORKDAY，但单元格是在随机日期填充的，所以我不知道开始日期是什么.这不是一个有持续时间的项目.我想今天使用，但它不包含工作日.
I wanted to use WORKDAY, but the cells are filled at random dates so I don't know what the start date would be. It's not a project with a duration. I want to use today, but it doesn't incorporate workdays.
请帮忙！谢谢！
推荐答案
如果你有你想在 D8 中使用 time=0 的日期，你要格式化的单元格是C8，然后选择 C8，并添加 条件格式 -> 使用公式确定要格式化的单元格并输入公式 =TODAY() >工作日(D8,10).
If you have the date you want to use as time=0 in D8,
and the cell you want to format is C8,
then select C8, and add a Conditional Formatting -> Use a formula to determine which cells to format
and enter the formula =TODAY() > WORKDAY(D8,10).
注意事项:
在条件格式中使用相对引用(例如，D8 代替 $D$8)允许在条件格式中指定的扩展范围使用相同的条件em> -> 管理规则 -> 适用于.根据您的需要，您可能需要为列 ($D8)、行 (D$8) 或两者($D$8；不太可能)选择绝对引用.您可能还需要定义几个条件格式.检查 这 和 这个.
Using relative references in the conditional formatting (e.g., D8 instead of $D$8) allows for using the same condition for an extended range, specified under Conditional Formatting -> Manage Rules -> Applies To. You might need to select absolute referencing for columns ($D8), rows (D$8) or both ($D$8; unlikely), depending on your needs. You may also need to define several Conditional Formats. Check this and this.
您可能需要更改数字-10.
这篇关于在相邻单元格中找到的日期之后的十个工作日(不包括节假日)突出显示相邻单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
