英文:
How to conditional format a cell when the time in the cell is 15 minutes past the hour?
问题
我被委派创建一个电子表格，用于组织和可视化员工的工作效率。一个具体的要求是，我要创建一个公式，用于突出显示包含员工打卡时间的两列单元格。
我想要做一个条件格式，如果员工的打卡时间在所发生的小时之后的15分钟内，就会突出显示单元格为浅红色。
例如，如果时间是07:18或09:22，都会突出显示单元格，因为它们都是打卡时间所在小时之后的15分钟。
不幸的是，我无法提供电子表格，因为其中包含敏感的员工数据。希望这些信息足以清楚地表达我的问题。
我首先尝试使用大于或小于的语句，但很快意识到这不一定适用，因为名单上的员工在不同的时间打卡和下班。如果他们都需要在同一时间打卡，这将变得非常容易。
我现在知道必须使用公式进行条件格式设置，但是在处理Excel公式时，我并不是很擅长。
英文:
I've been tasked with creating a spreadsheet that organizes and visualizes employee productivity. One specific request was that I create a formula that will highlight cells on two columns that contain employee clock-in and clock-out times.
I want to do a conditional format that will highlight the cell in light red if the employees clock-in time is 15 minutes past the hour it occurred.
For example, highlight the cell if the time is 07:18, or 09:22, because they are both 15 minutes past the hour the clock-in occurred.
Unfortunately, I cannot provide the spreadsheet as it contains sensitive employee data. Hopefully, this is adequate information to get my questions across clearly.
I first begun by using greater than or less than statements but quickly realized that wouldn't necessarily work because employees on the list clock-in and out for their shifts at different times. If they all were required to clock-in at the same time, it would make this astronomically easier.
I now know it must be a conditional format using formula, however, I'm not the greatest when it comes to excel formulas.
答案1
得分: 1
使用 MINUTE。假设您的时间戳在 A1 中：
=MINUTE(A1)>15
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论