Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: Excel хранит дату как число дней, прошедших с 1 января 1900 года. Это означает, что все функции даты не работают для дат XVIII в. и более ранних. Это проблема для историков и генеалогов (людей, занимающихся генеалогией).
Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой — 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.
Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.
Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу
Скачать заметку в формате Word или pdf, примеры в формате Excel
Если ячейка содержит допустимую дату, вы добавляете 1000 лет. Простой способ сделать это — использовать функцию ДАТАМЕС(А4;12000). Эта функция возвращает дату, которая на 12 000 месяцев (1000 лет) отстоит от начальной даты, указанной в первом аргументе функции ДАТАМЕС. Обратите внимание, что эта функция появилась в Excel 2007. В более ранних версиях эта функция доступна после установки надстройки Пакет анализа. Вместо этого можете использовать формулу: =ДАТА(ГОД(А4)+1000;МЕСЯЦ(А4);ДЕНЬ(А4)).
Если ячейка содержит недопустимую дату (до 1900 г.), вам нужно разбить дату в текстовом виде на элементы, добавить 1000 лет к годам, после чего обратно склеить элементы:
- Получите левую часть даты (дни, месяцы и разделитель) =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)
- Получите год =ПРАВСИМВ(A4;4)
- Добавьте 1000 лет к году =ПРАВСИМВ(A4;4)+1000
- Сцепите дни, месяцы и год =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000
- Преобразуйте текст, полученный на предыдущем шаге в дату =ДАТАЗНАЧ(ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000)
Теперь необходимо избирательно использовать, либо фрагмент с ДАТАМЕС, либо фрагмент с ДАТАЗНАЧ, в зависимости от того, как Excel воспринимает содержимое ячейки: как дату, или как текст. Если кратко, то формула в С4: =ЕСЛИ(ЕТЕКСТ(А4);ДАТАЗНАЧ();ДАТАМЕС()). Полностью формула: =ЕСЛИ(ЕТЕКСТ(A4);ДАТАЗНАЧ(ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000); ДАТАМЕС(A4;12000)). Скопируйте ее в ячейку D4, чтобы получить измененную дату для столбца В. Теперь вы можете использовать эти результаты с любой функцией даты. Например, в Е4 с помощью формулы =D4-C4 вы просто определите разность между конечной и начальной датой в днях. А в F4 с помощью формулы =РАЗНДАТ(C4;D4;”y”) разность D4-C4 в полных годах. В ячейке G4 формулы из С4, D4 и Е4 объединены в одну мегаформулу: =ЕСЛИ(ЕТЕКСТ(B4);ДАТАЗНАЧ(ЛЕВСИМВ(B4;ДЛСТР(B4)-4)&ПРАВСИМВ(B4;4)+1000);ДАТАМЕС(B4;12000))-ЕСЛИ(ЕТЕКСТ(A4);ДАТАЗНАЧ(ЛЕВСИМВ (A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000);ДАТАМЕС(A4;12000))
Не забудьте, что в Англии и колониях переход с юлианского на григорианский календарь привел к тому, что вслед за 2 сентября 1752 года наступило сразу 14 сентября. В России вслед за 31 января 1918 года наступило 14 февраля (подробнее см. Григорианский календарь). Предложенная формула не обрабатывает эти аномалии.
Резюме: несмотря на то, что Excel не умеет напрямую работать с датами до 1900 года, вы можете добавить ко всем датам некое фиксированное число лет (например, 1000), и сделать доступным обработку формулами дат.
Источник. Эта формула была предложена Барри Гудини.
