优化日期转换循环 [英] Optimizing a date conversion loop

问题描述

我有一组数据（n），大约1500项长，用excel无法识别的DD.MM.YYYY格式填充了日期。我的目标是将它们更改为excel可以使用的东西，这就是我的工作方式。

I have a set of data (n), about 1500 items long filled with dates in DD.MM.YYYY format that excel does not regognize. My goal is to change them to something that excel can work with, this is how I do it.

Function date_to_excel()
Call public_dims

Dim date_i As String
date_array = ThisWorkbook.Sheets("Spread").Range(Cells(7, 5), Cells(7 + n, 5))

For i = 0 To n
date_i = ThisWorkbook.Sheets("Spread").Cells(7 + i, 5)
    If date_i <> "" Then
    date_array = Split(date_i, ".")
    date_i = date_array(1) & "/" & date_array(0) & "/" & date_array(2)
    ThisWorkbook.Sheets("Spread").Cells(7 + i, 5) = date_i
    End If
Next

End Function

该函数可以正常运行，但是只需要很长时间。 社区中我要问的是关于如何优化此循环的想法。我尝试将带有日期的整个范围添加到Array中并进行仔细检查，但似乎与我的建议不兼容更改日期格式的方法（date_i =末尾的行）。

The function works allright, but it just takes a really long time. What I am askingfrom the community is for ideas on how to optimize this loop. I have tried adding the entire range with the dates into an Array and looking through that, but it doesn't seem compatible with my method of changing the date format (the date_i = line near the end).

推荐答案

您可以在DMY中使用文本到列功能列格式。

You can use Text To Columns functionality with DMY column format.

ThisWorkbook.Sheets("Spread").Range("E7:E" & 7 + n).TextToColumns _
                                DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)

这篇关于优化日期转换循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！