Разбор дат в LibreOffice Calc - есть ли лучший / более простой способ?

Question

У меня есть строка этого конкретного формата:

DD/MMM/YY HH:MM AM/PM

например

03/Nov/14 9:00 AM

Я не мог понять, как заставить Calc распознавать его как дату, поэтому мне пришлось сделать следующее для строки даты в B2:

=DATEVALUE(
    CONCATENATE(
        2000+MID(B2,8,2),
        "-",
        IF(MID(B2,4,3)="Jan",1,
            IF(MID(B2,4,3)="Feb",2,
                IF(MID(B2,4,3)="Mar",3,
                    IF(MID(B2,4,3)="Apr",4,
                        IF(MID(B2,4,3)="May",5,
                            IF(MID(B2,4,3)="Jun",6,
                                IF(MID(B2,4,3)="Jul",7,
                                    IF(MID(B2,4,3)="Aug",8,
                                        IF(MID(B2,4,3)="Sep",9,
                                            IF(MID(B2,4,3)="Oct",10,
                                                IF(MID(B2,4,3)="Nov",11,
                                                    IF(MID(B2,4,3)="Dec",12,""
        )))))))))))),
        "-",
        MID(B2,1,2)
    )
)
+
TIMEVALUE(
    IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8))
)

или в одну строку:

=DATEVALUE(CONCATENATE(2000+MID(B2,8,2),"-",IF(MID(B2,4,3)="Jan",1,IF(MID(B2,4,3)="Feb",2,IF(MID(B2,4,3)="Mar",3,IF(MID(B2,4,3)="Apr",4,IF(MID(B2,4,3)="May",5,IF(MID(B2,4,3)="Jun",6,IF(MID(B2,4,3)="Jul",7,IF(MID(B2,4,3)="Aug",8,IF(MID(B2,4,3)="Sep",9,IF(MID(B2,4,3)="Oct",10,IF(MID(B2,4,3)="Nov",11,IF(MID(B2,4,3)="Dec",12,"")))))))))))),"-",MID(B2,1,2)))+TIMEVALUE(IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8)))

а затем измените тип ячейки, содержащей формулу, на «Дата» и выберите желаемый формат.

В целом довольно сложный процесс. Есть ли более автоматизированный способ?

Answer 1

Вы можете использовать своего рода "справочную таблицу" для перевода названий месяцев в даты (это решение основано на решении, предложенном для противоположной операции - преобразования чисел в названия месяцев)

Просто поместите названия месяцев один за другим в столбец вашей электронной таблицы и определите именованный диапазон для этих ячеек (например, Monthnames). Теперь вы можете определить номер месяца, используя =MATCH("Dec",Monthnames,0) что приводит к 12:

С такой таблицей вы можете заменить операторы IF одним вызовом MATCH :

=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MATCH(MID(A2,4,3),Monthnames,0),"/",MID(A2,1,2)))

или на нескольких строках:

=DATEVALUE( CONCATENATE( MID(A2,8,2), "/", MATCH( MID(A2,4,3), Monthnames, 0 ), "/", MID(A2,1,2) ) )

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

Answer 2

Используйте MONTH, чтобы извлечь значение месяца с помощью фиктивных значений dd, yy:

=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MONTH("1"&MID(A2,4,3)&"1"),"/",MID(A2,1,2)))