Отображение числа в формате даты в Excel

Сталкивались ли вы когда-нибудь с ситуацией, когда в Excel в ячейке вместо числа отображается дата? Иногда это происходит, когда мы вводим число в качестве суммы, а оно преобразуется в формат "dd/mm/yyy". Такие ситуации становятся еще более неприятными, когда пользователь не знает, как решить эту проблему. В этой статье я постараюсь объяснить, чем вызвано такое поведение Excel, и как его исправить.

При работе в Microsoft Excel бывают случаи, когда при вводе числа в ячейку значение отображается как дата. Эта ситуация особенно неблагоприятна, если необходимо ввести другие данные, а пользователь не знает, как их потом вводить. После изучения причин, по которым это происходит, и того, что можно сделать для решения этой проблемы, я хотел бы поделиться собственным опытом ее решения.

Сегодня поговорим про проблему отображения числа в формате даты в популярной программе Эксель. Итак, поехали!

Случаются ситуации, когда при работе в программке Excel, впоследствии занесения количества в ячейку, оно отражается в облике даты.

Тем более эта обстановка нервирует, в случае если надо изготовить ввод данных иного подобия, а юзер не понимает как это устроить. Давайте разберемся, отчего в Экселе взамен количества отражается дата, а еще определим, как выправить данную историю.


Заключение трудности отражения числа как даты

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

Этим образом, дабы сделать отражение данных, как ему надо, юзер обязан его изменить. Устроить это возможно незамедлительно несколькими методами.


Метод 1: Контекстное окно

Основная масса юзеров для заключения предоставленной задачки пользуют контекстное окно.

Кликаем правой кнопкой мыши по спектру, в котором надо заменить формат. В контекстном рационы, которое будет замечен впоследствии данных поступков, избираем место «Формат ячеек…».




Раскрывается окошко форматирования. Перебегаем во вкладку «Число», в случае если оно внезапно было не закрыто в иной вкладке. Нам надо перевести параметр «Числовые форматы» с «Дата» на необходимое юзеру.

Почаще всего это «Общий», «Числовой», «Денежный», «Текстовый», но имеют все шансы быть и иные.

Здесь все находится в зависимости от определенной истории и назначения вводимых данных. Впоследствии такого, как переключение параметра выполнено нажимаем на кнопку «OK».



Впоследствии сего данные в выделенных ячейках уже не станут отражаться как дата, а будут видеться в подходящем для юзера формате. То есть, станет достигнута установленная задача.


Метод 2: Перемена форматирования на ленте

2 метод в том числе и легче первого, но непонятно почему наименее известный между юзеров.

Выделяем ячейку или же спектр с форматом даты.


Находясь во вкладке «Главная» в блоке инструментов «Число» раскрываем особое фон форматирования.

В нём представлены известные форматы. Избираем тот, который более подходит для определенных данных.


В случае если между представленного списка необходимый вариант не был отыскан, то нажимайте на место «Другие числовые форматы…» в данном же перечне.


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

В соответствии с этим, и последующие воздействия также станут буквально этими же, как и при первом варианте заключения трудности.

Избираем необходимое место и нажимаем на кнопку «OK».


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

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

В случае если юзер понимает метод поступков, то данная процедура делается простой. Исполнить её возможно 2-мя методами, но оба они сводятся к изменению формата ячейки с даты на всякий иной.

Преобразование дат из текстового формата в формат даты

В некоторых случаях даты могут форматироваться и храниться в ячейках в виде текста. Например, даты могут быть введены в ячейки, которые имеют текстовый формат, кроме того, даты могут быть импортированы или вставлены в ячейки в текстовом формате из внешних источников данных.

Даты, отформатированные как текст выравниваются по левому краю ячейки (а не по правому краю). Если включена Проверка ошибок , дат в текстовом формате с двузначным может быть также помечены как с индикатором ошибки: Ячейка с ошибкой в формуле .

Поскольку ошибок в Excel можно определить текст, отформатированный дат с двумя цифрами, можно использовать параметры автоматического исправления преобразуемый Дата в формате даты. Функция ДАТАЗНАЧ преобразования большинство других типов дат в текстовом формате в формат даты.

Преобразование текстовых дат с двузначным годом с помощью проверки ошибок

При импорте данных в Microsoft Excel из другого источника или введите даты с двузначным по ячейкам, которые были ранее отформатированные как текст, может появиться маленький зеленый треугольник в левом верхнем углу ячейки. Индикатор этой ошибки вы узнаете, что даты хранятся как текст, как показано в следующем примере.

Ячейки с зеленым индикатором ошибки в левом верхнем углу

Для преобразования текстовых значений в формат дат можно использовать индикатор ошибок.

Примечания: Во-первых убедитесь, что ошибок включен в Microsoft Excel. Это можно сделать:

Выберите пункты Файл > Параметры > Формулы .

В Excel 2007, нажмите кнопку Microsoft Office Изображение кнопки Office , а затем выберите пункт Параметры Excel > формулы .

Проверка ошибок установите флажок Включить фоновый поиск ошибок . Любая ошибка, обнаружении, будут помечены треугольник в левом верхнем углу ячейки.

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

Выполните эту процедуру, чтобы преобразовать дату в формате текста в обычном даты.

Выберите на листе любую ячейку или диапазон смежных ячеек с индикатором ошибки в верхнем левом углу. Дополнительные сведения читайте в статье Выбор ячеек, диапазонов, строк или столбцов на листе.

Совет: Для отмены выделения ячеек щелкните любую ячейку в таблице.

Нажмите кнопку ошибки, которая появится рядом с выбранной ячейки.

В меню выберите команду Преобразовать XX в 20XX или Преобразовать XX в 19XX . Если вы хотите прекратить индикатором ошибки без преобразования число, нажмите кнопку Пропустить ошибку .

Команды преобразования дат
Преобразование дат в текстовом формате с двузначным номером года в стандартные даты с четырьмя цифрами.

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

Преобразование дат в текстовом формате с помощью функции ДАТАЗНАЧ
Чтобы преобразовать дату в текстовом формате в ячейке в числовом формате, с помощью функции ДАТАЗНАЧ. Скопируйте формулу и выделение ячеек, содержащих даты в текстовом формате с помощью Специальной вставки применить формат даты.

Выполните следующие действия.

Выделите пустую ячейку и убедитесь, что его числовой формат Общие.
Проверка числового формата: на вкладке Главная в группе число щелкните стрелку рядом с полем Числовой формат и выберите пункт Общие .

В пустой ячейке сделайте следующее.

Введите = ДАТАЗНАЧ (

Щелкните ячейку, содержащую дату в текстовом формате, которую следует преобразовать.

Введите )

Нажмите клавишу ВВОД, а функция ДАТАЗНАЧ возвращает порядковый номер даты, которая была представлена в текстовом формате.

Excel даты хранятся как целые числа, чтобы их можно использовать в вычислениях. По умолчанию 1 января 1900 г — порядковый номер 1, а 1 января 2008 г., — номер 39448, так как интервал в днях после 1 января 1900.To скопировать формулу преобразования в диапазон смежных ячеек, выделите ячейку, содержащую формулу, которая была введена , а затем перетащите маркер заполнения Маркер заполнения по диапазону пустых ячеек, который по размерам совпадает диапазон ячеек, содержащих даты в текстовом формате.

В результате получится диапазон ячеек с последовательными номерами, который соответствует диапазону ячеек с датами в текстовом формате.

Выберите ячейку или диапазон ячеек с последовательными номерами и на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .

Сочетание клавиш: Можно также нажать клавиши CTRL + C.

Выберите ячейку или диапазон ячеек, которые содержат даты в текстовом формате, и на вкладке Главная в группе Буфер обмена нажмите кнопку со стрелкой Вставить и выберите команду Специальная вставка .

В диалоговом окне Специальная вставка в разделе Вставить выберите параметр Значения и нажмите кнопку ОК .

На вкладке Главная щелкните средство запуска всплывающего окна рядом с надписью число .

В поле Числовые форматы выберите пункт Дата , после чего укажите необходимый формат даты в списке Тип .

Чтобы удалить порядковые номера после успешного преобразования всех дат, выделите содержащие их ячейки и нажмите клавишу DELETE.

Преобразовать дату в текст Excel.

Рассмотрим, как преобразовать дату в текст Excel . Нам нужно, чтобы дата была написана в текстовом формате.
Сначала вспомним, как записаны и как хранятся даты в Excel.
В Excel даты хранятся под порядковыми номерами. Например, дата 01.01.1900 г. хранится под порядковым номером – 1. Дата 02.01.1900 г имеет порядковый номер 2, дата 27.01.2015 г. имеет порядковый номер – 42031, т.д.

Чтобы преобразовать этот порядковый номер в привычный для нас формат даты, нужно установить в ячейке формат даты. Какими способами вызвать диалоговое окно и сменить формат, смотрите в статье «Формат Excel» тут.
Уточним еще один момент - формат даты в Excel.

В Excel два формата – формат ячейки и формат значения , написанного в ячейке.
Формат ячейки в Excel – это внешний вид данных в ячейке - шрифт, размер шрифта; число, записанное с разрядами; в каком месте ячейки будут написаны данные – слева, справа, по центру, выровнены, т.д.

Например, при числовом формате ячеек данные пишутся от правой границы ячейки, а при текстовом формате – от левой границы ячейки. Формат значения ячейки Excel – это формат, с помощью которого Excel понимает ЧТО написано – число, дата, текст, т.д., и проводит различные вычисления в зависимости от формата числа, текста, т.д. Если формат чисел стоит не верный, то формула считать не будет.

Теперь рассмотрим, как преобразовать порядковый номер дат в формат «Дата» .
У нас таблица с датами, написанными порядковыми номерами.

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

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

Здесь в столбце B дата стоит в формате ячейки «Дата».

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

Даты записаны так. Формат ячеек – «Дата».

Нажимаем на ячейку В7. На закладке «Формулы» в разделе «Библиотека функций» в функциях «Текстовые» выбираем функцию «Текст». Диалоговое окно функции «Текст» заполнили так.Нажимаем «ОК». Получилась такая формула. =ТЕКСТ(А7;"ДД.ММ.ГГГГ") Копируем формулу из ячейки В7 вниз по столбцу.

Даты в столбце B написаны как текст (текстовый формат значений ). Формат в ячейках (внешний вид данных)стоит – «Общий». Если нужно, то можно изменить формат ячеек «Общий» на формат «Текстовый».

Бывает, что в ячейках дата написана одним числом (например, так. 20160112). Нам нужно преобразовать это число в дату, разделить точками год, месяц, день.

В Excel можно отследить изменения, произведенные ранее. Например, сотрудник без вашего ведома изменил некоторые цифры. Или, например, составляем отчет. Нам нужно посмотреть предыдущую цифру, которая стояла в ячейке, т.д. Подробнее о работе с этой функцией смотрите в статье "Журнал изменений в Excel".

Преобразование времени

Предположим, что вы хотите узнать, сколько часов, существует 5,5 дней или передает в годах 3,45 сколько дней или точно как количество месяцев между датой начала и дату окончания.

Чтобы преобразовать единиц времени несколькими способами.

Преобразование единиц времени

Для преобразования из единиц времени в другой, используйте функцию преобразовать .

Примечание: Для преобразования, включающие года рекомендуется правильности 365,25 дней в году.

Преобразование стандартного формата времени в десятичное число

Для преобразования из часы: минуты: секунды формат, воспользуйтесь функцией целое .

Преобразование десятичное число в стандартный формат времени

Для преобразования десятичных чисел в часы: минуты: секунды формат, используйте оператор деления (/) вместе с функцией текст .

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

Примечание: Также можно применить формат времени без использования функции текст для задания формата. Чтобы просмотреть числа в виде времени: выделите ячейку и на вкладке Главная — в группе " число ", щелкните стрелку рядом с полем Числовой формат . Нажмите кнопку Другие числовые форматы , в списке категории выберите пункт настраиваемые и выберите в списке Тип выберите пользовательский формат. Помните: Если вы используете оба формата функция текст и формат чисел, функция текст имеет приоритет.

При вводе чисел в Excel, например, вы можете неожиданно обнаружить, что число представлено в виде даты. Это случилось со мной совсем недавно при подготовке отчета о доходах за текущий год. Заполняя пробелы в Excel, я постоянно видел даты вместо чисел. Например, если я вводил число один, то в моей электронной таблице появлялась дата 1/1/1890.

Вы вводите число, а не дату, которое форматируется как дата. Или наоборот. Каждый раз, когда вы вводите число или текст, он превращается в формат ячейки. Это не очень удобно и полезно, надеюсь мы разобрались как решить эту проблему.

Рейтинг:(голосов:1)