日付形式をyyyy-mm-ddに変更する

date excel format vba
日付形式をyyyy-mm-ddに変更する

混合形式の日付を含む日付列があります。 例えば:

_
A + 1990年3月21日+ 1990年3月21日
_

したがって、基本的に1つの列には2つの異なる形式があります: dd.mm.yyyy`と mm / dd / yyyy`。 列のすべての日付の形式を「yyyy-mm-dd」に変更するVBAスクリプトを作成しようとしています。 それが私が今までに持っているものです:

Sub changeFormat()

Dim rLastCell As Range
Dim cell As Range, i As Long
Dim LValue As String

i = 1

With ActiveWorkbook.Worksheets("Sheet1")
    Set rLastCell = .Range("A65536").End(xlUp)
    On Error Resume Next
    For Each cell In .Range("A1:A" & rLastCell.Row)
        LValue = Format(cell.Value, "yyyy-mm-dd")
        .Range("B" & i).Value = LValue
        i = i + 1
    Next cell
    On Error GoTo 0
End With

End Sub

私はそれがエレガントなコードではないことを知っていますが、私はVBAの初心者ですのでご容赦ください。 そのコードの問題は、フォーマット関数の引数を「yyyy-mm-dd」から「dd / mm / yyyy」に変更すると、変更されていないA列をB列に書き換えるだけですが、フォーマットの日付のみmm / dd / yyyy`で、 `dd.mm.yyyy`はそのままです。 私は何かアドバイスをいただければ幸いです。

  6  4


ベストアンサー

更新:新しい回答

これが仕事をするソリューションです! サブルーチンには、置換を行う関数が含まれています(関数自体は本当に便利です!)。 サブを実行すると、列Aのすべてのオカレンスが修正されます。

Sub FixDates()

Dim cell As range
Dim lastRow As Long

lastRow = range("A" & Rows.count).End(xlUp).Row

For Each cell In range("A1:A" & lastRow)
    If InStr(cell.Value, ".") <> 0 Then
        cell.Value = RegexReplace(cell.Value, _
        "(\d{2})\.(\d{2})\.(\d{4})", "$3-$2-$1")
    End If
    If InStr(cell.Value, "/") <> 0 Then
        cell.Value = RegexReplace(cell.Value, _
        "(\d{2})/(\d{2})/(\d{4})", "$3-$1-$2")
    End If
    cell.NumberFormat = "yyyy-mm-d;@"
Next

End Sub

この関数を同じモジュールに配置します。

Function RegexReplace(ByVal text As String, _
                      ByVal replace_what As String, _
                      ByVal replace_with As String) As String

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)

End Function

仕組み:正規表現を使用して置換を行うことができる気の利いたRegexReplace関数があります。 サブはA列をほぼループし、あなたが言及した2つのケースに対して正規表現の置換を行います。 私が最初にInstr()を使用する理由は、置換が必要かどうか、およびその種類を決定するためです。 技術的にはこれをスキップできますが、それを必要としないセルで置換を行うことは本当にコストがかかります。 最後に、安全な測定のために中身に関係なく、セルをカスタムの日付形式にフォーマットします。

正規表現に慣れていない場合(参照:http://www.regular-expressions.info/)、私が使用している表現は次のとおりです。

  • ()の各アイテムはキャプチャグループです-別名、混乱させたいもの
    with

  • \ dは数字[0-9]を表します。

  • \ {2}は2 ofを意味し、\ {4}は4 ofを意味します。 私はここで明示的でした
    安全性。

  • 。の前の\ 「。」以降の最初の置換が必要です。持っている
    特別な意味。

  • VBA正規表現では、$ + noを使用してキャプチャグループを参照します。 グループの。
    これは、3つのアイテムの順序を入れ替える方法です。

8


これにはVBAは本当に必要ありません。 次の1行のワークシートの数式がトリックを行います。

=IF(ISERROR(FIND(".",A1)),IF(ISERROR(FIND("/",A1)),"invalid format",
    DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),
    DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)))

これは、日と月が常に2桁の数字として与えられることを前提としています(例: 常に03で、3だけではありません)、年は4桁です(つまり、 1000〜9999年に「制限」されています)。 ただし、これが当てはまらない場合は、目的に合わせて数式を簡単に調整できます。

4


これがあなたの望むことをするかどうか見てください。 独自のアプリケーションに合わせて少し調整する必要がある場合があります。

お役に立てれば!

Sub convertDates()
    Dim rRng As Range
    Dim rCell As Range
    Dim sDest As String
    Dim sYear, sMonth, sDay, aDate

    'Range where the dates are stored, excluding header
    Set rRng = Sheet1.Range("A2:A11")

    'Column name of destination
    sDest = "B"

    'You could also use the following, and just select the range.
    'Set rRng = Application.selection

    For Each rCell In rRng.Cells
        sYear = 99999

        If InStr(rCell.Value, ".") > 0 Then
            aDate = Split(rCell.Value, ".")
            If UBound(aDate) = 2 Then
                sDay = aDate(0)
                sMonth = aDate(1)
                sYear = aDate(2)
            End If
        ElseIf InStr(rCell.Value, "/") > 0 Then
            aDate = Split(rCell.Value, "/")
            If UBound(aDate) = 2 Then
                sDay = aDate(1)
                sMonth = aDate(0)
                sYear = aDate(2)
            End If
        End If

        With rCell.Range(sDest & "1")
            If sYear <> 99999 Then
                On Error Resume Next
                .Value = "'" & Format(CDate(sMonth & "/" & sDay & "/" & sYear), "YYYY-MM-DD")
                'If it can't convert the date, just put the original value in the dest
                'cell. You can tailor this to your own preference.
                If Err.Number <> 0 Then .Value = rCell.Value
                On Error GoTo 0
            Else
                .Value = rCell.Value
            End If
        End With
    Next
End Sub

3


@Jean-FrançoisCorbettには機能する式のソリューションがありますが、エラーメッセージ(「#VALUE!」は情報として)に基づいて半数以上短縮される可能性がありますISERROR、および1つのLEFT、MID、RIGHTセットの代わりのSUBSTITUTE:

=IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,".","/"))

これは、OPへのコメントで@Issunによって言及された解釈を適用し、出力が「yyyy-mm-dd」でフォーマットされたセルになることを想定しています。

次のようなサブルーチンを使用して記述できます。

Sub Macro1()
Range("B1:B10").Formula = "=IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,""."",""/""))"
End Sub

1


これに対する簡単な解決策は次のとおりです。

     Sub ChangeFormat1()

              Dim ws as Worksheet
                 Set ws = Thisworkbook.Sheets("Sheet1")
                 LR = ws.cells(rows.count,1).End(Xlup).Row

                 with ws
                        For I = 1 to LR
                          .cells(I,2).value = .cells(I,1).value
                        Next
                 End with

                 ws.range("B1:B" & LR).numberformat = "yyyy-mm-dd"
      End Sub

0


月(日付)& “-“&日(日付)& “-“&年(日付)

-1


タイトルとURLをコピーしました