Excel VBA - 从格式不正确的日期提取正确的日期？ [英] Excel VBA - Extract the correct dates from badly formatted dates?

问题描述

我正在学习VBA编程，并遇到下面的情况，我将不胜感激你的帮助。理想情况下，不仅仅是找到一个解决方案，而且还要了解解决方案的工作原理。为什么这个解决方案可以运行。

假设有一个数据库可以导出数据的电子表格。其中一列具有日期值，但导出格式不正确。系统发送日期为mm / dd / yyyy hh：mm AM / PM，例如 04/11/2014 09:24 AM ，但电子表格具有此标识作为dd / mm / ...，表示它作为日期输入04，而作为月份输入11。

在此列中，如果日期在之前或之前包含12该月份，单元格格式化为日期。如果这天是十二号，那么这个单元格格式化一般的格式。

我的问题是，我可以编写一个VBA宏，月，并在新列中创建正确的日期？我会认为它首先要确定一个单元格是否格式化为日期，然后以某种方式将日期和月份提取到正确的位置，或者如果格式化为一般格式，然后使用其他代码来提取正确的日期。

如果这对于这个社区来说太过于基础，还有另一个社区更适合，我将乐意转发我的问题。

编辑：

在下面的评论之后，我发挥了功能，并寻找可能有助于其他类似功能我需要什么，用日期值切换日期值，到目前为止我有：

 '一般格式的日期： 04/14/2014 11:20 AM 
 = DATE（MID（A1,7,4），LEFT（A1,2），MID（A1,4,2））'在日期的列中
 = TIME（MID（A1,12,2），MID（A1,15,2），00）'，因为我可能需要这个
 
'日期格式为：4/11/2014 7:35:00 PM 
 = DATE（TEXT（A1，yyyy），TEXT（A1，日期的列中的
 = TEXT（A1，hh：mm AM / PM）'的列中的文本（，mm）），时间
  

现在我只需要找出一个条件函数来确定何时根据值或格式或列应用每组公式A。

但是有通过VBA实现此功能的等效功能吗？我需要这些日期和时间列才能保留值而不是公式，以便我可以直接从中导出数据。不管怎么说，将这个在VBA代码看起来对我来说更加干净，使用公式让我感觉到像一个挥发性的解决方案。我不知道如何正确地解释这个问题，但是我在某种程度上比较适合使用数据操作的编码。

EDIT2：

我已经解决了下面的工作表函数解决方案。花了我一段时间来弄清楚如何使用日期格式化的单元格查找FIND错误，并且只有在列表Excel中偶然发现IFERROR函数时才会写出= IF。

 '以获取正确的日期
 = IF（IFERROR（FIND（/，A1），0）> 0，DATE（MID（A1,7， 4），LEFT（A1,2），MID（A1,4,2）），DATE（TEXT（A1， YYYY），TEXT（A1， DD），TEXT（A1中， mm））） 
'得到正确的时间
 = IF（IFERROR（FIND（/，A1），0）> 0，TIME（MID（A1,12,2），MID ，2），00），TEXT（A1，h：mm AM / PM））
  

现在至少我有一个工作的解决方案，但我仍然对这些公式的VBA翻译感兴趣，并将继续搜索这些公式。

解决方案

查看这个。让我们以你的公式为例：

= IF（IFERROR（FIND（/，A1），0）> 0，DATE MID（A1,7,4），LEFT（A1,2），MID（A1,4,2）），DATE（TEXT（A1， YYYY），TEXT（A1， DD），TEXT（A1， mm）））

VBA等效功能

Find = Instr

日期= DateSerial

文本=格式（不完全相同但最近的）

代码等效：

  Dim mydate As Date 
 Dim myformat As String 
 
 myformat =mm / dd / yyyy hh：mm AM / PM
如果InStr（1，[A1]，/）> 0然后
 mydate = DateSerial（Mid（Format（[A1]，myformat），7，4），_ 
 Left（Format（[A1]，myformat））2） A1]，myformat），4，2））
 Else 
 mydate = DateSerial（年（[A1]），月（[A1]），日（[A1]））
结束如果
 [B1] = mydate 
  

请注意 [A1 ] 是一个快捷方式评估函数，也可以写成 Evaluate（A1）。

我用它来代替单元格A1，就像你的公式一样。您可以使用常规的 Range Object 引用： Range（A1）。我使用捷径，因为它看起来更干净。但是在巨大的数据集中是不可取的。

对于你的时间公式：

= IF（IFERROR（FIND（ /，A1），0）大于0，TIME（MID（A1,12,2），MID（A1,15,2）， 00），TEXT（A1， h：mm AM / PM））

代码等效：

  Dim mytime As Date 
如果InStr（1，[A1]，/）> 0然后
 mytime = TimeValue（[A1]）$ ​​b $ b Else 
'~~> myformat被声明高于
 mytime = TimeValue（格式（[A1]，myformat））
如果
 [C1] = mytime 
  

您还可以检查单元格的格式如下：

 选择案例True 
案例[A1] .NumberFormat =常规
 mydate = DateSerial（年（[A1]），月（[A1]），日（[A1]））
 mytime = TimeValue（格式（[A1]，myformat））
案例[A1] .NumberFormat = myformat'~~>再次，这被声明在
 mydate = DateSerial（Mid（Format（[A1]，myformat）），7，4），_ 
 Left（Format（[A1]，myformat）），2），Mid格式（[A1]，myformat），4，2））
 mytime = TimeValue（[A1]）$ ​​b $ b Case Else 
 MsgBox无效的格式无法评估
结束选择
 [B1] = mydate：[C1] = mytime 
  

真正解决您的问题。

从数据库中提取日期戳记时，只有很多可能性。

如果您提到的方案只是您遇到的问题，则上述解决方案可能会工作。

I am currently learning VBA programming by doing, and have encountered the below situation with which I would appreciate your help. Ideally not just in finding a solution, but also to understand how and why the solution works.

Say that there is a database from which one can export a spreadsheet of data. One of the columns has date values, but they are badly formatted from the export. The system sends the dates as mm/dd/yyyy hh:mm AM/PM, for example, 04/11/2014 09:24 AM, but the spreadsheet has this identified as dd/mm/..., meaning it enters 04 as the day and 11 as the month.

Within this column, if the day is before or including 12 of the month, the cell is formatted as date. If the day is past the 12th, the cell is formatted with a general format.

My question is, could I write a VBA macro that could reverse the values for day and month and create the correct dates in a new column? I would think that it would first have to identify if a cell is formatted as date, and then somehow extract the date and month in the correct positions, or if it's formatted as a general format, and then use a different code to extract the correct date.

If this is too basic an issue for this community and there's another community more suited, I will gladly repost my question there.

EDIT:

After my comment below I played around with functions and looked for other similar functions that may help do what I need, switch the day value with the month value, and so far I have:

'for dates with general format: 04/14/2014 11:20 AM
=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)) 'in a column for the date
=TIME(MID(A1,12,2),MID(A1,15,2),"00") 'in a column for time, since I may need this

'for dates with a date format: 4/11/2014  7:35:00 PM
=DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")) 'in a column for the date
=TEXT(A1,"hh:mm AM/PM") 'in a column for time

Now I just need to figure out a conditional function to identify when to apply each set of formulas according to the values or formatting or column A.

But are there equivalent functions to achieve this through VBA? I need these date and time columns to only hold values, not formulas, so that I may export the data out of them directly. And somehow putting this in VBA code seems more "clean" to me, using formulas feels to me like a volatile solution. I'm not sure how to explain this properly, but I'm somehow more confortable with proper coding behind my data manipulation.

EDIT2:

I've resolved the worksheet functions solution as below. It took me a while to figure out how to go around the FIND error with date formatted cells, and only found the IFERROR function by chance in the list Excel suggests when writing =IF.

'to get the correct date
=IF(IFERROR(FIND("/",A1),0)>0,DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)),DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")))
'to get the correct time
=IF(IFERROR(FIND("/",A1),0)>0,TIME(MID(A1,12,2),MID(A1,15,2),"00"),TEXT(A1,"h:mm AM/PM"))

Now at least I have a working solution, but I'm still interested in a VBA translation for these formulas and will continue searching for these.

解决方案

Check this out. Let's take for example your formula:

=IF(IFERROR(FIND("/",A1),0)>0,DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)),DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")))

VBA equivalent functions:

Find = Instr
Date = DateSerial
Text = Format (not exactly the same but the nearest)

Code equivalent:

Dim mydate As Date
Dim myformat As String

myformat = "mm/dd/yyyy hh:mm AM/PM"
If InStr(1, [A1], "/") > 0 Then
    mydate = DateSerial(Mid(Format([A1], myformat), 7, 4), _
        Left(Format([A1], myformat), 2), Mid(Format([A1], myformat), 4, 2))
Else
    mydate = DateSerial(Year([A1]), Month([A1]), Day([A1]))
End If
[B1] = mydate

Take note that [A1] is a shortcut Evaluate function which can also be written as Evaluate("A1").
I used that to refer to Cell A1 as in your formula. You can use the conventional Range Object reference like this: Range("A1"). I used the shortcut because it looks cleaner. But it is not advisable in huge data sets.

For your time formula:

=IF(IFERROR(FIND("/",A1),0)>0,TIME(MID(A1,12,2),MID(A1,15,2),"00"),TEXT(A1,"h:mm AM/PM"))

Code Equivalent:

Dim mytime As Date
If InStr(1, [A1], "/") > 0 Then
    mytime = TimeValue([A1])
Else
    '~~> myformat is declared above
    mytime = TimeValue(Format([A1], myformat))
End If
[C1] = mytime

You can also check the format of the cell like below:

Select Case True
Case [A1].NumberFormat = "General"
    mydate = DateSerial(Year([A1]), Month([A1]), Day([A1]))
    mytime = TimeValue(Format([A1], myformat))
Case [A1].NumberFormat = myformat '~~> again this is declared above
    mydate = DateSerial(Mid(Format([A1], myformat), 7, 4), _
        Left(Format([A1], myformat), 2), Mid(Format([A1], myformat), 4, 2))
    mytime = TimeValue([A1])
Case Else
    MsgBox "Invalid Format. Cannot be evaluated"
End Select
[B1] = mydate: [C1] = mytime

Not sure if above will really solve your problem.
There are just many possibilities when you extract datetime stamp from a database.
If the scenarios you mentioned are only the problems you encounter, then above solutions might work.

这篇关于Excel VBA - 从格式不正确的日期提取正确的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！