Формула Excel для преобразования форматов даты

Question

Предполагая, что у меня есть ячейки с текстовыми значениями, упомянутыми как:

02/23/15 to 03/01/15

Могу ли я использовать функции Excel, чтобы преобразовать это в нечто вроде

23-Feb-15 to 1-Mar-15

До сих пор я смотрел на формулу DATEVALUE но это не похоже на решение моей конкретной проблемы.

Я думаю, что я ищу: как мне указать формат даты (скажем, «дд / мм / гг») для строки (скажем, «16.02.15») и преобразовать ее в строку даты в другом формате ( скажи "дд-ммм-гг")

Answer 1

Вот формула, которая работает для вашего примера, независимо от ваших настроек локали:

=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & " to " & TEXT(DATE(RIGHT(A1,2),MID(A1,13,2),MID(A1,16,2)),"dd-mmm-yy")

Вот тот, который работает независимо от того, что находится между датами, если даты имеют формат mm/dd/yy и в самом начале и конце:

=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & MID(A1,9,LEN(A1)-16) & TEXT(DATE(RIGHT(A1,2),MID(A1,LEN(A1)-7,2),MID(A1,LEN(A1)-4,2)),"dd-mmm-yy")

---

В зависимости от вашей ситуации этот другой вариант может быть полезен или не полезен. Вы можете изменить формат даты по умолчанию для системы. Это влияет на функции даты в Excel, так что они ожидают, что даты будут в этом формате. После этого вы можете просто использовать функцию DATEVALUE . Если это одноразовая операция, я бы предложил изменить формат, запустить формулы и вернуть его обратно. В противном случае, я бы пошел с формулой. Вы можете получить все подробности о том, как изменить настройки системы здесь . (Я не копировал и не вставлял все это, потому что это долго .)

Answer 2

Вы можете попробовать это (я использую формат даты Великобритании)

=CONCATENATE(TEXT(LEFT(A1,8), "dd-mmm-yy")," to ",TEXT(RIGHT(A1,8), "dd-mmm-yy"))

Как вы можете видеть, он ожидает, что форматы даты будут иметь фиксированную длину (8 символов, например, мм / дд / гг или / дд / мм / гг).

---

Поскольку вышеприведенное не работает, вы можете попробовать VBa, который будет считывать текущее значение в ColA и вставлять новое значение в ColB

Option Explicit
Sub YikesThereBePirates()

Dim row As Integer
row = 1

Do While Range("A" & row).Value <> ""

    Dim splitty() As String
    splitty = Split(Range("A" & row).Value, " to ")

    Dim newFirstDate As String
    Dim newSecondDate As String

    newFirstDate = GetDate(splitty(0))
    newSecondDate = GetDate(splitty(1))

    Range("B" & row).Value = newFirstDate & " to " & newSecondDate

row = row + 1
Loop


End Sub

Function GetDate(d As String) As String

Dim splitty() As String
splitty = Split(d, "/")
Dim mnth As String

Select Case (splitty(0))
    Case "01"
    mnth = "Jan"
    Case "02"
    mnth = "Feb"
    Case "03"
    mnth = "Mar"
    Case "04"
    mnth = "Apr"
    Case "05"
    mnth = "May"
    Case "06"
    mnth = "Jun"
    Case "07"
    mnth = "Jul"
    Case "08"
    mnth = "Aug"
    Case "09"
    mnth = "Sep"
    Case "10"
    mnth = "Oct"
    Case "11"
    mnth = "Nov"
    Case "12"
    mnth = "Dec"
  End Select

    GetDate = splitty(1) & "-" & mnth & "-" & splitty(2)

End Function

Кроме того, я использовал формат даты в США, чтобы VBa соответствовал вашему запросу.

Как добавить VBA в MS Office?

Помните, с VBa, сначала сделайте копию файла в качестве резервной копии (нет возможности отменить)