Как разделить дату рождения по столбцам в Excel

Обновлено: 20.11.2024

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

Разделить дату на день, месяц и год с помощью формул

Предполагается, что у вас есть список данных в диапазоне A1:A6, которые содержат значения даты. И вы хотите разделить каждое значение даты на три отдельных столбца для дня, месяца и года. Как этого добиться. Вы можете использовать три формулы, основанные на функции ДЕНЬ, функции месяца и функции года, чтобы получить результат разделения дат. Просто выполните следующие действия:

Эта формула извлечет значение дня из заданной даты в диапазоне A2:A6.

Эта формула извлечет значение месяца из заданной даты в диапазоне A2:A6

Вы должны увидеть, что все даты в диапазоне A2:A6 разделены на отдельные дни, месяцы и годы.

Разделить дату на день, месяц и год с помощью функции «Текст в столбец»

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

Видео: разбить дату на день, месяц и год

Связанные функции

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

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

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

Иногда нам нужно преобразовать формат даты и времени в формат только даты в Excel, например, преобразовать 29.01.2019 06:51:03 в 29.01.2019, мы можем преобразовать формат с помощью формулы или настроек формата. Эти два способа просты в освоении, поэтому вы .

Иногда нам нужно получить информацию о годе из формата даты и времени, чтобы год отображался только в Excel. Например, конвертируя 29.01.2019 06:51:03 в 2019, мы можем получить информацию о годе по формуле или настройкам формата. Два пути.

В этом посте рассказывается, как рассчитать оставшиеся дни в заданном месяце или году в Excel. Как рассчитать количество дней, оставшихся в месяце или году, используя формулу в Excel 2013/2016. Рассчитать остаток.

В этом посте рассказывается, как разделить ячейки с разделителями-запятыми на новые строки или столбцы в Excel. Как разделить значения, разделенные запятыми, на строки с помощью функции «Текст в столбец» в Excel. Как преобразовать текст, разделенный запятыми.

В этом посте рассказывается, как сортировать даты или дни рождения по месяцам или дням только в Excel. Как сортировать даты по году, месяцу или дню только с помощью формулы в Excel 2013/2016. Как использовать .

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

В этом посте рассказывается, как разделить несколько строк в ячейке на отдельные строки или столбцы в Excel. Как разделить содержимое ячейки на несколько ячеек с помощью функции «Текст в столбцы» в Excel. Разделить несколько строк.

Чтобы выполнить это разделение, мы должны помнить, что 1 представляет 1 день.

Время – это часть суток, например 0,33333 – 8 часов (1/3 суток). Ознакомьтесь с этой статьей о формате даты и времени.

Шаг 1. Извлеките только дату

Итак, чтобы извлечь дату, нужно просто вставить значение в столбец A функции INT.

INT означает целое число, поэтому вы извлекаете всю часть ячейки в столбце A. Целое число — это дата (десятичная часть — это время).

Возможно, вам потребуется изменить формат столбца B, чтобы отобразить формат "Дата".

Шаг 2. Извлеките время

Чтобы извлечь время, просто вычтите данные в столбце A из всего значения, рассчитанного в столбце B.

Опять же, возможно, вам придется изменить формат столбца C, чтобы отобразить формат ячейки на «Время»

И конечный результат

Как разделить дату с помощью Power Query?

С Power Query методы более просты. Давайте начнем с этих дат, встроенных в таблицу.

Шаг 1. Загрузите даты в Power Query

После того, как ваши данные будут в таблице, вы можете загрузить их в Power Query с помощью меню "Данные" > "Из таблицы/диапазона"

А затем в Power Query вы можете визуализировать свои данные в Power Query

Шаг 2. Измените тип ваших данных

Как видно из заголовка, у вас есть символ ABC/123. Это означает, что Power Query считает ваши данные «любыми»; не текст, не дата, не число.

Обязательно изменить тип ваших данных на Дата и время

Кроме того, тип данных вашего столбца теперь "Дата/время", а пиктограмма другая

Шаг 3. Извлечение даты

Поскольку ваши данные имеют тип Дата и время, извлечь дату очень просто.

И результат потрясающий; новый столбец только с датой

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

Знание того, как даты и время хранятся в Excel, позволяет легко разделить их на отдельные ячейки.

Разделить даты и время

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

Функция INT

Сначала мы будем использовать функцию INT для возврата целочисленного значения, представляющего дату:

Функция TRUNC
Или мы можем использовать функцию TRUNC, чтобы обрезать десятичное значение (время):

Функция ОКРУГЛВНИЗ

Стоимость времени

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

Настройка форматирования

Наконец, перейдите на ленту «Главная» > «Число» и измените формат ячейки с «Общий» на «Краткая дата» (для даты) и «Время» (для времени):

Окончательный результат показан ниже.

Разделить дату и время с форматированием

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

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

Вы можете изменить формат даты в меню "Форматирование ячейки":

Введите «м/дд/гггг» в поле «Тип», чтобы задать формат даты, или «ч:мм AM/PM», чтобы задать формат времени.

Для доступа к меню форматирования ячеек используйте сочетание клавиш CTRL + 1 или нажмите эту кнопку:

Функция ТЕКСТ

Вы также можете использовать функцию ТЕКСТ в отдельных ячейках для хранения значений даты и времени отдельно в виде текста:

Мы можем легко разделить текст и числа из ячейки, используя различные комбинации функций, таких как НАЙТИ, МИН, ДЛСТР, ВЛЕВО и ПРАВО. В приведенных ниже шагах описан процесс разделения текста и чисел, содержащихся в ячейке.

Рисунок 1. Разделение текста и чисел с помощью формулы

Настройка данных

Мы настроим данные, вставив имена пяти студентов колледжа в столбец A, включая дату их рождения, не оставляя пробела между именем и датой рождения.

Мы также введем должность, имя и дату рождения в ячейки B3, ячейки C3 и ячейки D3 соответственно.

Рисунок 2. Настройка данных

Основная формула

Мы можем использовать базовую формулу для разделения текста и чисел из ячейки в соответствии с функцией ВЛЕВО или ВПРАВО в соответствии с требованиями.

Определение положения ПЕРВОГО ЧИСЛА в ячейках A4 и A8

Мы вставим приведенную ниже формулу в ячейку B4.

Рисунок 3: Позиция первого числа

Рисунок 4: Позиция первого числа

Как показано на рис. 4, эта формула возвращает позицию первого числа в ячейке A4. JOHN состоит из четырех букв, следующее значение — не текст, а число, следовательно, позиция первого числа — 5
Мы будем использовать функцию раскрывающегося списка, чтобы получить позиции первого числа для оставшихся четырех учащихся.

Рисунок 5: Позиция первого числа

Извлечение текста с помощью функции LEFT

Мы будем использовать приведенную ниже формулу для извлечения текста (имен) из столбца A:

Мы вставим эту формулу в ячейку C4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.

Рис. 6. Разделение текста и чисел с помощью функции ВЛЕВО

Извлечение чисел с помощью функции ПРАВИЛЬНО

Мы будем использовать приведенную ниже формулу для извлечения чисел (даты рождения) из столбца А:

=ВПРАВО(A4,ДЛСТР(A4)-B4+1)

Мы вставим эту формулу в ячейку D4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.

Рис. 7. Разделение текста и чисел с помощью функции RIGHT

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Читайте также: