Форматирование даты Excel

У меня есть большой импортированный файл csv, содержащий американские даты в столбце. Я хочу изменить эти даты в формате Великобритании (dd/mm/yyyy) с помощью кода. Проблема заключается в том, что даты в США относятся как к формату "mm/dd/yyyy", так и к "m/dd/yyyy", последний из которых не отвечает ни на один из вариантов "clickable" в excel, включая процедуру "Text to Columns". Есть ли код, который при запуске в соседнем столбце будет выполнять преобразование?

Ответы

Ответ 1

Вы также можете легко использовать функцию TEXT() (Примечание: исходные данные должны быть значением даты для Excel)

TEXT(value, format_text)

где value - эталонная ячейка, а format_text - это то, как вы хотите отформатировать текст в вашем случае dd/mm/yyyy.

Предполагая, что:

A1 = 3/17/2013
A2 = 12/27/2013

В B1 и B2 просто введите:

B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")

и результат должен быть

     A              B
3/17/2013      17/03/2013
12/27/2013     27/12/2013

Надеюсь, что это поможет.

ОБНОВЛЕННОЕ ПРЕДЛОЖЕНИЕ ПРИ РАБОТЕ С ТЕКСТОМ:

Разделите строку, используя функции mid(), left() и right(), затем проверьте, не превышает ли длина месяца 1 или 2 символа с помощью функции LEN(). Наконец, объедините строку вместе с помощью операторов and и /.

Попробуйте вставить это в B1, он должен работать нормально:

=MID(A1,FIND("/",A1,1)+1,2)&"/"&IF(LEN(LEFT(A1,FIND("/",A1)-1))=1,0&LEFT(A1,FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))&"/"&RIGHT(A1,4)

Ответ 2

Вы можете использовать функцию DATEVALUE для преобразования строк даты в значения данных Excel, а затем создать пользовательский формат даты для отображения значений даты в формате U.K.

Предполагая, что первая строка даты находится в ячейке A1, следующий вызов функции превратит строку в значение даты.

  =DATEVALUE(A1)

Вы можете создать пользовательский формат dd/mm/yyyy;@, щелкнув правой кнопкой мыши на ячейке, выбрав "Формат ячеек", "Номер", "Пользовательский" и введите формат в поле "Тип".

Работа с значениями даты вместо строк даты позволит вам изменить отображаемый формат без необходимости выполнять строковые операции для изменения элементов даты. Это также позволит сделать арифметику дат, если это возникнет.

Ответ 3

Вот что может вам помочь. Используя метод QueryTables, вы можете указать, что Excel интерпретирует определенные столбцы данных как строку.

Sub ImportExternalCSVAsText()
'
Dim sFileName As String
sFileName = Application.GetOpenFilename("CSV files, *.csv")

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & sFileName _
        , Destination:=Range("$A$1"))
        .Name = "CSV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 2) '2 denotes STRING
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

В этом примере я импортировал CSV файл 3 столбца шириной и указывал, что столбцы 2 и 3 содержат данные типа String.

.TextFileColumnDataTypes = Array(1, 2, 2) '2 denotes STRING

Если этот метод не работает, сообщите мне. Я должен был решить эту проблему раньше (как подрядчик США для контрактной работы в Великобритании), и я уверен, что у меня есть несколько функций, которые я использовал в то время.

Это приведет все значения в строку.

Вам, скорее всего, придется разбирать их, потому что входы данных в США, такие как "12/30/2011", не смогут выполнить функцию DATEVALUE() в вашей системе Locale, а входы данных в США, такие как "07/05/2012" (5 июля 2012 года) будет интерпретироваться как "7 мая 2012 года" по вашему языку.

Ответ 4

Эта функция должна выполнить трюк:

=CONCATENATE(MID(B2,SEARCH("/", B2,1)+1,2), "/",LEFT(B2,SEARCH("/", B2,1)-1), "/", RIGHT(B2,4))

Ответ 5

Как ни странно, для этого вам нужно преобразовать один столбец текста в один столбец текста.

Выберите столбец, содержащий ваши даты: mm/dd/yyyy

  • В разделе "Данные" выберите "Текст в столбцы"
  • Выберите фиксированную ширину
  • Далее
  • Установите разделитель ширины столбца после года (гггг)
  • В формате данных столбца
  • Выпадающий выберите mm/dd/yyyy (исходный формат)
  • Выберите пункт назначения
  • Готово

Исходные данные преобразуются в формат даты вашей системы.

Sub USUKDateFormat()

    ' USUKDateFormat Macro

    Selection.TextToColumns Destination:=ActiveCell.Offset(0, 2).Range("A1"), _
        DataType:=xlFixedWidth, OtherChar:="/", FieldInfo:=Array(Array(0, 4), Array _
        (10, 1)) 
End Sub