Мини-конкурс на знание Excel


Недавно в социальной сети мне пришло сообщение от бывшего коллеги. Цитирую:

"…Вот что мне пришлось придумать, чтобы преобразовать числа вида 1/24/2014 (бывшие даты) в нормальные даты: =DATEVALUE(CONCATENATE(MID(A1;SEARCH("/";A1;1)+1;SEARCH("/";A1;4)-SEARCH("/";A1;1)-1);".";LEFT(A1;SEARCH("/";A1;1)-1);".";RIGHT(A1;4)))

..Интересно, найдется ли решение попроще?"

Друзья, этот вопрос и стал нашим заданием на мини-конкурс:

  • Если вы используете более простой и доступный способ преобразования дат – опишите его в комментариях к этому посту.
  • Обязательно укажите свой контакт или ник – мы не сможем выдать приз анониму.
  • Победителя определит комиссия из сотрудников Microsoft.
  • Комментарии принимаются на конкурс до 7 февраля 2014 года. Мы будем рады узнать ваш вариант решения и после этой даты, но он уже не будет иметь шанса на приз.
  • Приз – ключ на Office 365 Home Premium, на год.

Удачи!

Comments (19)

  1. MrBond_ru says:

    Боюсь, что тут одним форматом не отделаешься. Раз дата автоматом не распозналась, значит Excel определил содержимое как текст, и без вмешательства танца с бубном не обойдется. Судя по исходной формуле, нам нужно m/d/yyyy преобразовать в d.m.yyyy либо dd.mm.yyyy
    (будет зависеть от региональных настроек для дат в системе). Я придумал три варианта преобразования с помощью формулы. 1. "Из пушки по воробьям" (формула на 164 символа) Данный метод – формульная альтернатива text to columns (если пробел не является криичным
    символом): =DATEVALUE( SUBSTITUTE( TRIM(LEFT(RIGHT(SUBSTITUTE(A1;"/";REPT(" ";LEN(A1)));2*LEN(A1));LEN(A1)) & LEFT(SUBSTITUTE(A1;"/";REPT(" ";LEN(A1)));LEN(A1))&RIGHT(A1;4));" ";".")) 2. "Симметричный" (125 символов) =DATEVALUE(SUBSTITUTE(RIGHT(MID(A1;1;LEN(A1)-5);2);"/";"")
    & "." & SUBSTITUTE(LEFT(MID(A1;1;LEN(A1)-5);2);"/";"") & "." &RIGHT(A1;4)) 3. "Магия" (93 символа) =DATEVALUE(SUBSTITUTE(MID(A1;3;LEN(A1)-7);"/";"")&"."&LEFT(A1;FIND("/";A1)-1)&"."&RIGHT(A1;4)) Спасибо за интересную задачу! 🙂

  2. Друзья, по правилам конкурса (ответы до 7 февраля) и результатам голосования, с учетом мнения автора вопроса, победителем становится Марат. Марат, мы попытаемся с вами связаться, для надежности – напишите сообщение в форме обратной связи http://blogs.technet.com/b/tasush/about.aspx

  3. Danila_Master says:

    По-видимому, проблема заключается в том, что региональные настройки для России не предусматривают варианта написания даты в формате месяц/день/год, а источником данных как раз является таблица, созданная в версии Excel локализированной для США. Изменение
    формата представления даты в этом случае скорее всего не поможет. Могу предположить, что изменение региональных настроек для windows сможет решить проблему с датами (сам не проверял, поэтому утверждать не могу). На мой взгляд, самым простым способом конвертации
    дат является использование функции Text-to-Columns (Tab: DATA) на третьем шаге нужно выбрать Column Data Format: Date: MDY (для нашего конкретного случая). Если же локализация Excel англоязычная (США), то прекрасно работают следующие варианты (или просто поменять
    формат отображения даты…): =DAY(A1)&"."&MONTH(A1)&"."&YEAR(A1) =TEXT(A1, "dd.mm.yyyy")

  4. Друзья, спасибо за ответы! Я впечатлена, и боюсь, что за судейством придется обратиться к автору вопроса.

  5. MaratSh says:

    Другой вариант. Длиннее, медленнее и совсем не практично, но зато совершенно с другой идеей: {=MAX(IF(COLUMN($A$1:$L$31)&"/"&ROW($A$1:$L$31)&RIGHT(A1;5)=A1;DATE(RIGHT(A1;4);COLUMN($A$1:$L$31);ROW($A$1:$L$31))))} По-русски: {=МАКС(ЕСЛИ(СТОЛБЕЦ($A$1:$L$31)&"/"&СТРОКА($A$1:$L$31)&ПРАВСИМВ(A1;5)=A1;ДАТА(ПРАВСИМВ(A1;4);СТОЛБЕЦ($A$1:$L$31);СТРОКА($A$1:$L$31))))}
    (фигурные скобки не нужны – вводить через Ctrl+Shift+Enter)

  6. Жук says:

    использую формулу =ПОДСТАВИТЬ(A1; "/"; ".")

  7. MaratSh says:

    Немного покороче, но не принципиально: =DATE(RIGHT(A1;4);SUBSTITUTE(LEFT(A1;2);"/";"");SUBSTITUTE(MID(A1;FIND("/";A1)+1;2);"/";"")) В переводе на русский: =ДАТА(ПРАВСИМВ(A1;4);ПОДСТАВИТЬ(ЛЕВСИМВ(A1;2);"/";"");ПОДСТАВИТЬ(ПСТР(A1;НАЙТИ("/";A1)+1;2);"/";""))

  8. Жук says:

    :)) правильнее формула выглядит: =ЕСЛИ(ДЛСТР(A1)=10;ПСТР(A1;4;2)&"."&ПСТР(A1;1;2)&"."&ПСТР(A1;7;4);ПСТР(A1;3;2)&"."&ПСТР(A1;1;1)&"."&ПСТР(A1;6;4)) результатом будет, при значении месяца до 10, например 1/24/2014 результат 24.1.2014, при значении месяца
    от 10 результатом будет 24.10.2014

  9. Жук says:

    А можно и так: =ЕСЛИ(ДЛСТР(A6)=10;ПСТР(A6;4;2)&"."&ПСТР(A6;1;2)&"."&ПСТР(A6;7;4);ПСТР(A2;3;2)&"."&ПСТР(A2;1;1)&"."&ПСТР(A2;6;4))

  10. Жук says:

    Можно эту задачу решить и при помощи формулы: =ПСТР(A2;3;2)&"."&ПСТР(A2;1;1)&"."&ПСТР(A2;6;4)

  11. Vlad_53 says:

    2 Жук Отличное решение!

  12. Жук says:

    использую формулу =ПОДСТАВИТЬ(A1; "/"; ".")

  13. Александр says:

    Эта задача решаются выбором формата представления даты в ячейке вообще без формул.

  14. Жук says:

    использую формулу =ПОДСТАВИТЬ(A1; "/"; ".")

  15. Дмитрий К (rio@tut.by) says:

    Доброго дня. Можно и вот так 🙂 =ДАТА(ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ПСТР(B22;7;4);ПСТР(B22;6;4));ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ЛЕВСИМВ(B22;2);ЛЕВСИМВ(B22;1));ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ПСТР(B22;4;2);ПСТР(B22;3;2)))

  16. Дмитрий К (rio@tut.by) says:

    Жук, молодец 🙂

  17. Иван says:

    Жук, одна проблема: в американской формате сначала идет месяц (в условиях как раз такой пример), так что получится дата вида 1.24.2014. Не совсем то, что нужно 🙂

  18. Виктория Зуева says:

    Если это один столбец с такими "датами", то предлагаю такое решение. 1) Выделить столбец. Ctrl+H – окно замены, найти /, заменить – . (точка). Заменить все. Результат – столбец со значениями вида 1.24.2014 (или 12/24/2014 – для двузначных номеров месяцев)
    2) Столбец выделен. Данные – Работа с данными – Текст по столбцам. На первом шаге выбираем: формат исходных данных – с разделителями, Далее. На втором шаге: символом-разделителем является: – "галка" напротив "пробел", и справа флажок включить рядом со "считать
    последовательные разделители одним", Далее. На третьем шаге – Формат ряда данных столбца – переключатель на "дата", там же рядом в списке выбрать МДГ. ГОТОВО!

  19. Виктория Зуева says:

    Если это один столбец с такими "датами", то предлагаю такое решение. 1) Выделить столбец. Ctrl+H – окно замены, найти /, заменить – . (точка). Заменить все. Результат – столбец со значениями вида 1.24.2014 (или 12/24/2014 – для двузначных номеров месяцев)
    2) Столбец выделен. Данные – Работа с данными – Текст по столбцам. На первом шаге выбираем: формат исходных данных – с разделителями, Далее. На втором шаге: символом-разделителем является: – "галка" напротив "пробел", и справа флажок включить рядом со "считать
    последовательные разделители одним", Далее. На третьем шаге – Формат ряда данных столбца – переключатель на "дата", там же рядом в списке выбрать МДГ. ГОТОВО!

Skip to main content