确保Excel中单元格内的有效日期 [英] Ensure a valid date inside the cell in Excel
问题描述
我正在为Excel 2010准备一张信用卡交易清单,以进行转移和进一步分析(个人需求)。问题是我的银行业机构不遵循标准的日期格式,所以有时候,日期将会以$ code为单位。 1,2013 ,有时作为 2013年5月27日
。第一个不被Excel识别为日期,因此其数据透视不能(月,季等)组。列表中没有其他无法识别的日期事件。
有没有办法确保列中始终有效的日期? / p>
如果我是在.NET编码,一个选项将使用 ParseExact 格式字符串 MMM。 d,yyyy
。
另一个选项是执行如果
,转换任何字符串到如下日期:
如果不是类型MyCell是DateTime然后_
MyCell.Value = CDate (MyCell.Value.Substring(0,3)&
MyCell.Value.Substring(3))
(这是VB.NETish伪语法,希望它有助于解释我的意图)。它假定一个非有效的日期将始终是一个字符串,其中有一个点,由转换器删除 - 我可以。
如何在Excel中执行此操作?寻找最简单的方法。
假设您的日期是 A1
,此公式将转换为时间序列格式:
= DATEVALUE(SUBSTITUTE(A1,。,))
然后,您可以将其格式化为任何
另一个选项是使用这个UDF:
函数MorphDate(strDate As String)As Date
Application.Volatile
如果VarType(strDate)<> 8然后退出函数
Res = Replace(strDate,。,)
MorphDate = Res
结束函数
使用如下: = MorphDate(A1)
,并将其格式化为任何您想要的格式。
截图:
I am trying to prepare a list of credit card transactions for pivoting and further analysis in Excel 2010 (for personal needs). Problem is that my banking institution does not follow a standard date format, so sometimes dates will be coming as Jun. 1, 2013
, and sometimes as May 27, 2013
. The first is not recognized as a date by Excel, so its Pivot cannot Group on that (month, quarter etc.). There are no other unrecognized date occurrences in the list.
Is there a way to ensure there is always a valid date in a column?
If I were coding in .NET, one option would be use ParseExact with a format string MMM. d, yyyy
.
Another option is to do an If
, to convert any string to a date in a something like:
If Not TypeOf MyCell Is DateTime Then _
MyCell.Value = CDate(MyCell.Value.Substring(0,3) &
MyCell.Value.Substring(3))
(This is VB.NETish pseudo-syntax, hope it helps explain my intention). It assumes that a non-valid date will always be a string with a dot inside it, to be removed by the converter - which I am okay with.
How would I do this in Excel? Looking for the most simple approach.
Assuming your date is in A1
, this formula will convert it to time-serial format:
=DATEVALUE(SUBSTITUTE(A1,".",""))
You can then format it to whatever format you want.
Another option is to use this UDF:
Function MorphDate(strDate As String) As Date
Application.Volatile
If VarType(strDate) <> 8 Then Exit Function
Res = Replace(strDate, ".", "")
MorphDate = Res
End Function
Use like so: =MorphDate(A1)
, and format it to whatever format you want.
Screenshots:
这篇关于确保Excel中单元格内的有效日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!