Как изменить дату во всех ячейках Excel
Обновлено: 03.07.2024
Когда вы вводите дату в Microsoft Excel, программа отформатирует ее в соответствии с настройками даты по умолчанию. Например, если вы хотите ввести дату 6 февраля 2020 г., дата может отображаться как 6 февраля, 6 февраля 2020 г., 6 февраля или 06 февраля 2020 г., все зависит от ваших настроек. Вы можете обнаружить, что если вы измените форматирование ячейки на «Стандартное», ваша дата будет сохранена в виде целых чисел. Например, 6 февраля 2020 г. станет 43865 , поскольку форматирование даты в Excel основано на 1 января 1900 г. Каждый из этих параметров представляет собой способ форматирования дат в Excel. Чтобы упростить организацию данных в Excel, узнайте, как изменить формат даты в Excel .
Выбор формата даты из списка
Форматировать даты в Excel проще всего с помощью списка форматов дат. Большинство форматов даты, которые вы можете использовать, можно найти в этом меню.
- Выберите ячейки, которые хотите отформатировать.
- Нажмите Ctrl+1 или Command+1.
- Выберите вкладку "Числа".
- В категориях выберите «Дата».
- В меню "Тип" выберите нужный формат даты.
Создание пользовательского формата даты Excel
Чтобы настроить формат даты, выполните шаги по выбору параметра из списка форматов даты. Выбрав наиболее близкий формат даты к желаемому, вы можете настроить его.
- В меню "Категория" выберите "Пользовательская".
- Появится тип, который вы выбрали ранее. Вносимые вами изменения будут применяться только к вашим индивидуальным настройкам, а не к настройкам по умолчанию.
- В поле "Тип" введите правильный код, чтобы изменить дату.
Преобразование форматов даты в другие локали
Если вы используете даты для нескольких разных местоположений, вам может потребоваться преобразовать их в другой язык:
- Выберите нужные ячейки.
- Нажмите Ctrl+1 или Command+1.
- В меню "Числа" выберите "Дата".
- Под меню "Тип" есть раскрывающееся меню "Язык".
- Выберите правильный «Язык».
Вы также можете настроить региональные параметры:
Советы по отображению дат в Excel
Если у вас есть правильный формат даты, есть дополнительные советы, которые помогут вам понять, как организовать данные в Excel для ваших наборов данных.
Даже с правильно отформатированными датами и отображениями организация данных в Excel может работать только так же, как и сами данные. Беспорядочные данные не приведут к пониманию во время анализа, как бы они ни были отформатированы.
Подготовка данных в Excel
Форматирование данных является частью более крупного процесса, известного как "подготовка данных", или всех шагов, необходимых для очистки, стандартизации и подготовки данных для аналитического использования.
Хотя подготовка данных в Excel, безусловно, возможна, она становится экспоненциально сложнее, поскольку аналитики работают с большими и более сложными наборами данных. Вместо этого многие современные аналитики вкладывают средства в современные платформы подготовки данных, такие как Trifacta, чтобы ускорить общий процесс подготовки данных для больших и малых данных.
Запланируйте демонстрацию Trifacta, чтобы увидеть, как она может улучшить процесс подготовки данных, или попробуйте платформу сами, начав работать с Trifacta сегодня.
Первая часть нашего руководства посвящена форматированию дат в Excel и объясняет, как установить форматы даты и времени по умолчанию, как изменить формат даты в Excel, как создать собственное форматирование даты и преобразовать даты в другая локаль.
Наряду с числами даты и время являются наиболее распространенными типами данных, которые люди используют в Excel. Однако работать с ними может быть довольно запутанно, во-первых, потому что одна и та же дата может отображаться в Excel разными способами, а во-вторых, потому что Excel всегда хранит даты в одном и том же формате, независимо от того, как вы отформатировали дату в Excel. заданная ячейка.
Небольшое знание форматов даты в Excel может помочь вам сэкономить массу времени. И это как раз цель нашего подробного руководства по работе с датами в Excel. В первой части мы сосредоточимся на следующих функциях:
Формат даты Excel
Прежде чем вы сможете воспользоваться мощными функциями работы с датами в Excel, вы должны понять, как в Microsoft Excel хранятся даты и время, так как это является основным источником путаницы.Хотя вы ожидаете, что Excel запомнит день, месяц и год для даты, это не так.
Excel хранит даты в виде последовательных чисел, и только форматирование ячейки приводит к тому, что число отображается как дата, время или дата и время.
Даты в Excel
Все даты хранятся как целые числа, представляющие количество дней с 1 января 1900 года, которое хранится как число 1, до 31 декабря 9999 года, которое сохраняется как 2958465.
- 2 – 2 января 1900 года.
- 3 – 3 января 1900 года.
- 42005 – 1 января 2015 года (поскольку прошло 42 005 дней после 1 января 1900 года).
Время в Excel
Время хранится в Excel в виде десятичных дробей от 0,0 до 0,99999, которые представляют собой долю дня, где 0,0 – 00:00:00, а 0,99999 – 23:59:59.
- 0,25 — это 06:00.
- 0,5 — это 12:00.
- 0,541655093 — 12:59:59
Даты и время в Excel
Excel хранит дату и время в виде десятичных чисел, состоящих из целого числа, представляющего дату, и десятичной части, представляющей время.
- 1.25 – 1 января 1900 года, 6:00.
- 42005.5 — 1 января 2015 г., 12:00.
Как преобразовать дату в число в Excel
Если вы хотите узнать, какой серийный номер соответствует определенной дате или времени, отображаемому в ячейке, вы можете сделать это двумя способами.
1. Диалоговое окно «Формат ячеек»
Выделите ячейку с датой в Excel, нажмите Ctrl+1, чтобы открыть окно Формат ячеек, и перейдите на вкладку Общие.
Если вы просто хотите узнать порядковый номер, стоящий за датой, без фактического преобразования даты в число, запишите число, которое вы видите в разделе Образец, и нажмите Отмена, чтобы закрыть окно. Если вы хотите заменить дату числом в ячейке, нажмите кнопку ОК.
2. Функции Excel DATEVALUE и TIMEVALUE
Используйте функцию ЗНАЧДАТА() для преобразования даты Excel в порядковый номер, например =ЗНАЧДАТА("1/1/2015") .
Используйте функцию ВРЕМЗНАЧ(), чтобы получить десятичное число, представляющее время, например =ВРЕМЗНАЧ("6:30") .
Чтобы узнать и дату, и время, объедините эти две функции следующим образом:
=ДАТАЗНАЧ("1/1/2015") И ВРЕМЯЗНАЧ("6:00")
Примечание. Поскольку серийные номера Excel начинаются с 1 января 1900 года, а отрицательные числа не распознаются, даты до 1900 года в Excel не поддерживаются.
Если вы имеете дело со значениями даты и времени и хотите преобразовать время в десятичное число, ознакомьтесь с формулами, описанными в этом руководстве: Как преобразовать время в десятичное число в Excel.
Формат даты по умолчанию в Excel
При работе с датами в Excel краткий и длинный форматы даты извлекаются из региональных настроек Windows. Эти форматы по умолчанию отмечены звездочкой (*) в диалоговом окне Формат ячейки:
Форматы даты и времени по умолчанию в поле Формат ячейки изменяются, как только вы изменяете настройки даты и времени в панели управления, что приводит нас прямо к следующему разделу.
Как изменить форматы даты и времени по умолчанию в Excel
Если вы хотите установить другой формат даты и/или времени по умолчанию на своем компьютере, например изменить формат даты для США на стиль для Великобритании, перейдите на панель управления и нажмите "Регион и язык". Если панель управления открывается в представлении по категориям, нажмите Часы, язык и регион > Регион и язык > Изменить формат даты, времени или числа.
На вкладке Форматы выберите регион в разделе Формат, а затем задайте форматирование даты и времени, щелкнув стрелку рядом с форматом, который вы хотите изменить, и выбрав нужный из выпадающего списка:
Совет. Если вы не уверены, что означают разные коды (например, mmm, ddd, yyy), нажмите ссылку "Что означает обозначение" в разделе Форматы даты и времени. или ознакомьтесь с пользовательскими форматами даты Excel в этом руководстве.
Если вас не устраивает какой-либо формат времени и даты, доступный на вкладке Форматы, нажмите кнопку Дополнительные настройки в нижней правой части окна Регион и язык диалоговое окно. Откроется диалоговое окно Настройка, в котором вы переключитесь на вкладку Дата и введете пользовательский короткий и/или длинный формат даты в соответствующем поле.
Как быстро применить формат даты и времени по умолчанию в Excel
Microsoft Excel имеет два формата даты и времени по умолчанию — краткий и длинный, как описано в формате даты Excel по умолчанию.
Чтобы быстро изменить формат даты в Excel на формат по умолчанию, выполните следующие действия:
- Выберите даты, которые хотите отформатировать.
- На вкладке Главная в группе Число нажмите маленькую стрелку рядом с полем Формат числа и выберите нужный формат — краткая дата, длинная дата или время.
Если вам нужны дополнительные параметры форматирования даты, выберите в раскрывающемся списке Дополнительные числовые форматы или нажмите кнопку Диалоговое окно рядом с элементом Число. Откроется знакомое диалоговое окно Формат ячеек, где вы сможете изменить формат даты.
Как изменить формат даты в Excel
В Microsoft Excel даты могут отображаться различными способами. Когда дело доходит до изменения формата даты данной ячейки или диапазона ячеек, проще всего открыть диалоговое окно «Формат ячеек» и выбрать один из предопределенных форматов.
- Выберите даты, формат которых вы хотите изменить, или пустые ячейки, в которые вы хотите вставить даты.
- Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек. Кроме того, вы можете щелкнуть выбранные ячейки правой кнопкой мыши и выбрать Формат ячеек… в контекстном меню.
- В окне Формат ячеек перейдите на вкладку Число и выберите Дата в списке Категория.
- В разделе Тип выберите нужный формат даты. После этого в поле Образец отобразится предварительный просмотр формата с первой датой в выбранных данных.
- Если вас устраивает предварительный просмотр, нажмите кнопку ОК, чтобы сохранить изменение формата и закрыть окно.
Если формат даты на листе Excel не меняется, скорее всего, ваши даты отформатированы как текст, и вам нужно сначала преобразовать их в формат даты.
Как преобразовать формат даты в другой язык
После того, как у вас есть файл, полный зарубежных дат, вы, скорее всего, захотите изменить их на формат даты, используемый в вашей части мира. Допустим, вы хотите преобразовать американский формат даты (месяц/день/год) в европейский формат (день/месяц/год).
Самый простой способ изменить формат даты в Excel в зависимости от того, как даты отображаются на другом языке, заключается в следующем:
- Выберите столбец дат, который вы хотите преобразовать в другой язык.
- Нажмите Ctrl+1, чтобы открыть Формат ячеек .
- Выберите нужный язык в разделе «Язык (местоположение)» и нажмите «ОК», чтобы сохранить изменения.
Если вы хотите, чтобы даты отображались на другом языке, вам нужно будет создать собственный формат даты с кодом локали.
Создание пользовательского формата даты в Excel
Если ни один из предопределенных форматов даты Excel вам не подходит, вы можете создать свой собственный.
- На листе Excel выберите ячейки, которые нужно отформатировать.
- Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
- На вкладке Число выберите "Пользовательский" в списке Категория и введите нужный формат даты в поле "Тип".
- Нажмите "ОК", чтобы сохранить изменения.
Совет. Самый простой способ установить пользовательский формат даты в Excel — начать с существующего формата, близкого к тому, что вы хотите. Для этого сначала нажмите Дата в списке Категория и выберите один из существующих форматов в разделе Тип. После этого нажмите Пользовательский и внесите изменения в формат, отображаемый в поле Тип.
При настройке пользовательского формата даты в Excel можно использовать следующие коды.
| Код | Описание | Пример (1 января 2005 г.) |
| m | Номер месяца без начального нуля | 1< /td> |
| mm | Номер месяца с начальным нулем | 01 |
| mmm | Название месяца, короткая форма | Янв |
| мммм | Название месяца, полная форма | Январь |
| ммммм | Месяц как первая буква | J (обозначает январь, июнь и июль)< /td> |
| d | Номер дня без ведущего нуля | 1 |
| dd | Номер дня с начальным нулем | 01 |
| ddd | День недели, краткая форма | Пн |
| dddd | День недели, полная форма | Понедельник | < /tr>
| yy | Год (последние 2 цифры) | 05 |
| yyyy | Год (4 цифры) | 2005 |
| Код | Описание | Отображает as |
| h | Часы без начального нуля | 0-23 | < /tr>
| hh | Часы с ведущим нулем | 00-23 |
| m | Минуты без начального нуля | 0-59 |
| мм | Минуты с начальным нулем | 00-59 |
| s | Секунды без нуля в начале | 0-59 |
| ss | Секунды с нулем в начале | 00-59 |
| AM/PM | Периоды дня (если опущено, используется 24-часовой формат времени) | AM или PM |
Чтобы настроить формат даты и времени, включите единицы измерения даты и времени в код формата, например м/д/гггг ч:мм AM/PM. Если вы используете "m" сразу после "hh" или "h" или непосредственно перед "ss" или "s", Excel отобразит минут, а не месяца.
При создании пользовательского формата даты в Excel можно использовать запятую (,), тире (-), косую черту (/), двоеточие (:) и другие символы.
Например, одна и та же дата и время, например 13 января 2015 г., 13:03, могут отображаться по-разному:
| Формат | Отображается как |
| дд-ммм-гг | 13-янв-15 |
| мм/дд/гггг | 13/01/ 2015 |
| м/дд/гг | 13/01/15 |
| дддд, м/ д/гг ч:мм AM/PM | Вторник, 13/01/15 13:03 |
| ддд, мммм дд, гггг чч:мм :ss | Вт, 13 января 2015 г., 13:03:00 |
Как создать собственный формат даты Excel для другой локали
Если вы хотите отображать даты на другом языке, вам необходимо создать собственный формат и добавить к дате префикс соответствующего кода локали. Код локали должен быть заключен в [квадратные скобки] со знаком доллара ($) и дефисом (-). Вот несколько примеров:
- [$-409] – английский, штаты без названия
- [$-1009] – английский, Канада
- [$-407] – немецкий, Германия
- [$-807] — немецкий, Швейцария.
- [$-804] — Бенгальский, Индия
- [$-804] — Китайский, Китай
- [$-404] – Китай, Тайвань
Вы можете найти полный список кодов локалей в этом блоге.
Например, вот как можно настроить собственный формат даты Excel для китайского языка в формате год-месяц-день (день недели) и время:
На следующем изображении показаны несколько примеров одной и той же даты, отформатированной с использованием разных кодов локали традиционным для соответствующих языков способом:
Формат даты Excel не работает — исправления и решения
Обычно Microsoft Excel очень хорошо понимает даты, и вы вряд ли столкнетесь с какими-либо препятствиями при работе с ними. Если у вас возникла проблема с форматом даты в Excel, ознакомьтесь со следующими советами по устранению неполадок.
Ячейка недостаточно широка, чтобы вместить всю дату
Решение. Дважды щелкните правую границу столбца, чтобы изменить его размер в соответствии с датами. Кроме того, вы можете перетащить правую границу, чтобы установить нужную ширину столбца.
Отрицательные числа форматируются как даты
Если вы хотите отображать отрицательные числа как отрицательные даты, вам доступны два варианта:
Решение 1. Переключитесь на систему дат 1904 года.
Перейдите в раздел Файл > Параметры > Дополнительно, прокрутите вниз до раздела При расчете этой книги и выберите установите флажок Использовать систему дат 1904 и нажмите ОК.
В этой системе 0 соответствует 1 января 1904 года; 1 — 2 января 1904 г.; а -1 отображается как отрицательная дата -2 января 1904 года.
Конечно, такое представление очень необычно и требует времени, чтобы привыкнуть к нему, но это правильный путь, если вы хотите выполнять расчеты с более ранними датами.
Решение 2. Используйте функцию ТЕКСТ Excel.
Еще один возможный способ отображения отрицательных чисел в виде отрицательных дат в Excel — использование функции ТЕКСТ. Например, если вы вычитаете C1 из B1, а значение в C1 больше, чем в B1, вы можете использовать следующую формулу для вывода результата в формате даты:
=ТЕКСТ(ABS(B1-C1),"-d-ммм-гггг")
Возможно, вы захотите изменить выравнивание ячеек по правому краю, и, естественно, вы можете использовать любые другие настраиваемые форматы даты в формуле ТЕКСТ.
Примечание. В отличие от предыдущего решения, функция ТЕКСТ возвращает текстовое значение, поэтому вы не сможете использовать результат в других вычислениях.
Даты импортируются в Excel в виде текстовых значений
При импорте данных в Excel из CSV-файла или другой внешней базы данных даты часто импортируются в виде текстовых значений. Для вас они могут выглядеть как обычные даты, но Excel воспринимает их как текст и обрабатывает соответствующим образом.
Решение. Вы можете преобразовать «текстовые даты» в формат даты, используя функцию Excel DATEVALUE или функцию Text to Columns. Полную информацию см. в следующей статье: Как преобразовать текст в дату в Excel.
Вот как вы форматируете даты в Excel. В следующей части нашего руководства мы обсудим различные способы вставки дат и времени в листы Excel. Спасибо за внимание и увидимся на следующей неделе!
Маршалл Ганнелл
Маршалл Ганнелл
Писатель
Маршалл — писатель с опытом работы в сфере хранения данных. Он работал в Synology, а совсем недавно был директором по маркетингу и техническим писателем в StorageReview. В настоящее время он работает техническим писателем по API/программному обеспечению в Токио, Япония, управляет VGKAMI и ITEnterpriser и тратит то немногое свободное время, которое у него есть, на изучение японского языка. Подробнее.
Excel использует тот же формат даты, что и системные настройки вашего компьютера. Однако вы можете изменить формат дат, чтобы сделать его более доступным для пользователей в других регионах или сделать ваши данные более компактными.
Изменить формат даты
Для начала откройте электронную таблицу Excel, содержащую даты, которые вы хотите переформатировать. Выберите ячейки, содержащие каждую дату, щелкнув и перетащив на них указатель мыши.
После выбора нажмите стрелку вниз справа от текстового поля в группе "Число" на вкладке "Главная".
Появится раскрывающееся меню. В этом меню можно выбрать один из двух вариантов формата даты: короткая дата (17 января 2021 г.) и длинная дата (воскресенье, 17 января 2021 г.). Вы можете выбрать любой из них или, если это не тот формат, который вам нужен, нажмите «Другие числовые форматы» в нижней части меню. Откроется окно «Формат ячеек». Вы также можете нажать Ctrl+1 (Command+1 на Mac), чтобы открыть это окно.
Теперь вы окажетесь в категории «Дата» окна «Формат ячеек». В группе Тип выберите нужный формат. Если вы предпочитаете выбирать формат даты на основе того, как определенный язык и регион форматируют даты, вы можете выбрать параметр в раскрывающемся меню «Региональный стандарт».
Нажмите «ОК» в нижней части окна, когда вы выбрали формат, который хотите использовать. Даты в ранее выбранных ячейках изменятся на новый формат.
Создайте и используйте собственный формат даты
Существует множество различных форматов, но вы также можете создать свой собственный формат даты, если хотите. Для этого выберите ячейки, содержащие даты, которые вы хотите отформатировать, щелкнув и перетащив на них курсор.
Затем нажмите Ctrl+1 (Command+1 на Mac), чтобы открыть окно «Формат ячеек». Вы автоматически попадете в категорию Дата. Нажмите «Пользовательский» внизу списка категорий.
Далее вы увидите код в текстовом поле «Тип».
Вы можете отредактировать этот код, чтобы создать свой собственный формат. Каждая буква или буквы в коде представляют тип дисплея. Вот что означает каждая буква или буквы:
| Код | Отображается как: |
| м | Месяцы: 1-12 |
| мм | Месяцы: 01-12 |
| ммм | Месяцы: январь-декабрь |
| мммм | Месяцы: январь-декабрь |
| ммммм | Месяцы: первая буква месяца |
| d | Дни: 1–31 |
| dd | Дни: 01–31 |
| ddd | Дни: вс-сб |
| dddd | Дни: воскресенье-суббота |
| yy | Годы: 00–99 |
| гггг | Годы:1900-9999 |
Поэтому, если вы введете м/д/гг в поле "Тип" и затем нажмете "ОК", даты в выбранных ячейках будут отформатированы следующим образом:
Поэкспериментируйте с различными комбинациями кодов, чтобы найти идеальный формат, подходящий для вашей электронной таблицы.
При изменении формата даты учитывайте, кто будет просматривать таблицу. Если ваша электронная таблица содержит большой объем данных и вы хотите просмотреть данные за определенный период времени, вы можете отсортировать данные по дате независимо от используемого формата.
- › Как создать сводную таблицу в Microsoft Excel
- › Как найти день недели по дате в Microsoft Excel
- › Как вставить сегодняшнюю дату в Microsoft Excel
- › Что такое GrapheneOS и как она делает Android более приватным?
- › 5 вещей, которые вы, вероятно, не знали о GIF-файлах
- › Сколько оперативной памяти требуется вашему ПК?
- › Matter — это долгожданный стандарт умного дома
- › 7 основных функций Microsoft Excel для составления бюджета
Когда вы вводите дату в формате с косой чертой, Excel распознает ее как дату.Например, Excel автоматически выберет формат даты для ячейки, содержащей 11.11.19.
Вы можете настроить Excel для отображения дат различными способами, независимо от того, как вы вводите данные.
Вот как найти и изменить формат даты в Excel на Mac или ПК.
Ознакомьтесь с продуктами, упомянутыми в этой статье:
Microsoft Office (от 149,99 долларов США в магазине Best Buy)
MacBook Pro (от 1299,99 долларов США в Best Buy)
Lenovo IdeaPad (от 299,99 долларов США в Best Buy)
Как изменить формат даты в Microsoft Excel
Мы используем электронную таблицу, включающую даты найма сотрудников вымышленной компании, чтобы показать различные способы форматирования дат в Excel.
<р>1. Выделите ячейки даты, которые вы хотите отформатировать. <р>2. В ленте меню «Главная» посередине есть поле с выпадающим меню для разных форматов ячеек. По умолчанию должно быть указано «Дата». Нажмите на стрелку, чтобы открыть раскрывающееся меню.Раскрывающееся меню форматирования находится в разделе «Главная» панели инструментов. Лаура МакКэми/Business Insider
<р>3. В меню есть два формата дат: короткая дата и длинная дата.- Краткая дата отображает месяцы и дни как однозначные или двузначные числа, а годы - как двузначные числа. Например, 1 января 2019 года будет отображаться как 01.01.19.
- Длинная дата добавляет день недели и записывает месяц. Таким образом, 01.01.19 будет отображаться как вторник, 1 января 2019 года. Дата будет отображаться в выбранном вами формате, независимо от того, как вы ее вводите. Если вы введете «01.01.19» в ячейку с длинным форматом даты, Excel изменит ее на развернутую дату.
Совет: никогда не меняйте формат ячейки с датой на общий или текстовый. Excel превратит вашу дату в несвязанное число. Например, 03.03.2017 становится 42797, если оно не отформатировано как дата.
Читайте также:
- Как слепить ноутбук из пластилина
- Рейтинг компьютерных наушников
- Где хранится информация о том, как воспроизводить ноты, записанные в миди-файле
- Как отключить javascript в tor
- Как проверить кабель HDMI на работоспособность в домашних условиях