英文:
Excel: Highlighting datetime cell that is greater than now. Dynamic values
问题
我知道您需要在单元格中保留日期以便使用条件格式进行时间评估。我想要将动态日期(今天())与静态小时(我将有多个值:上午9:00、上午10:00、下午1:00)连接起来。
假设A1是今天()
B1是上午9:00
C1的公式是:concat(a1, ' ', b1)
然后我得到这个混乱的结果:45120 0.375
我该如何格式化这些值,使其显示为7/13/2023 5:58PM?
这个想法是创建一个通话列表,我可以动态地突出显示不在通话时间内的时间。
我尝试通过右键单击格式化这些值。似乎不起作用。我开始觉得这可能无法完成。
英文:
I know that you need to keep the date in the cell in order to evaluate time with conditional formatting. I want to concatenate the dynamic date, today(), with a static hour (I will have several values: 9:00 AM, 10:00 AM, 1:00 PM.)
Say, A1 is today()
B1 is 9:00 AM
C1 has the formula: concat(a1,' ',b1)
I then get this mess: 45120 0.375
How do I format the values so it appears as 7/13/2023 5:58PM?
The idea is to create a call list where I can dynamically highlight times that are not within call times.
I tried formatting the values by right clicking. That doesn't seem to work. I'm starting to think it cannot be done.
答案1
得分: 0
日期时间是数字。数字不应该被连接,而应该被相加。
而不是
=CONCAT(A1," ",B1)
尝试
=A1+B1
结果应该是一个数字,你可以按照你的意图进行格式化。
英文:
Datetimes are numbers. Numbers should not be concatenated, they should be added.
Instead of
=CONCAT(A1," ",B1)
try
=A1+B1
The result should be a number that you can format as you intended.
答案2
得分: 0
使用now()函数获取当前日期和时间。
然后,格式化您希望突出显示数据的单元格,
使用“仅格式化数值大于”now()的单元格,并将其着色为您选择的颜色。
英文:
for current date and time, use now() function, which will give you current date, time.
and now format the cell in which you want data to be highlighted,
format this cell with "Format only cell with value greated than" now() and colour it with your colour.enter image description here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论