是否有可能有不符合我想要的格式的 excel 高亮单元格(例如日期为 6.2.2021 与所需的 6/2/2021)? [英] Is it possible to have excel highlight cells that don't conform to my desired format (ex. date as 6.2.2021 vs the desired 6/2/2021)?
问题描述
我使用电子表格为我的团队确定工作流程的优先级.某些单元格突出显示取决于我们与循环时间的接近程度.有开始日期、暂定完成日期和出院日期.
I use a spreadsheet to prioritize workflow for my team. Certain cells highlight depending on how close we are to cycle times. There is a start date, a tentative finish date, and a discharge date.
如果出现以下情况，空白放电日期单元格会变为红色:开始日期不为空；暂定不为空；并且今天的日期在暂定日期的五天内.
A blank discharge date cell turns red if: start date is not blank; tentative is not blank; and today's date is within five days of the tentative date.
我的一些团队成员习惯用句点 (mm.dd.yyyy) 写他们的日期.Excel 当然不会将此识别为日期.但它确实知道暂定日期"单元格不是空白的.结果，即使今天的日期等于暂定日期，放电日期"单元格也不会变红.这剥夺了这张表的实用性，因为它旨在提供一目了然的信息，其中一个明显的红色单元格让人们知道，哦，射击！我需要把这项任务作为优先事项."
Some of my team member have the habit of writing their dates with periods (mm.dd.yyyy). Excel of course does not recognize this as a date. BUT it does know that the 'tentative date' cell is not blank. As a result, even if today's date is equal to the tentative date, the 'discharge date' cell does not turn red. This is taking away from the utility of this sheet, as it is intended to be info at a glance, where a glaring red cell lets one know, "Oh shoot! I need to focus on that task as a priority."
如果日期不是所需的格式 (mm/dd/yyyy)，我希望开始日期和暂定日期单元格变为红色.
I'd like for the start date and tentative date cells to go red if the date is not the desired format (mm/dd/yyyy).
S1 中的公式和条件:
Formula and condition in S1:
S1 填充红色如果
=AND($P1-TODAY()<=5,COUNTA($S1)=0,$M1<>0,$P1<>0)
推荐答案
与您可以使用上面提供的公式有条件地将输出格式化为红色一样，您可以有条件地格式化输入(开始日期和暂定日期)，如果它们不符合您的标准.
In the same way that you can conditionally format the output as red with your supplied formula above, you can conditionally format the inputs (start date and tentative date) if they do not match your criteria.
一个想法是使用类似 =SEARCH(".", A1)>0 作为格式标准(如果不正确的输入总是出现在 dd.mm.yyyy，并假设您的范围的起点在单元格 A1 中).
One idea is to use something like =SEARCH(".", A1)>0 as the criteria for the format (if the incorrect input always comes in some variation of dd.mm.yyyy, and assuming the start of your range is in cell A1).
如果您没有创建原始规则，请执行以下步骤:
Here are the steps to do so if you did not create the original rule:
- 突出显示开始日期和暂定日期的范围
- 转到主页->条件格式设置->新规则->使用公式确定要设置格式的单元格"
- 输入上面的公式(确保删除绝对引用)
- 转到格式->填充->选择红色并点击确定
这篇关于是否有可能有不符合我想要的格式的 excel 高亮单元格(例如日期为 6.2.2021 与所需的 6/2/2021)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
