Как привести даты в excel к одному формату
Думаю, из прошлого урока Вам уже известно, что даты и время в Excel хранятся в виде порядковых чисел, началом отсчета которых принято считать некое 0 января 1900 года. К счастью, в ячейках мы видим не эти числа, а привычные для нас даты и время, которые могут храниться в самых различных форматах. В этом уроке Вы узнаете, как вводить даты и время в Excel, чтобы получать требуемое форматирование.
Ввод дат и времени в Excel
Даты и время в Excel можно вводить в виде порядкового числа или доли суток, но как Вы сами понимаете, это не совсем удобно. К тому же при таком подходе к ячейке придется каждый раз применять определенный числовой формат.
Excel предлагает несколько форматов для ввода временных данных. Если Вы примените такой формат, Excel автоматически преобразует введенную дату (или время) в порядковое число (долю суток) и применит к ячейке формат Даты (или Времени), установленные по умолчанию.
Это не все возможные варианты, которые поддерживает Excel. Но даже этих вариантов Вам будет предостаточно.
Некоторые варианты отображения дат, представленные в правой колонке, могут отличаться. Это зависит от региональных настроек и формата отображения даты и времени в настройках операционной системы.
Формат даты и времени в Excel
Работая с ячейками в Microsoft Excel, которые содержат дату или время, Вы имеете возможность применять к ним различное форматирование. Например, Вы можете отображать в ячейке только день недели, или только месяц и год, или же только время.
Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек. В категории Дата приведены встроенные форматы дат:
А в категории Время – встроенные форматы времени:
Для того чтобы применить форматирование к ячейке, достаточно выбрать нужный формат в разделе Тип и нажать ОК. Нужное форматирование будет применено. Если встроенных числовых форматов Вам будет недостаточно, то можно воспользоваться категорией Все форматы. Здесь Вы также сможете найти много всего интересного.
Если ни один из вариантов Вас так и не устроил, то можно создать пользовательский числовой формат. Его создать достаточно легко, если в качестве образца использовать встроенные числовые форматы. Чтобы создать пользовательский числовой формат, выполните следующие действия:
Как видите все достаточно просто!
В этом уроке мы научились настраивать формат отображения дат и времени в Microsoft Excel, а также разобрали несколько полезных вариантов их ввода на рабочий лист. В следующем уроке мы поговорим о 7-ми функциях Excel, которые позволяют извлекать нужные параметры из значений дат и времени. На этом урок закончен. Всего Вам доброго и успехов в изучении Excel.
Одна приятная особенность Microsoft Excel состоит в том, что обычно существует несколько способов выполнения многих популярных функций. Это особенно верно с форматами даты. Независимо от того, импортировали ли вы данные из другой электронной таблицы или базы данных или просто вводите даты оплаты для своих ежемесячных счетов, Excel может легко отформатировать большинство стилей дат. Читайте дальше, чтобы узнать, как изменить формат даты в Excel.
Инструкции в этой статье относятся к Excel 2019, 2016 и 2013.
Как изменить формат даты Excel с помощью функции «Формат ячеек»
С помощью множества меню Excel вы можете изменить формат даты в несколько кликов.
Выберите вкладку « Главная ».
В группе ячеек, выберите формат в раскрывающемся меню, затем выберите Формат ячейки .
На вкладке «Число» в диалоговом окне «Формат ячеек» выберите « Дата» .
Как видите, в поле «Тип» есть несколько вариантов форматирования.
Вы также можете просмотреть раскрывающийся список «Местонахождение» и выбрать формат, наиболее подходящий для страны, для которой вы пишете.
Выбрав формат, нажмите « ОК», чтобы изменить формат даты выбранной ячейки в электронной таблице Excel.
Создайте свой собственный формат Excel с датой
Если вы не можете найти нужный формат, выберите « Пользовательский» в поле «Категория», чтобы отформатировать дату так, как вы хотите. Ниже приведены некоторые сокращения, которые вам понадобятся для создания индивидуального формата даты .
На вкладке «Число» в диалоговом окне «Формат ячеек» выберите « Пользовательский» . Как и в категории «Дата», есть несколько вариантов форматирования.
Выбрав формат, нажмите кнопку «ОК», чтобы изменить формат даты для выбранной ячейки в электронной таблице Excel.
Как отформатировать ячейки с помощью мыши
Если вы предпочитаете использовать только мышь и хотите избежать маневрирования в нескольких меню, вы можете изменить формат даты с помощью контекстного меню в Excel, щелкнув правой кнопкой мыши.
Выберите ячейки, содержащие даты, формат которых вы хотите изменить.
Щелкните правой кнопкой мыши выделение и выберите « Формат ячеек» . Либо нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».
Либо выберите « Домой» > « Номер» , нажмите стрелку и выберите « Формат номера» в правом нижнем углу группы. Или в группе « Число » вы можете выбрать раскрывающийся список, а затем выбрать « Дополнительные форматы номеров» .
Выберите « Дата» или, если вам нужен более индивидуальный формат, выберите « Пользовательский» .
В поле «Тип» выберите параметр, который наилучшим образом соответствует вашим потребностям форматирования. Это может занять немного проб и ошибок, чтобы получить правильное форматирование.
Выберите OK, когда вы выбрали формат даты.
Независимо от того, используется ли категория «Дата» или «Пользовательский», если вы видите один из типов со звездочкой ( * ), этот формат будет меняться в зависимости от выбранной локали (местоположения).
Использование Quick Apply для длинных или коротких дат
Если вам нужно быстрое изменение формата с короткой даты (мм / дд / гггг) или длинной даты (дддд, мммм дд, гггг или понедельник, 1 января 2019 г.), есть быстрый способ изменить это на ленте Excel ,
Выберите ячейки, которые вы хотите изменить формат даты.
Выберите Дом .
В группе «Число» выберите раскрывающееся меню, затем выберите « Короткая дата» или « Длинная дата» .
Использование формулы TEXT для форматирования дат
Эта формула является отличным выбором, если вам нужно сохранить исходные ячейки даты. Используя TEXT, вы можете диктовать формат в других ячейках в любом предсказуемом формате.
Чтобы начать работу с формулой TEXT, перейдите в другую ячейку, а затем введите следующее, чтобы изменить формат:
Использование поиска и замены для форматирования дат
Этот метод лучше всего использовать, если вам нужно изменить формат с тире (-), косой черты (/) или периодов (.) Для разделения месяца, дня и года. Это особенно удобно, если вам нужно изменить большое количество дат.
Выберите ячейки, для которых нужно изменить формат даты.
Выберите « Домой» > « Найти и выбрать» > « Заменить» .
В поле «Найти» введите свой оригинальный разделитель даты (тире, косая черта или точка).
В поле «Заменить на» введите то, что вы хотите изменить в качестве разделителя формата (тире, косая черта или точка).
Затем выберите один из следующих вариантов:
- Заменить все : который заменит все первые записи поля и заменит их на ваш выбор из поля Заменить на .
- Заменить : Заменяет только первый экземпляр.
- Найти все : только поиск всех исходных записей в поле « Найти» .
- Find Next : только находит следующий экземпляр из вашей записи в поле Find what .
Использование текста в столбцах для преобразования в формат даты
Если ваши даты отформатированы в виде строки чисел, а формат ячейки установлен в текст, текст в столбцы может помочь вам преобразовать эту строку чисел в более узнаваемый формат даты.
Выберите ячейки, для которых вы хотите изменить формат даты.
Убедитесь, что они отформатированы как текст. (Нажмите Ctrl + 1, чтобы проверить их формат).
Выберите вкладку « Данные ».
В группе «Инструменты данных» выберите « Текст в столбцы» .
Выберите «С разделителями» или « Фиксированная ширина» , затем нажмите « Далее» .
Большую часть времени следует выбирать с разделителями, так как длина даты может колебаться.
Снимите флажки со всех разделителей и выберите Далее .
В области « Формат данных столбца» выберите « Дата» , выберите формат строки даты в раскрывающемся меню, затем нажмите « Готово» .
Использование проверки ошибок для изменения формата даты
Если вы импортировали даты из другого источника файлов или ввели двузначные годы в ячейки, отформатированные как текст, вы заметите маленький зеленый треугольник в верхнем левом углу ячейки.
Это проверка ошибок в Excel, указывающая на проблему. Из-за параметра «Проверка ошибок» Excel определит возможную проблему с двузначными форматами года. Чтобы использовать проверку ошибок для изменения формата даты, сделайте следующее:
Выберите одну из ячеек, содержащих индикатор. Вы должны заметить восклицательный знак с выпадающим меню рядом с ним.
Выберите раскрывающееся меню и выберите « Преобразовать ХХ в 19ХХ» или « Преобразовать ХХ в 20ХХ» , в зависимости от года.
Вы должны увидеть, что дата немедленно изменится на четырехзначное число.
Использование быстрого анализа для доступа к ячейкам форматирования
Быстрый анализ может использоваться не только для форматирования цвета и стиля ваших ячеек. Вы также можете использовать его для доступа к диалоговому окну «Формат ячеек».
Выберите несколько ячеек, содержащих даты, которые нужно изменить.
Выберите Быстрый анализ в правом нижнем углу вашего выбора, или нажмите Ctrl + Q .
В разделе «Форматирование» выберите « Содержащий текст» .
Используя правое выпадающее меню, выберите Custom Format .
Выберите вкладку Number , затем выберите либо Date, либо Custom .
Изменить формат даты и времени в Excel может быть непросто. Случается, что в одной и той же книге даты отображаются в разных форматах, например, 03/08/2020, 4 авг 20, 05.08.20, т.д. Как быстро унифицировать даты, чтобы они на всех листах отображались одинаково, например, в формате yyyy-mm-dd?
Инструмент «Изменить формат даты» поможет найти все ячейки с датами и временем и мгновенно унифицировать их формат:
Изменить только даты, только время, или даты вместе со временем Унифицировать формат в диапазонах, на всех листах и книгах сразу Выбрать формат из готовых вариантов или задать собственный форматПеред началом работы добавьте «Изменить формат даты» в Excel
«Изменить формат даты» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Как изменить формат даты в Excel
Вкладка XLTools В группе «Дата и Время» откройте выпадающий список Нажмите Изменить формат даты и времени .
Задайте область поиска:
Выберите нужный формат:
Выберите подходящий формат из списка, напр., принятый в вашей стране или на вашем языке. Совет: превью в нижней части окна на примере текущей даты показывает, как даты будут отображаться в ячейках.5. Нажмите ОК Готово! Теперь все даты в одном формате
Как изменить формат времени в Excel
Вкладка XLTools В группе «Дата и Время» откройте выпадающий список Нажмите Изменить формат даты и времени .
Задайте область поиска:
Выберите нужный формат:
Выберите подходящий формат из списка, напр., принятый в вашей стране или на вашем языке. Совет: превью в нижней части окна на примере текущего времени показывает, как время будет отображаться в ячейках.5. Нажмите ОК Готово! Теперь все значения времени в одном формате.
Как изменить формат даты вместе со временем в Excel
Вкладка XLTools В группе «Дата и Время» откройте выпадающий список Нажмите Изменить формат даты и времени .
Отметьте Дата и время , чтобы найти все ячейки, в которых стоят даты со временем.Задайте область поиска:
Выберите нужный формат:
Выберите подходящий формат из списка, напр., принятый в вашей стране или на вашем языке. Или задайте пользовательский формат даты и времени Совет: превью в нижней части окна на примере текущих дат и времени показывает, как даты будут отображаться в ячейках5. Нажмите ОК Готово! Теперь все даты со временем в одном формате.
Если формат даты не меняется
Инструмент «Изменить формат даты» обрабатывает только ячейки с форматом даты или времени. Если какие-то ячейки не изменили формат, значит, Excel не распознает эти ячейки как даты или время. Скорее всего, это ячейки с общим, текстовым или числовым форматом.
В этом случае нужно применить формат даты к нераспознанным ячейкам:
Задайте формат вручную:
Выберите ячейку По правому клику выберите Формат Ячеек На вкладке «Число» выберите «Дата» (или «Время») Примените нужный формат При необходимости вставьте даты.
Выберите ячейку По правому клику выберите Вставить дату Выберите нужную дату в календаре Дата вставится в ячейку, ячейке будет автоматически присвоен формат даты.
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]"любой текст"КодФормата"любой текст"
Посмотрите простые примеры использования форматирования. В столбце А - значение без форматирования, в столбце B - с использованием пользовательского формата (применяемый формат в столбце С)
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
Форматы даты
| Формат | Описание применения | Пример отображения |
|---|---|---|
| М | Отображает числовое значение месяца | от 1 до 12 |
| ММ | Отображает числовое значение месяца в формате 00 | от 01 до 12 |
| МММ | Отображает сокращенное до 3-х букв значение месяца | от Янв до Дек |
| ММММ | Полное наименование месяца | Январь - Декабрь |
| МММММ | Отображает первую букву месяца | от Я до Д |
| Д | Выводит число даты | от 1 до 31 |
| ДД | Выводит число в формате 00 | от 01 до 31 |
| ДДД | Выводит день недели | от Пн до Вс |
| ДДДД | Выводит название недели целиком | Понедельник - Пятница |
| ГГ | Выводит последние 2 цифры года | от 00 до 99 |
| ГГГГ | Выводит год даты полностью | 1900 - 9999 |
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат "ДД.ММ.ГГГГ" отформатирует дату в привычный нам вид 31.12.2017, а формат "ДД МММ" преобразует дату в вид 31 Дек.
Форматы времени
Аналогичные форматы есть и для времени.
| Формат | Описание применения | Пример отображения |
|---|---|---|
| ч | Отображает часы | от 0 до 23 |
| чч | Отображает часы в формате 00 | от 00 до 23 |
| м | Отображает минуты | от 0 до 59 |
| мм | Минуты в формате 00 | от 00 до 59 |
| с | Секунды | от 0 до 59 |
| сс | Секунды в формате 00 | от 00 до 59 |
| [ч] | Формат истекшего времени в часах | например, [ч]:мм -> 30:15 |
| [мм] | Формат истекшего времени в минутах | например, [мм]:сс -> 65:20 |
| [сс] | Формат истекшего времени в секундах | - |
| AM/PM | Для вывода времени в 12-ти часовом формате | например, Ч AM/PM -> 3 PM |
| A/P | Для вывода времени в 12-ти часовом формате | например, чч:мм AM/PM -> 03:26 P |
| чч:мм:сс.00 | Для вывода времени с долями секунд |
Текстовые форматы
Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.
| ДО форматирования | ПОСЛЕ форматирования | Примененный формат |
|---|---|---|
| Россия | страна - Россия | "страна - "@ |
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
- Положительные числа
- Отрицательные числа
- Нулевые значения
- Текстовый формат
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]"любой текст"КодФормата"любой текст"
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает "нет данных".
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
- Выделить ячейки, формат которых вы хотите отредактировать.
- Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
- Изменить строку форматирования в поле Тип.
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Читайте также: