如何在Excel中检查日期格式 [英] How to check date format in Excel
问题描述
我在一个Excel文件中有大约20,000条记录,在大约四列中有日期。我正在尝试将这些插入SQL。但是,日期列的日期格式不正确,例如; 02/092015
或 02/90/2015
或 2015
。因此,一一检查20,000条记录将非常漫长。
I have around 20,000 records in an Excel file and around four columns which have dates. I am trying to insert those into SQL. However date columns have dates in incorrect format eg; 02/092015
or 02/90/2015
or 2015
. So checking 20,000 records one by one would be very lengthy.
我试图计算 /
,但是没有用。它将列的格式更改为日期。
I tried to count /
but it didn't work. It changes the format of column to date.
我在寻找可以检查格式并可能为单元格或类似颜色着色的公式。
I was looking for some formula which can check the format and maybe color the cell or something like it.
推荐答案
我今天遇到了这个问题,想补充一下nekomatic是什么。
I was running across this issue today, and would like to add on to what nekomatic started.
在我们开始之前, TEXT
公式必须遵循我们正在使用的日期格式。如果您的日期采用月/日/年格式,则 TEXT
公式的第二个参数将是 mm / dd / yyyy。如果采用日/月/年的格式,则公式将需要使用 dd / mm / yyyy。为了在这里回答我,我将以月/日/年的格式显示日期。
Before we begin, the TEXT
formula needs to follow the date format we are working with. If your dates are in month/day/year format, then your second argument for the TEXT
formula would be "mm/dd/yyyy". If it is in the format day/month/year, then the formula would need to use "dd/mm/yyyy". For the purposes of my answer here, I am going to have my dates in month/day/year format.
现在,让我们假设单元格A1包含值 2015年12月1日
,单元格A2包含值 monkey
,单元格A3包含值 2015
。此外,假设我们的最小可接受日期是2000年12月1日。
Now, let's assume that cell A1 contains the value 12/1/2015
, cell A2 contains the value monkey
, and cell A3 contains the value 2015
. Further, let's assume our minimum acceptable date is December 1st, 2000.
在B列中,我们将输入公式
In column B, we will enter the formula
= IF(ISERROR(DATEVALUE(TEXT(A1, mm / dd / yyyy)))),不是日期,IF(A1> = DATEVALUE(TEXT( 01 / 01/2000, mm / dd / yyyy)),A1,无效日期))
上面的公式将验证正确的日期,对非日期值,不完整的日期以及日期值在可接受的最小值范围内进行测试。
The above formula will validate correct dates, test against non-date values, incomplete dates, and date values outside of an acceptable minimal value.
然后,我们在B列中的结果应显示 2015/12/1
,不是日期
和不是有效日期
。
Our results in column B should then show 12/1/2015
, not a date
, and not a valid date
.
这篇关于如何在Excel中检查日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!