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

В этой статье вы научитесь и узнаете как работает функция ГОД в Excel, функция МЕСЯЦ в Excel и функция ДЕНЬ в Excel. Узнаете для чего нужны эти функции времени.

Содержание

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

Мы можем легко подсчитать дни между двумя датами в Excel, но как отобразить количество дней как год / месяц / день, например “1 год 4 месяца 25 дней«? А что, если преобразовать число ггггммдд в обычную дату? В этой статье рассказывается о преобразовании чисел в год / месяц / день или даты в Excel.

Преобразование чисел (дней) в год / месяц / день

Этот метод представит формулу для преобразования количества дней в год / месяц / день, например “1 год 4 месяца 25 дней”в Excel. Сделайте следующее:

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

= РАЗНДАТ (0, A2, «y») & «годы» & РАЗНДАТ (0, A2, «ym») & «месяцы» и РАЗНДАТ (0, A2, «мд») & «дни»

Примечание: A2 – это ячейка с количеством дней, которое вы преобразуете в год / месяц / день.

2. Продолжайте выбирать ячейку с результатом преобразования и перетаскивайте ее маркер заполнения в нужный диапазон. И тогда вы увидите, что все числа преобразованы в год / месяц / день. Смотрите скриншот:

Только один щелчок, чтобы удалить все формулы, но сохранить рассчитанные значения из нескольких ячеек

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Преобразуйте числа (дни) в год / месяц / день с помощью удивительного инструмента

Некоторым пользователям может быть трудно запомнить формулы для преобразования количества дней в год / месяц / дни в Excel. Здесь я рекомендую Formula Helper из Kutools for Excel, чтобы быстро завершить разговор, не запоминая болезненные формулы.

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Выберите ячейку, в которую вы поместите результат расчета, и нажмите Kutools > Формула Помощник > Формула Помощник для включения функции.

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

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

Преобразование общих чисел в дату форматирования даты по умолчанию

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

Выберите 5-значные числа и нажмите Главная > Формат номера коробка> Короткое свидание. И тогда вы увидите, что все выбранные 5-значные числа сразу преобразуются в даты с форматированием даты по умолчанию.

Преобразование общих чисел в дату с настраиваемым форматированием даты

Kutools для Excel Применить форматирование даты Утилита может помочь вам быстро преобразовать 5-значные числа в даты с настраиваемым форматированием даты.

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Выберите 5-значные числа и нажмите Kutools > Формат > Применить форматирование даты.

2. В открывшемся диалоговом окне «Применить форматирование даты» выберите указанное форматирование даты, которое вы будете использовать в Форматирование даты и нажмите Ok кнопка. И тогда вы увидите, что все числа преобразованы в даты с указанным форматированием даты. Смотрите скриншот:

Один щелчок, чтобы преобразовать число ггггммдд на сегодняшний день

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

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Выделите ячейки со специальными номерами ггггммдд и нажмите Kutools > Content > Преобразовать в дату. Смотрите скриншот:

И тогда вы увидите, что все выбранные числа согласованы с обычными датами. Смотрите скриншот:

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

Демонстрация: преобразование чисел на дату в Excel

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

офисный дно

Примеры использования функций для обработки даты в Excel

Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

Функция ГОД используется аналогично смежным:

  • МЕСЯЦ;
  • ДЕНЬ;
  • ДЕНЬНЕД.

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

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

=ГОД(адрес ячейки с датой в числовом формате)

Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.

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

Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

Введем исходные данные в Excel.

исходные данные в Excel.

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

=ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

ГОД.

В результате мы извлекаем года из каждой даты.

Аналогичный пример работы функции МЕСЯЦ в Excel:

МЕСЯЦ.

Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

ДЕНЬ.

Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

ДЕНЬНЕД.

Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):

Во втором опциональном аргументе.

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

Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

ИНДЕКС и ДЕНЬНЕД.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Как отобразить все скрытые строки или столбцы в Excel?

Василий19:36:00 Add Comment

При работе в Excel вы скрыли много строк и/или столбцов и теперь их надо всех быстро отобразить? Прочитайте эту статью, и вы узнаете, как эт…

Далее…

Основная формула Excel расчета для возраста в годах.

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

Если предположить, что день рождения (ДР) записан в ячейке B2, то вычислить возраст в годах можно следующим образом:

=(СЕГОДНЯ()-B2)/365

=(TODAY()-B2)/365

Первая часть выражения (СЕГОДНЯ()-B2) возвращает разницу между текущей и датой рождения в днях. А затем вы делите это на 365, чтобы получить количество лет.

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

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

=ЦЕЛОЕ((СЕГОДНЯ()-B2)/365)

Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого.

=ОКРУГЛ((СЕГОДНЯ()-B3)/365;0)

Округляет количество прожитых лет по стандартным правилам в зависимости от значения дробной части (более 0,5 — в большую сторону).

=ОКРУГЛВНИЗ((СЕГОДНЯ()-B4)/365;0)

Округляет в меньшую сторону.

=ОТБР((СЕГОДНЯ()-B5)/365;0)

Отбрасывает дробную часть, оставляя только целое.

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

Недостатки:  использование этого выражения в Экселе дает довольно точные результаты, но не безупречные. Деление на среднее число дней в году в большинстве случаев работает нормально, но иногда все же показывает неверный возраст. Например, если кто-то родился 29 февраля, а сейчас 28 февраля, программа сделает человека на один день старше.

В качестве альтернативы вы можете использовать в знаменателе 365,25 вместо 365, поскольку каждый четвертый год состоит из 366 дней. Однако и этот подход не идеален. Например, если вы рассчитываете возраст ребенка, который еще не прожил високосный год, деление на 365,25 дает неправильный результат.

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

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

Таблица перевода из Суток в Год

Сутки 1 000 5 000 10 000 25 000 50 000 100 000 250 000 500 000
Год 2.74 13.699 27.397 68.493 136.986 273.973 684.932 1 369.863

Таблица

Лет Сколько дней
1 365
5 1825
10 3650
20 7300
30 10950
50 18250
100 36500
200 73000
300 109500
400 146000
500 182500
1000 365000
2000 730000
5000 1825000

Похожие калькуляторы:

  • ico.png Посчитать дни от даты до даты
  • ico.png Перевести часы в года
  • ico.png Перевести секунды в часы
  • ico.png Разница в возрасте
  • ico.png Перевести часы в дни
  • ico.png Перевести дни в недели
  • ico.png Перевести часы в недели

Как в Excel хранится время

Excel сохраняет время, используя тот же формат порядковой нумерации, что и для дат. День у Excel «начинается», как и у нас с вами в полночь. Каждый час составляет 1/24 дня, поэтому он представлен в виде десятичного значения: 0,041666…

Если Вы хотите узнать, что «видит» Excel, когда вы записываете в ячейку 9:00 утра, то умножьте: 0,041666 на 9.

Одна минута в Excel представлена числом: 1 / (24 * 60) = 1/1440 = 0,000694.

Секунда составляет 1 /(24 * 60 * 60 )= 1/86400 = 0,00001157407…

Поэтому если вы запишете в ячейку 8:30 утра, Excel совершит следующий расчет:

(8 * (1/24)) + (30 * (1/1440)) = 0,354167

Так что, если вы опаздываете на работу на полчаса и хотите сообщить об этом своему боссу, напишите ему / ей и скажите, что будете на месте через 0,02083333….

Если время указано без даты, например, 9:00, Excel сохраняет его, как 9:00 0 января 1900 года. Это может затруднить выполнение математических расчетов для значений времени (без даты), поскольку вычитание 6:00 из 3:00 станет отрицательным и будет считаться ошибкой, так как  0,125 — 0,25 = -0,125 , что будет отображаться как #########.

Если же время указано вместе с датой, то проблем не возникнет. Так 11:00 20 августа 2020 года будет сохранено как 44063,46.

формат времени в excel

Вставка текущей даты и времени.

В Microsoft Excel вы можете сделать это в виде статического или динамического значения.

Как вставить сегодняшнюю дату как статическую отметку.

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

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

  • Ctrl + ; (в английской раскладке) или Ctrl+Shift+4 (в русской раскладке) вставляет сегодняшнюю дату в ячейку.
  • Ctrl + Shift + ; (в английской раскладке) или Ctrl+Shift+6 (в русской раскладке) записывает текущее время.
  • Чтобы вставить текущую дату и время, нажмите Ctrl + ; затем нажмите клавишу пробела, а затем Ctrl + Shift +;

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

Как сделать, чтобы дата оставалась актуальной?

Если вы хотите вставить текущую дату, которая всегда будет оставаться актуальной, используйте одну из следующих функций:

  • =СЕГОДНЯ()- вставляет сегодняшнюю дату.
  • =ТДАТА()- использует текущие дату и время.

2020-04-05_2.png

В отличие от нажатия специальных клавиш, функции ТДАТА и СЕГОДНЯ всегда возвращают актуальные данные.

А если нужно вставить текущее время?

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

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

=ТДАТА()-СЕГОДНЯ()

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

При использовании формул имейте в виду, что:

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

Калькуляторы по физике

Решение задач по физике, подготовка к ЭГЕ и ГИА, механика термодинамика и др. Калькуляторы по физике
  

Функции извлечения дат

ДЕНЬ(), МЕСЯЦ() и ГОД()

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

Для этого в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД. Синтаксис прост:

Как показать сразу несколько скрытых листов в Excel?

Василий18:50:00 Add Comment

При работе с большими книгами в Excel, часто возникает необходимость скрыть лишние листы, что бы они не мешались в работе. Но что делать, ес…

Далее…

Измерение физических величин

Содержит информацию о способах измерения физических величин и о измерительных приборах.
Измерение физических величин
  

Дополнительные функции извлечения даты

ДЕНЬНЕД() и НОМНЕДЕЛИ()

Довольно часто, бывает так: нам говорят: «Давайте встретимся 24.08.2020!». Многие люди не могут по дате сориентировать: свободны они или нет. У нас так условно «заведено» — мыслить днями недели. Для нас понедельник, например, (какая бы дата ни была) – день тяжелый, а вот пятница – «проказница».

Иногда полезно найти зависимость продаж от дней недели. Как это сделать?! Не станешь же все даты размечать.

На такой случай есть полезная функция: ДЕНЬНЕД(). Эта функция выдает в качестве результата число от 1 до 7, которое соответствует дню недели. Синтаксис очень прост:

= ДЕНЬНЕД (дата_в_числовом_формате, [тип])

Параметр [тип] показывает, с какого числа начинается неделя и может принимать следующие значения:

Замечание! Кто внимательно смотрел, те заметили, что варианты 1 и 17; 2 и 11 – одинаковые. Действительно, функционально эти значения одинаковы. Но первые значения предназначены для совместимости с более ранними версиями Excel.

По умолчанию параметр [тип] принимает значение 1 (то есть неделя начинается с воскресенья). Но в России первым днем недели принято считать понедельник, поэтому для расчетов мы будет использовать значение параметра [тип] = 2.

Функция НОМНЕДЕЛИ() возвращает, как понятно из названия, номер недели, на которую приходится конкретная дата.

Синтаксис совпадает с предыдущей формулой.

Рассмотрим работу функции на примере:

Видно, что 20 августа 2020 года – это четвертый день недели (четверг) и выпадает на 34 неделю с начала года.

Значение параметра [тип_возвр] принимает фиксированные значения из списка:

Developing.ru

Универсальная формула перевода дат в годы, месяцы, недели.

. дни, часы, минуты секунды.
Возможно ли создать такую формулу?
То есть, например, вводятся 2 значения:

14.01.1946 23:48:51
и
12.12.2005 16:00:02

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

file.php?avatar=34_1555601700.jpg

Какое название соответствует DATEDIF в русифицированном офисе не помню.
Найдите у себя файл Funcs.xls и посмотрите там русское название.

Или то же самое, если не хотите скачивать:

$C$6 = 15.12.2005 00:00:00
$C$7 = 15.12.2005 10:15:15
$C$9=$C$7-$C$6

file.php?avatar=34_1555601700.jpg

ДОЛЯГОДА
Возвращает долю года, которую составляет количество дней между двумя датами.
Возврат функцией значения ошибки #ИМЯ? свидетельствует о необходимости установить библиотеку msowcf.dll.

ДОЛЯГОДА(нач_дата;кон_дата;базис)
Нач_дата — это дата, соответствующая начальной дате.
Кон_дата — это дата, соответствующая конечной дате.
Базис — это число от 0 до 4, которое определяет используемый способ вычисления дня.

file.php?avatar=34_1555601700.jpg

В оригинальном релизе есть функция DATEDIF.
Если офис русифицированный (или локализованный для другой страны), то среди его файлов есть Funcs.xls.
Этот файл показывает соответствие оригинальных и локализованных названий функций, а также содержит гиперссылки на соответствующие разделы справочной системы.

Черт. Щас пойду русский офис где нибудь поищу.

file.php?avatar=34_1555601700.jpg

Функция называется РАЗНДАТ

Хм. Прикольно! В русском офисе по ней нет хелпа.

Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.

Start_date is a date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, «2001/1/30»), as serial numbers (for example, 36921, which represents January 30, 2001, if you’re using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE(«2001/1/30»)). For more information about date serial numbers, see NOW.

End_date is a date that represents the last, or ending, date of the period.

Unit is the type of information you want returned.

Unit Returns
«Y» The number of complete years in the period.
«M» The number of complete months in the period.
«D» The number of days in the period.
«MD» The difference between the days in start_date and end_date. The months and years of the dates are ignored.
«YM» The difference between the months in start_date and end_date. The days and years of the dates are ignored.
«YD» The difference between the days of start_date and end_date. The years of the dates are ignored.

Microsoft Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.

Excel for Windows and Excel for the Macintosh use different date systems as their default. For more information, see NOW.
Examples

DATEDIF(«2001/1/1″,»2003/1/1″,»Y») equals 2, or two complete years in the period.

DATEDIF(«2001/6/1″,»2002/8/15″,»D») equals 440, or 440 days between June 1, 2001, and August 15, 2002.

DATEDIF(«2001/6/1″,»2002/8/15″,»YD») equals 75, or 75 days between June 1 and August 15, ignoring the years of the dates.

DATEDIF(«2001/6/1″,»2002/8/15″,»MD») equals 14, or the difference between 1 and 15 — the day of start_date and the day of end_date — ignoring the months and the years of the dates.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...