Вопросы по датам Excel 2007
| Автор: | Индык Игорь Викторович |
| e-mail: | exelentc@yandex.ru |
Мне прислали огромную таблицу, в которой есть столбец с датами. Но даты введены как числа без разделителей! Например, вместо 20.09.1981 введено 200981. Как мне конвертировать их в даты?
К сожалению, стандартные функции Excel не позволяют конвертировать любое шестизначное число в дату с такими же значениями дня, месяца и года. Но существует способ сделать это самостоятельно с использованием текстовых формул.
Допустим, у нас есть таблица, которую экспортировали из сторонней базы данных. Даты в этой таблице приведены так же, как указано в вопросе.
Первой у нас указана дата 221103, что означает "22 января 2003 года". Чтобы получить дату 22.11.2003, мы обычно вводим в ячейку 22/11/03, и устанавливаем формат ячейки "Дата". Сейчас мы сделаем то же самое, но с помощью функции ЗНАЧЕН. Эта функция преобразовывает текстовое значение в числовое.
Кроме того, мы используем функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР. Все эти функции извлекают из ячейки с текстом указанное в аргументе количество символов, начиная с заданной в аргументе позиции. Например,
=ЛЕВСИМВ(B2;2)
Извлекает из ячейки с значением 221103 два символа слева (то есть 22). Функция ПРАВСИМВ делает то же, но отсчитывает справа, а функция ПСТР - слева, но извлекает нужное количество символов, предварительно отсчитав указанную ей позицию. Поэтому с помощью ПСТР можно извлекать символы из середины текста.
С помощью, этих трёх функций мы извлечём отдельные части даты (22, 11, 03), с помощью операнда & и знака / склеим их вместе, а с помощью функции ЗНАЧЕН преобразуем получившееся выражении в число:
=ЗНАЧЕН(ЛЕВСИМВ(B2;2)&"/"&ПСТР(B2;3;2)&"/"&ПРАВСИМВ(B2;2))
Мы специально использовали функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР одновременно, чтобы продемонстрировать их работу. Но возможно использовать только функцию ПСТР, правильно задав позиции символов:
Этот метод также замечательно подходит для ситуации, когда выгруженные даты находятся в формате MM/ДД/ГГ или ГГ/ММ/ДД - достаточно правильно указать в нашей формуле позиции извлекаемых сегментов текста или расположить функции ЛЕВСИМВ, ПРАВСИМВ и ПСТРв нужном нам порядке.
Я делаю расчеты для диплома, и мне нужно вывести отрицательные значения дат и времени. Как это сделать?
Excel работает только с положительными значениями дат и времени, иначе пользователь получает ошибку ##### (см. ошибки Excel). Но поскольку обязательно найдутся желающие преодолеть ограничения, мы можем посоветовать следующее способы.
Способ 1: Вывести результат вычислений как текст, используя функцию ТЕКСТ.
Например, мы вычисляем разницу между двумя датами в ячейках А1 и А2
Если просто отнять от меньшей даты большую, мы получим число -29848. С помощью функции ТЕКСТ, мы можем преобразовать число 29848 в дату со знаком минус:
=ТЕКСТ(A1-A2;"-ДД.ММ.ГГГГ")
То есть мы сначала нашли положительную разницу между датами (29848), а потом преобразовали её в текст с форматом даты и знаком минус.
Если нужно вывести отрицательное значение времени, необходимо использовать формат времени:
=ТЕКСТ(A1-A2;"-Ч:ММ")
Полный список форматов можно найти во встроенной помощи по функции ТЕКСТ.
Обратите внимание, если разница двух чисел будет изначально отрицательной, функция ТЕКСТ возвратит ошибку. Для решения этой проблемы можно использовать конструкцию:
=ТЕКСТ(МАКС(А1:А2)-МИН(А1:А2);"-ДД.ММ.ГГГГ Ч:ММ")
То есть мы всегда сначала вычисляем позитивную разницу, а потом форматируем негативную дату/время.
Не забывайте, что полученный результат является текстом, то есть его невозможно использовать в дальнейших вычислениях!
Способ 2: Использование пользовательского формата.
В этом случае мы создаем пользовательский формат даты/времени со знаком минус. Дл создания формата нужно вызвать окно Формат ячеек любым удобным Вам способом. В списке Числовые форматы необходимо выбрать пункт (все форматы). После этого в списке Тип мы выбираем числовой формат ДД.ММ.ГГГГ ч:мм, который мы будем настраивать.
Поскольку этот формат является встроенным, после настройки Excel создаст копию этого числового формата. Исходный формат в списке Тип невозможно изменить или удалить.
После выбора из списка нужного формата, он появится в поле Тип. Исправляем этот формат на следующий: -ДД.ММ.ГГГГ ч:мм , и нажимаем Ок.
Осталось только отформатировать ячейки с нужными датами/временем с применением нового формата.
Помните, что исходное число должно быть положительным, иначе Excel возвратит ошибку!
Я готовлю зарплатные ведомости и мне нужно постоянно вводить в таблицу последнее число месяца и рассчитывать, какой это будет день недели. Это можно как-то автоматизировать?
Для такой задачи используется функция КОНМЕСЯЦА:
=КОНМЕСЯЦА(нач_дата;число_месяцев) , где
- нач_дата - это дата, от которой будет отсчитываться конец месяца,
- число_месяцев - порядковый номер месяца, в котором будет рассчитана последняя дата, отсчитанный от начальной даты.
То есть, если у нас исходная дата составляет 01.08.2011, то формула:
=КОНМЕСЯЦА(01.08.2011;0)
вернёт нам 31 августа,
а формула
=КОНМЕСЯЦА(01.08.2011;1)
- 30 сентября.
Соответственно, чтобы вывести в столбец список всех последних дат всех месяцев, достаточно задать в ячейке А1 дату 01.01.2011, а в столбце результатов:
=КОНМЕСЯЦА(А1;0) =КОНМЕСЯЦА(А1;1) =КОНМЕСЯЦА(А1;2) … =КОНМЕСЯЦА(А1;12)
Чтобы узнать, какой это будет день недели, нужно использовать функцию ДЕНЬ:
=ДЕНЬ(КОНМЕСЯЦА(A1;0))
А как определить, является ли дата выходным днем?
Для этого можно использовать функцию ДЕНЬНЕД:
=ДЕНЬНЕД(A1;2)>5
То есть мы рассчитываем порядковый номер дня недели, который соответствует заданной дате, и если этот день имеет номер 6 или 7 (суббота или воскресенье), то наша формула возвратит нам значение ИСТИНА, что означает, что наша дата - выходной.
