Excel自定义DateTime格式化以删除时间，如果不是时间为零（00:00:00）

I am looking for custom DateTime format which allows me to remove the time for dates where time is zero (00:00:00). The reason for this is that I have some sheets with many DateTime columns, some of these columns time is essential and come with it, whereas others time is not necessary and comes as 00:00:00.

我正在寻找自定义DateTime格式,它允许我删除时间为零(00:00:00)的日期。这样做的原因是我有一些带有许多DateTime列的工作表,其中一些列时间是必不可少的并且随之而来,而其他时间则不是必需的,并且为00:00:00。

Sample:
Date/Time               Desired result
====================    =====================
22/05/2017 08:30:15     22/05/2017 08:30:15
23/05/2017 00:00:00     23/05/2017
24/05/2017 00:00:00     24/05/2017
25/05/2017 08:30:15     25/05/2017 08:30:15

The idea is to apply this custom format to all Date/Time columns.

我们的想法是将此自定义格式应用于所有日期/时间列。

Thanks

1 个解决方案

#1

Unless you are dealing with an older version of Excel, you can do this with Conditional Formatting.

除非您正在处理旧版本的Excel,否则可以使用条件格式执行此操作。

Format the cells as dd/mm/yyyy hh:mm:ss

将单元格格式化为dd / mm / yyyy hh:mm:ss

Then set up conditional formatting with a formula
- =A2=INT(A2)
- Format ► Number ► Custom ► dd/mm/yyyy

然后使用公式设置条件格式= A2 = INT(A2)格式►数字►自定义►dd/ mm / yyyy

#1