Основные принципы работы с датами и временем в Excel
Видео
Как обычно, кому надо быстро - смотрим видео. Подробности и нюансы - в тексте ниже:
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:
|
"Классическая" форма |
3.10.2006 |
|
Сокращенная форма |
3.10.06 |
|
С использованием дефисов |
3-10-6 |
|
С использованием дроби |
3/10/6 |
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню - правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):
Время вводится в ячейки с использованием двоеточия. Например
16:45
По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:
16:45:30
И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть
27.10.2012 16:45
Быстрый ввод дат и времени
Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).
Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать - как именно копировать выделенную дату:
Если Вам часто приходится вводить различные даты в ячейки листа, то гораздо удобнее это делать с помощью всплывающего календаря:
Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата - лучше воспользоваться функцией СЕГОДНЯ (TODAY):
Как Excel на самом деле хранит и обрабатывает даты и время
Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек - вкладка Число - Общий), то можно увидеть интересную картинку:
То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417
На самом деле любую дату Excel хранит и обрабатывает именно так - как число с целой и дробной частью. Целая часть числа (41209) - это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)
Из всех этих фактов следуют два чисто практических вывода:
- Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! ;)
- Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они - числа! А вот это уже раскрывает перед пользователем массу возможностей.
Количество дней между двумя датами
Считается простым вычитанием - из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:
Количество рабочих дней между двумя датами
Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):
Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис - Надстройки (Tools - Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).
Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.
Про то, как это правильно вычислять, лучше почитать тут.
Сдвиг даты на заданное количество дней
Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.
Сдвиг даты на заданное количество рабочих дней
Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.
Вычисление дня недели
Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.
Первый аргумент этой функции - ячейка с датой, второй - тип отсчета дней недели (самый удобный - 2).
Вычисление временных интервалов
Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические операции, как и с датой - сложение, вычитание и т.д.
Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:
Ссылки по теме
- Как вычислять возраст (стаж) в полных годах-месяцах-днях
- Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.
- Автоматическое добавление текущей даты в ячейку при вводе данных.
- Как вычислить дату второго воскресенья февраля 2007 года и т.п.
очень было бы удобно на работе!
Не оно?
Есть таблица с датами в формате: 4 Февраля 1985. Как привести ячейки к виду: 04.02.1985. Изменение формата ячейки не помогает.
1. ввести функцию сслыки на список праздников
2. сделать эту функцию доступной для любого файла Excel?
2. Можно добавить данные по праздникам в файл Personal, но тогда эти формулы не будут работать у других пользователей. Проще вставлять (копировать) отдельный лист с праздниками.
Просто большое человеческое Спасибо!!!
Есть такая задача: имею список событий, которые отсортированы по датам. Хочу сделать так чтобы ячейка с датой (либо вся строка) автоматически меняла цвет по мере приближения к заданной дате. Например за месяц ячейка зелёная, за 2 недели желтая, за неделю красная, прошло событие - цвет ещё какой-нибудь...
Как это сделать? Не смог найти информации по такому вопросу...
выделяете ячейки с датами и открываете Главная - Условное форматирование - Правила выделения ячеек - Дата. В более сложном - там же Создать правило - Использовать формулу и ввести формулу проверки даты, например =(А1-СЕГОДНЯ())<7 , где А1 - ячейка с датой, которая проверяется и за неделю до срока начинает краснеть, например.
=(A1-ТДАТА())>=0,25
где А1 - ячейка с временем последней активности водителя
ТДАТА - функция, выдающая текущую дату-время
0,25 - длительность 6 часов в терминах Excel (1/4 часть суток)
доброго времени суток. Я сейчас изучаю excel интересным способом: у меня есть товарищ, гуру эксель. =) он даёт задачи, а я их решаю. В решениии помогают Google и всякие книжки. но есть вещи которых я просто не могу там найти. А каждый раз обращаться по "тупому" вопросу с товарищу тоже неудобно.
Сейчас я "встал" на такой вот задаче.
Есть 3 ячейки. 1 ячейка начальная дата (любая), 2 ячейка конечная дата (тоже любая). В 3 ячейке надо дать сумму рабочих дней между этими 2 датами. Однако выходные дни СРЕДА и СУББОТА или только СРЕДА.
Я так понимаю, что это надо сделать через формулу массивов и функции счётесли. Однако просто не понимаю, как можно из двух дат создать массив (двссыл тут не работает).
очень прошу помочь.
Чуть не забыл, для расчётов надо использовать только 1 ячейку.
Заранее спасибо.
Мы легких путей не ищем. =)
Буду искать возможности расчёта через массив. =)
=SUM(IF(WEEKDAY(ROW(INDIRECT(A3&":"&B3));2)<>3;1;0))
всеравно спасибо
набор дат: ян 2011, фев 2011, март 2011, апр 2011, май 2011...
набор цифр: 1,1,1,1,2,2,3,3.3,.....(количество сотрудников)
Нам надо определить, в каком месяце добавлялся новый сотрудник.
Заранее огромное спасибо! Вы меня спасете от очередной бессонной ночи))
Спасибо за отличный ресурс!
Задача: рассчет длительности периода
Проблема: Региональные настройки у всей компании Английский США. Формат времени для рассчета взят из 1С (15.08.2013 08:00).
При изменении региональных настроек на Русский (Россия) рассчет производится, но открыв данный файл в стандартных для компании настройках (Английский США). В колонке длительность периода только "VALUES".
Необходимо рассчитать длительность периода в английском формате. Как это можно сделать?
Спасибо за совет.
У меня такой вопрос: в ячейке указана дата в таком виде 13.08.2013, формат ячейки–date. Я хочу поменять формат так, чтобы был сначала месяц, потом число, а потом год. (08/13/2013). Правой кнопкой мыши нажимаю формат ячеек, выбираю формат, но он не меняется так как мне нужно (точнее он вообще никак не меняется). В настройках компьютера такой формат (8/13/2013). Помогите пожалуйста!!!!
Нужно вычислить кол-во часов между двумя ячейками, формат время.
Если в 2х ячейках время АМ
А1=ВРЕМЯ(2;15;0)
В1=ВРЕМЯ(8;0;0)
то =В1-А1 вычисляет нормально = 5,45 часов
но если в одной яч. время РМ после полудня, а в др. АМ до полудня
А1=ВРЕМЯ(23;0;0)
В1=ВРЕМЯ(7;0;0)
то =В1-А1 = ################ - отрицательные или слишком большие дата и время
почему так происходит и как исправить формулу, чтобы получить часы?
Т.е. я понимаю, что надо сделать сводную таблицу и название столбцов сделать "Дата". А вот как сгруппировать по месяцам и годам, так чтобы именно столбцы назывались не конкретное число, а именно январь, февраль или если по годам, то 2012,2013 и т.д.
Спасибо за ответ.
с датами разобрался а вот с часами минутами секундами и долями секунд ну никак?
0:00:38:2 минус 0:01:06:8
равно?
Правильно будет 0:00:38,2
=ЕСЛИ(ДЕНЬНЕД(A1+14;2)<=5;A1+14;A1+14-(ДЕНЬНЕД(A1+14;2)-5))
где А1 - ячейка с начальной датой.
=ЕСЛИ(ДЕНЬНЕД(A1+A2-1;2)<=5;A1+A2-1;ЕСЛИ(ДЕНЬНЕД(A1+A2-1;2)=6;A1+A2-2;A1+A2-3))
Спасибо
Спасибо!
Спасибо!
И хотелось бы узнать можно ли создать календарь с ссылками в датах. Скажем нажали на дату в прошлом а он открыл таблицу с отчетом в экзель или с других источников(таблиц, книг и листов) со всей информацией о прошлой проделанной работе? Или же нажав на текущею дату открыть таблицу для работ на сегоднящний день и сохронять этот файл в ячкйке календаря с этой датой. чтоб книга с таблицами выглядела как каленарь в начале работ, а после окончания сворачивалась снова в календарь с сохранением таблицы? Своего рода архив для востановления сценариев расхода и прихода, контроля и учета передвижения товаров и материальных сценностей. Зарание спасибо!
Николай, при анализе детализации вызовов после копирования таблицы в эксель , длительность или сумма - например 15.4 и 8.32, отображаются как 15.апр и авг.32 соответственно. Кроме как ручной правки (а это очень утомительно:() другого способа преобразования не вижу, ведь и в форматировании значения выглядят как числа даты 41714 и 11902. К сожалению провайдер не даёт детализацию в формате .xlsx, подскажите пожалуйста , как быть в этом случае?
=ЗНАЧЕН(ДЕНЬ(A1)&","&МЕСЯЦ(A1))
first of all, thank you very much for your work! It helps A LOT!
one question regarding time format:if time es exported as text like the following
Thank you in advance!
regards,
Условие: есть дата оплаты план (П.Дата) и есть дата платы Факк (Ф.Дата), есть сумма на оплату к примеру 10 000(Сумма) и коэф пени например 0,01%(Пени)
Примерно вычисление должно выглядеть след образом : (П.Дата - Ф.Дата ) * Сумма * Пени , т.е.
26 авг - 30 авг= -4 ( Дни просрочки)
4* 10 000*0,01%
Все бы хорошо, но есть одно НО. если формулу протягивать на н-ое количество листов , то она считает все подряд и просроченнные и не просроченные платежи. Т.к. разница дат выходит как с минусом - это и есть просрочка, так и с плюсом - эти платежи еще в перспективе.
Пытался прикрутить условие "Если" , т.е. если дни в разнице дат меньше нуля, то показывается значение с минусом, что собственно и должно идти в расчет, а если дни в разнице дат больше нуля то должен отображаться ноль и все перемножения коэфицентов будут равны нулю, что тоже есть правильно. но при вставке данной формулы выходит ошибка , мол разные еденицы измерения в расчетах. и в графе где по условию должны отображаться дни со значением минус -отображается значение ячейки в которой все это прописано
Всем затык, понять не могу, по логике вроде все верно, а по факту не получается, может у кого есть решение данного вопроса или аналогичные приемы для решения?
Подскажите как сделать так, чтоб указывало сколько в месяце дней, в отдельной ячейке?
Например, в одной ячейке указан месяц (Ноябрь 2014)
Имеем длинную таблицу с данными, по которым необходимо строить сводную диаграмму. В первом столбце, в каждой ячейке указано :дата и время в формате например, 27.10.2014 23:59:24
Таблица имеет много строк, и если например сводную диаграмму строить за неделю, в фильтре придется клацать множество раз "птички", так как это самое время за сутки повторяется с интервалом 3 минуты, например,
27.10.2014 12:59:24
27.10.2014 13:03:24
27.10.2014 13:06:24
Вопрос: как добавить рядом столбец, где будут исключительно даты, (по данным первого столбца)по которым можно будет фильтровать периоды при построении сводной диаграммы.
Как вариант
=ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2))
может быть вопрос элементарный но кто-нибудь может подсказать формулу для извлечения наименования месяца. например
27.11.20014 - Ноябрь
без утомительных "Если"
Спасибо
Если вам нужен не номер месяца (11), а именно текстовое его название (ноябрь), то формула будет чуть сложнее:
=ИНДЕКС({"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};МЕСЯЦ(A1))
В Вашей книге "Microsoft Excel Готовые решения бери и пользуйся" Вы также предлагаете для текстовых названий месяцев и дней недели использовать функцию ВЫБОР с перечислением всех месяцев/дней недели. Но ведь в Excel для этих целей существует формат данных, - для вывода названия месяцев нужно либо в формате ячейки выбрать нужный формат даты, либо там же в разделе (все форматы) подобрать или создать необходимый формат. Например, формат МММ выведет сокращенное наименование месяца, а ММММ - полное (янв или Январь). Для дней недели соответственно - ДДД и ДДДД. Если же нужно именно формулой извлечь название месяца/дня недели, можно использовать функцию ТЕКСТ, например
выведет: Пн понедельник ноя Ноябрь
(разумеется, если в ячейке A1 будет дата, соответствующая этому месяцу и дню недели)