Developing.ru
Универсальная формула перевода дат в годы, месяцы, недели.
. дни, часы, минуты секунды.
Возможно ли создать такую формулу?
То есть, например, вводятся 2 значения:
14.01.1946 23:48:51
и
12.12.2005 16:00:02
Можно ли в Экселе написать такую формулу (или несколько), чтобы введя две даты можно было на выходе (в разных ячейках) получить целое число: лет, месяцев, недель, дней, часов, минут, секунд.
Какое название соответствует 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
ДОЛЯГОДА
Возвращает долю года, которую составляет количество дней между двумя датами.
Возврат функцией значения ошибки #ИМЯ? свидетельствует о необходимости установить библиотеку msowcf.dll.
ДОЛЯГОДА(нач_дата;кон_дата;базис)
Нач_дата — это дата, соответствующая начальной дате.
Кон_дата — это дата, соответствующая конечной дате.
Базис — это число от 0 до 4, которое определяет используемый способ вычисления дня.
В оригинальном релизе есть функция DATEDIF.
Если офис русифицированный (или локализованный для другой страны), то среди его файлов есть Funcs.xls.
Этот файл показывает соответствие оригинальных и локализованных названий функций, а также содержит гиперссылки на соответствующие разделы справочной системы.
Черт. Щас пойду русский офис где нибудь поищу.
Функция называется РАЗНДАТ
Хм. Прикольно! В русском офисе по ней нет хелпа.
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.
Перевод дней в года месяцы и дни excel
О проблемах с регистрацией пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail, а тут можно восстановить пароль.
| Регистрация Есть количество дней 10232 дня (стаж работы). Просмотрел форум, не нашел. Пример вложен. Данные формулы выдают 28 лет 0 месяцев но не 19 дней, а 12 | |||||
Выделить из количества дней — годы, месяцы и дни есть алгоритм — найдуться и значения, но по сути
10232-й день начиная с 01.01.1900 это 5.01.1928
из которого легко определить стаж
1928-1900 = 28 лет
1-1 = 0 месяцев
5 дней
а что вам нужно — не понимаю
давайте не будет «тянуть кота за резину»
для примера выше:
непрерывный стаж работы с 01.01.1987 по 05.01.2015
без программ, пользуясь простой логикой:
если бы отработал по 01.01.2015 это было бы 28 лет и 1 день (начальная и конечная дата — это отработанные дни)
так вот с 01.01.1987 по 05.01.2015 отработано
2015 — 1987 = 28 лет, 1-1 = 0 месяцев, 5-1+1 = 5 дней. так?
идем дальше
если человек работал с 01.02.2015 по 01.02.2015 — это сколько дней он отработал?, правильно 01.02.2015 — 01.02.2015 +1 = 1 день
возвращаемся к нашему примеру и теперь спросим Ексель сколько будет
05.01.2015 — 01.01.1987 + 1?
правильно 10232 дня?
выше мы выяснили что это 28 лет 0 месяцев и 5 дней
а не 28 лет 0 месяцев и 12 или 19 дней как насчитали Вы и Ваша официальная (правильно считающая) программа для суммарного стажа 10232 дня
365,25 дня (а не 365)
10232/365,25=28 лет и 5 дней в остатке
Если считать потом и месяца, то иметь ввиду, что в месяце в среднем
30,4375 дня (а не 30,5 хотя это и не сильно отличается)
Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в Excel
В таблицах Excel предусмотрена возможность работы с различными видами текстовой и числовой информации. Доступна и обработка дат. При этом может возникнуть потребность вычленения из общего значения конкретного числа, например, года. Для этого существует отдельные функции: ГОД, МЕСЯЦ, ДЕНЬ и ДЕНЬНЕД.
Примеры использования функций для обработки даты в Excel
Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.
Функция ГОД используется аналогично смежным:
Все они отображают числовые значения, соответствующие григорианскому календарю. Даже если в таблице Excel для отображения введенной даты был выбран календарь Хиджра, то при вычленении года и других составных значений посредством функций, приложение представит число, которое является эквивалентом по григорианской системе летоисчисления.
Чтобы воспользоваться функцией ГОД, нужно ввести в ячейку следующую формулу функции с одним аргументом:
=ГОД(адрес ячейки с датой в числовом формате)
Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.
Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.
Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.
Введем исходные данные в Excel.
Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:
=ГОД (адрес ячейки, из даты которой нужно вычленить значение года)
В результате мы извлекаем года из каждой даты.
Аналогичный пример работы функции МЕСЯЦ в Excel:
Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:
Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:
Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):
Если пропустить второй необязательный для заполнения аргумент, тогда будет использоваться формат по умолчанию (английский с воскресенья-1 по суботу-7).
Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:
Получим более понятный вид реализации данной функции.
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.