Как преобразовать количество дней в годы, месяцы и дни в Excel

Обновлено: 14.05.2024

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

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

Эта функция возвращает количество дней между двумя датами при условии, что дата в E7 меньше или равна дате в F7. Третий аргумент, "d", заставляет DATEDIF возвращать результат в днях. Вы также можете указать месяцы ("m") и годы ('y"). Однако для целей этого примера есть несколько других аргументов, которые особенно полезны: месяцы без учета лет ("ym"), дни без учета лет ( "yd") и дни, исключая месяцы и годы ("md").

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

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

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

Эта формула становится довольно длинной, но она возвращает только те единицы, для которых есть значение. Таким образом, вместо «0 лет, 2 месяца, 1 день» будет возвращено «2 месяца, 1 день».

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

Конвертировать дни в годы, месяцы, дни

Если вы хотите преобразовать заданные числа дней в годы, месяцы, дни на листе, вы можете использовать формулу Excel, основанную на функции РАЗНДАТ. Вот так:

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

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

Преобразование общего числа в стандартную дату

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

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

Прибавление целых часов или десятичных часов к заданному времени в Excel – довольно простой процесс. Вы можете сделать это, используя функции Start_time+TIME(hours,0,0) и Start_time+hours/24. В этом руководстве по добавлению часов к времени мы покажем вам, как это сделать.

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

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

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

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

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

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

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

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

Для расчета и отображения времени между двумя датами в днях, месяцах и годах можно использовать формулу, основанную на функции РАЗНДАТ. В показанном примере формула в E5 выглядит следующим образом:

где даты начала указаны в столбце B, а даты окончания — в столбце C.

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

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

Решение DATEDIF

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

В приведенном примере мы вычисляем годы, месяцы и дни отдельно, а затем "склеиваем" результаты с помощью конкатенации. Чтобы получить целые годы, целые месяцы и дни между датами, мы используем DATEDIF следующим образом, изменяя только аргумент единица измерения.

Поскольку мы хотим создать строку, которая добавляет единицы измерения к каждому числу, мы объединяем число, возвращаемое DATEDIF, с именем единицы с помощью оператора амперсанда (&) следующим образом:

Наконец, нам нужно расширить приведенную выше идею, включив в нее пробелы и запятые и объединив все вместе в одну строку:

Реализация функции LET

Функция ПУСТЬ (новая функция в Excel 365) может упростить некоторые формулы, позволяя определять и повторно использовать переменные. Чтобы использовать функцию LET в этой формуле, нам нужно подумать о переменных. Основная цель переменных — определить полезное имя, которое можно повторно использовать в другом месте кода формулы. Глядя на формулу, есть как минимум пять возможностей объявить переменные. Первые две — это дата начала и дата окончания, которые повторно используются в формуле.После того как мы присвоим значения началу и концу, нам нужно только один раз обратиться к ячейкам C5 и D5. В качестве первого шага мы можем добавить разрывы строк, а затем определить переменные для start и end следующим образом:

Обратите внимание, что все экземпляры B5 и C5 в DATEDIF были заменены на начало и конец. Результат этой формулы такой же, как и исходная формула выше, но ссылка на B5 и C5 встречается только один раз. Даты начала и окончания затем повторно используются во всей формуле. Это облегчает чтение формулы и помогает уменьшить количество ошибок.

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

Обратите внимание, что мы присвоили результаты DATEDIF переменным годы, месяцы и дни, и эти значения объединены вместе в последнем строка внутри функции LET. В функции LET последним аргументом является вычисление или значение, которое возвращает окончательный результат.

Познакомьтесь с LET

После того как у нас будет работать базовая версия LET, мы можем легко расширить формулу, чтобы выполнять более сложную обработку, без избыточного повторного выполнения одних и тех же вычислений. Например, мы могли бы захотеть сделать единицы во множественном или единственном числе в зависимости от фактического номера единицы. В приведенной ниже формуле добавлены три новые переменные: ys, ms и ds для хранения строк, связанных с каждой единицей измерения:

Строки начинаются в единственном числе (т. е. "год", а не "годы"). Затем мы используем функцию ЕСЛИ, чтобы условно добавить "s" только тогда, когда число не равно 1. Если число равно 1, ЕСЛИ возвращает пустую строку ("" ) и имя модуля остается в единственном числе. Поскольку мы добавляем имя модуля к номеру модуля, когда определяем ys, ms и ds, последний шаг внутри LET проще; нам нужно только объединить единицы с запятой и пробелом.

Подробнее о функции LET см. здесь. Кроме того, см. пример подробной функции LET для другого объяснения преобразования существующей формулы для использования LET.

Вот лучшая формула Excel для преобразования количества дней в текст для года, месяцев и дней. В этой формуле не учитываются нулевые значения и правильное единственное/множественное число (например, не «1 год 0 месяцев 27 дней», а «1 год 27 дней»).

И мы покажем лучший способ написать формулу в Excel 365, 2021/LTSC с помощью функции Let().

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

Где «A1» — целое число дней.

См. ниже еще лучшую версию с использованием функции Let() в Excel 365/2021 и LTSC.

Обычная формула

Обычная формула преобразования дней в текстовую строку выглядит следующим образом:

Где A1 – целое число дней.

Как видите, он использует функцию Excel DateDif() для разделения дней на годы, месяцы и дни. DateDif обычно работает, вычисляя количество дней между двумя датами (первые два параметра). Но можно работать с одним числом дней, поставив 0 (ноль) в первый параметр.

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

Исправить эти мелкие недочеты можно с помощью дополнительных операторов IF.

Давайте рассмотрим это шаг за шагом.

Удаление нулевых значений

Остановка «0 лет» и т. д. означает добавление трех тестов ЕСЛИ для захвата нулевых значений. Например
ЕСЛИ (число <> 0, , )

Вся формула такова:

Исправление единственного/множественного числа

Теперь добавьте три теста для значений больше единицы, чтобы при необходимости добавить букву "s".

Вся формула с исправлением нуля такова:

Используйте Let() для более читаемого кода

Это работает для любой версии Excel, но есть еще лучший вариант для Excel 365, Excel 2021 и Excel LTSC, которые имеют (будут иметь) функцию Let(). Let() позволяет установить переменную для использования в этой формуле.

В данном случае это означает однократную настройку DaysCount вместо изменения восьми представлений. Let() также может ускорить вычисления, но это преимущество очень мало для этой простой формулы.

Вот версия приведенной выше формулы с помощью LET(). Третий параметр аналогичен приведенной выше формуле, за исключением того, что значение дней теперь является переменной с именем DaysCount.

DaysCount именуется и определяется первыми двумя параметрами Let()

Сколько лет в годах, месяцах и днях

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

Количество дней вычисляется функцией Let() как Today() минус день рождения как порядковая дата в Excel.

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