Excel как преобразовать число в дату
Перейти к содержимому

Excel как преобразовать число в дату

Исправление чисел, превратившихся в даты

При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема — дробные числа превращаются в даты:

Испорченные данные

Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью — точка), а в Excel у вас российские настройки (где между целой и дробной частью — запятая, а точка используется как разделитель в дате).

При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике — если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату — оно будет конвертировано в дату. Всё, что на дату не похоже — останется текстом.

Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:

  • В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
  • В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
  • А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

Неправильная дата

То же самое случилось и в ячейке А4, где изначально было число 12.6923. Причем 6923-й год Excel тут нисколько не смутил — для него главное, чтобы дата была не раньше 1900 года. Так что добро пожаловать в далекое будущее:

Еще одна неправильная дата

  • Единственное, что распознаётся корректно — это числа без дробной части (число 1000 в ячейке А6). И на том спасибо 🙂
  • Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel — количество дней от 01.01.1900 до текущей даты:

    Неправильное число после изменения формата

    Лечится вся эта история тремя принципиально разными способами.

    Способ 1. Заранее в настройках

    Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл — Параметры — Дополнительно (File — Options — Advanced) :

    Настройка разделителей в окне параметров Excel

    Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .

    После этого можно смело импортировать данные — проблем не будет.

    Способ 2. Формулой

    Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:

    Формула исправления чисел из дат

    =—ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )

    В английской версии это будет:

    =—IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))

    Логика здесь простая:

    • Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
    • Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
    • Если в исходной ячейке дата, то выводим её в формате "номер месяца — запятая — номер года" с помощью функции ТЕКСТ (TEXT) .
    • Чтобы преобразовать получившееся текстовое значение в полноценное число — выполняем бессмысленную математическую операцию — добавляем два знака минус перед формулой, имитируя двойное умножение на -1.

    Способ 3. Макросом

    Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert — Module и копируем туда такой код:

    Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer — Macros) . Все испорченные числа будут немедленно исправлены.

    Числа преобразовать в дату

    Помощь в написании контрольных, курсовых и дипломных работ здесь.

    Преобразовать дату из текстовой строки в DATETIME
    В экселе сейчас текстовые поля вместо дат: 1 марта в 19:24 вчера в 09:45 сегодня в 09:45 и т.д.

    Преобразовать дату
    Гении С++, вы моя последняя надежда. Задали мне задачу по программированию. Она простая.

    Преобразовать дату
    В Sql Server дата хранится в фармате yyyymmdd и соответственно когда присваиваю значение из базы.

    Вложения

    дата.xlsx (8.2 Кб, 32 просмотров)

    Сообщение от AlexM

    Помощь в написании контрольных, курсовых и дипломных работ здесь.

    Преобразовать дату в дни
    Есть дата 1.01.2017, нужно преобразовать ее так чтобы показывало сколько это дней.

    Преобразовать строку в дату
    каким образом можно преобразовать строку "18 октября 2016 г." в дату??

    Преобразовать дату в число
    Есть дата в переменной в формате «01.02.3088» как ее преобразовать в число 433941 ? То есть просто.

    Преобразовать число в дату
    Переменная содержит 229 Как данное число преобразовать в время. то есть на выходе необходимо.

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *