Возвращение текущей даты в эксель. Вставка текущих даты и времени в ячейку.

Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

Аргументы:

• начальная дата;
• конечная дата;
• код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

• для отображения результата в днях – «d»;
• в месяцах – «m»;
• в годах – «y»;
• в месяцах без учета лет – «ym»;
• в днях без учета месяцев и лет – «md»;
• в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

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

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

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

• ДАТА (DATE) - возвращает заданную дату в числовом формате Microsoft Excel:
  =ДАТА(2010; 6; 20)
  =DATE(2010, 6, 20) Год (year) (2010) - число, обозначающее год. Должно быть в интервале от 1900 до 2078.
  Месяц (month) (6) - число, обозначающее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Так, ДАТА(2010;14;4) вернет дату 4 марта 2011 года.
  День (day) (20) - число, определяющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число будет прибавлено к первому дню указанного месяца. Например, ДАТА(2010;10;44) вернет дату 13 ноября 2010 года.
  Аргументы месяц и день так же могут быть указаны и отрицательными числами.
• ДАТАЗНАЧ (DATEVALUE) - Преобразует дату из текстового формата в числовой(понятный для Excel):
  =ДАТАЗНАЧ("20.6.2009")
  =ДАТАЗНАЧ("20-ИЮН-09")
  =DATEVALUE("6/20/2009") обе функции вернут 39984 . Чтобы лучше понимать назначение данной функции советую ознакомиться со статьей:
  Если в указанной текстом дате не указан год("20.6"), то ДАТАЗНАЧ применит текущий год на основании системной даты ПК. Любая информация о времени(часы, минуты, секунды) этой функцией игнорируются. Чтобы преобразовать время необходимо воспользоваться функцией ВРЕМЗНАЧ (TIMEVALUE)
• ДНЕЙ360 (DAYS360) - Вычисляет количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Может использоваться для расчета платежей, если бухгалтерия основана на двенадцати 30-дневных месяцах:
  =ДНЕЙ360(нач_дата; кон_дата; метод) Нач_дата и кон_дата - даты, количество дней между которыми требуется узнать.
  Метод - логическое значение для указания метода расчета при вычислениях. Если не указан, указан ЛОЖЬ (FALSE) или 0, то применяется Американский метод(NASD). Он основан на том, что если начальная дата является 31-м числом месяца, то она считается равной 30-ому числу того же месяца. Если конечная дата является 31-м числом месяца и начальная дата меньше, чем 30-ое число, то конечная дата считается равной 1-ому числу следующего месяца, в противном случае конечная дата считается равной 30-ому числу того же месяца. Если указан ИСТИНА (TRUE) или 1, то применяется Европейский метод. В этом случае начальная и конечная даты, которые приходятся на 31-ое число месяца, считаются равными 30-ому числу того же месяца.
  =ДНЕЙ360("30.11.13"; "7.12.13")
  =ДНЕЙ360("30.11.13"; "7.12.13"; 1)
  =ДНЕЙ360(A1 ; A2) все формулы вернут 7(при условии, что в A1 записана дата "30.11.13" , а в A2 - "07.12.13").
• ДАТАМЕС (EDATE) - Находит дату, отстоящую на заданное число месяцев вперед или назад от указанной даты:
  =ДАТАМЕС(нач_дата; число_месяцев)
  =ДАТАМЕС("15.01.16"; 1)
  =ДАТАМЕС("15.01.16"; -1)
  =EDATE("01/15/16", 1) нач_дата ("15.01.16") - дата или ссылка на ячейку с датой, от которой необходимо вычислить новую дату
  число_месяцев (1) - целое число, определяющее количество месяцев до или после даты, указанной аргументом нач_дата . Если указано положительное число, то к указанной дате будет прибавлено указанное количество месяцев; если отрицательное - то из указанной даты будет вычтено указанное количество месяцев. Если указано не целое число, то от него отбрасывается дробная часть без применения округления(1,7 будет преобразовано в 1)
  Замечание: Сервис -Надстройки
• КОНМЕСЯЦА (EOMONTH) - Определяет дату для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
  =КОНМЕСЯЦА("01.01.2016"; 0)
  =EOMONTH("01.01.2016", 0) нач_дата ("01.01.2016") - ссылка на ячейку с датой либо непосредственно дата в числовом или текстовом формате.
  число_месяцев (0) - число месяцев до или после даты, указанной аргументом нач_дата . Если указан 0, то будет возвращена дата последнего дня для месяца даты, указанной аргументом нач_дата . Любое положительное число прибавляет указанное кол-во месяцев от даты в нач_дата , а отрицательное - отнимает.
  Замечание: для версий Excel ниже 2007 функция может быть недоступна. Если функции нет в списке, то следует установить надстройку Пакет Анализа(если она не установлена), а затем подключить его с помощью команды Сервис -Надстройки
• ЧАС (HOUR) - возвращает количество часов для заданного значения времени:
  =ЧАС("15:30:30")
  =ЧАС("10.04.2016 15:30:30")
  =ЧАС(0,646180555555556)
  =ЧАС(6,646180555555556)
  =ЧАС("3:30:30 PM")
  =HOUR("15:30:30") все вышеприведенные формулы вернут число 15. В качестве аргумента можно задавать как время в текстовом формате("15:30:30"), так и его эквивалент в виде дробной части(0,646180555555556). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату("10.04.2016 15:30:30"), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646180555555556 и 6,646180555555556 вернут одинаковое значение) Чтобы лучше понимать работу данной функции советую ознакомиться со статьей:
• МИНУТЫ (MINUTE) - возвращает количество минут для заданного значения времени:
  =МИНУТЫ("15:30:30")
  =МИНУТЫ("10.04.2016 15:30:30")
  =МИНУТЫ(0,646180555555556)
  =МИНУТЫ(6,646180555555556)
  =МИНУТЫ("3:30:30 PM")
  =MINUTE("15:30:30") все вышеприведенные формулы вернут число 30. В качестве аргумента можно задавать как время в текстовом формате("15:30:30"), так и его эквивалент в виде дробной части(0,646180555555556). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату("10.04.2016 15:30:30"), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646180555555556 и 6,646180555555556 вернут одинаковое значение) Чтобы лучше понимать работу данной функции советую ознакомиться со статьей:
• СЕКУНДЫ (SECOND) - возвращает количество секунд для заданного значения времени:
  =МИНУТЫ("15:30:45")
  =СЕКУНДЫ("10.04.2016 15:30:45")
  =СЕКУНДЫ(0,646354166666667)
  =СЕКУНДЫ(6,646354166666667)
  =СЕКУНДЫ("3:30:45 PM")
  =SECOND("15:30:45") все вышеприведенные формулы вернут число 45. В качестве аргумента можно задавать как время в текстовом формате("15:30:45"), так и его эквивалент в виде дробной части(0,646354166666667). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату("10.04.2016 15:30:45"), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646354166666667 и 6,646354166666667 вернут одинаковое значение) Чтобы лучше понимать работу данной функции советую ознакомиться со статьей:
• ДЕНЬ (DAY) - возвращает число, означающее номер дня для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =ДЕНЬ("20.4.2009")
  =ДЕНЬ("20-ИЮН-09")
  =ДЕНЬ(A1)
  =DAY("6/20/2009") все указанные функции вернут число 20, т.е. день месяца (при условии, что в А1 записана дата 20.06.2009). =ДЕНЬ(СЕГОДНЯ()) вернет номер дня месяца для текущей даты(на основании системной даты).
• МЕСЯЦ (MONTH) - возвращает число, означающее номер месяца для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =МЕСЯЦ("20.6.2009")
  =МЕСЯЦ("20-ИЮН-09")
  =МЕСЯЦ(A1)
  =MONTH("6/20/2009") все указанные функции вернут число 6, т.е. номер месяца для указанной даты(в А1 записана дата 20.06.2009). =МЕСЯЦ(СЕГОДНЯ()) вернет номер месяца для текущей даты(на основании системной даты).
• ГОД (YEAR) - возвращает число, означающее год для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =ГОД("20.6.2009")
  =ГОД("20-ИЮН-09")
  =ГОД(A1)
  =YEAR("6/20/2009") все указанные функции вернут 2009, т.е. год для указанной даты(в А1 записана дата 20.06.2009). =ГОД(СЕГОДНЯ()) вернет год для текущей даты(на основании системной даты).
• ЧИСТРАБДНИ (NETWORKDAYS) - возвращает количество рабочих дней между указанными датами. Рабочими днями считаются все дни за исключением выходных и праздничных дней. Праздничные дни указываются отдельно:
  =ЧИСТРАБДНИ(ДАТАЗНАЧ("1.6.2015"); ДАТАЗНАЧ("1.4.16"); F1:F30)
  =ЧИСТРАБДНИ("1.6.2015"; "1.4.16"; F1:F30)
  =ЧИСТРАБДНИ(A1 ; B1 ; F1:F30)
  =ЧИСТРАБДНИ(A1 ; ДАТАЗНАЧ ("01.04.16"); F1:F30)
  =NETWORKDAYS("1.6.2015", "1.4.16", F1:F30) Нач_дата ("1.6.2015") - начальная дата для вычисления рабочих дней. Если начальная дата меньше конечной даты, то функция вернет отрицательное значение.
  Кон_дата ("1.4.16") - конечная дата для вычисления рабочих дней.
  Праздники
• ТДАТА (NOW) - возвращает текущую дату и время. Не имеет аргументов:
  =ТДАТА()
  =NOW() функция не обновляется ежесекундно и будет вычислена только в случае, если на листе с функцией были произведены какие-либо изменения(вручную или макросами).
• ВРЕМЯ (TIME) - возвращает заданное в 24-часовом формате время в числовом формате Microsoft Excel:
  =ВРЕМЯ(15; 30; 45)
  =TIME(15, 30, 45) часы (hour) (15) - число, обозначающее час. Должно быть в интервале от 0 до 23.
  минуты (minute) (30) - число, обозначающее минуты часа. Если минуты больше 59, то излишек от 59 прибавляется к указанному часу. Так, ВРЕМЯ(15;78;45) вернет время 16:18:45 .
  секунды (second) (45) - число, обозначающее секунды от минуты. Если секунды больше 59, то излишек будет прибавлен к минутам. Например, ВРЕМЯ(15;18;65) вернет время 15:19:05 .
  Аргументы минуты и секунды так же могут быть указаны и отрицательными числами. В этом случае указанное количество минут будет вычтено из часа, а секунды - из минут: =ВРЕМЯ(15;18;-65) будет равно 15:16:55
• ВРЕМЗНАЧ (TIMEVALUE) - Преобразует время из текстового формата в числовой(понятный для Excel):
  =ВРЕМЗНАЧ("15:30")
  =ВРЕМЗНАЧ("15:30:45")
  =TIMEVALUE("15:30:45") первая функция вернет значение 0,645833333333333 , а вторая - 0,646354166666667 . Чтобы лучше понимать назначение данной функции и её результаты советую ознакомиться со статьей:
  Если в указанном текстом времени не указаны секунды, то ВРЕМЗНАЧ приравняет их к нулевым("15:00" = "15:00:00"). Если не указаны минуты, то функция вернет значение ошибки #ЗНАЧ! (#VALUE!) . Любая информация о дате этой функцией игнорируются. Чтобы преобразовать дату необходимо воспользоваться функцией ДАТАЗНАЧ (DATEVALUE)
• СЕГОДНЯ (TODAY) - возвращает текущую дату и не имеет аргументов:
  =СЕГОДНЯ()
  =TODAY() функция не обновляется ежесекундно и будет вычислена только в случае, если на листе с функцией были произведены какие-либо изменения(вручную или макросами).
• ДЕНЬНЕД (WEEKDAY) - возвращает день недели для указанной даты. День недели возвращается как целое число в от 1 до 7. При этом вторым аргументом указывается тип определения начала недели - с Воскресенья или с Понедельника:
  =ДЕНЬНЕД("02.04.2015")
  =ДЕНЬНЕД("2.4.2015";2)
  =ДЕНЬНЕД(39905;1)
  =ДЕНЬНЕД(A1;2)
  =WEEKDAY("4/2/2015") дата_в_числовом_формате ("02.04.2015") - дата, записанная как текст("02.04.2015" или "02 Апр 2015") или как число, определяющее дату(39905). Текст автоматически преобразуется в дату в числовом формате. Чтобы лучше понимать принципы задания аргументов для данной функции советую ознакомиться со статьей:
  Тип - целое число, определяющее тип отсчета начала недели:
  1 - неделя начинается с Воскресенья. Это значит, что при задании даты "02.4.2015" будет возвращено число 5, хотя день недели Четверг.
  2 - неделя начинается с Понедельника. Значит при задании даты "02.4.2015" будет возвращено число 4, что наиболее подходящее для России и Европейской части населения.
  В версиях, новее 2003 так же есть и другие варианты, отвечающие за первый день недели(12,13 и т.д.), но в силу их отсутствия их практического применения они не рассматриваются.
• НОМНЕДЕЛИ (WEEKNUM) - возвращает номер рабочей недели года для указанной даты:
  =НОМНЕДЕЛИ("02.4.2015")
  =НОМНЕДЕЛИ("02.4.2015";1)
  =НОМНЕДЕЛИ("02.4.2015";1)
  =НОМНЕДЕЛИ("02 апр 2015";1)
  =НОМНЕДЕЛИ(A1;2)
  =WEEKNUM(A1,1) пор_номер_даты ("02.4.2015") - дата, записанная как текст("02.04.2015" или "02 Апр 2015") или как число, определяющее дату(39905). Текст автоматически преобразуется в дату в числовом формате. Так же может быть ссылкой на ячейку с датой. Чтобы лучше понимать принципы задания аргументов для данной функции советую ознакомиться со статьей:
  тип_возв(2) - необязательный аргумент, по умолчанию принимающий значение 1. Число, которое определяет, с какого дня начинается неделя. Определение первой недели на основании Системы 1 : 1 или не указан - Воскресенье, 2 или 11 - Понедельник, с 12 до 17 - Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье. Определение первой недели на основании Системы 2 : 21 - первым днем недели считается Понедельник.
  Система 1 - Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
  Система 2 - Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1.
• РАБДЕНЬ (WORKDAY) - возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от указанной даты. Рабочими днями не считаются выходные дни и дни, определенные как праздничные аргументом праздники . Как правило РАБДЕНЬ используется, чтобы исключить выходные дни или праздники при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней:
  =РАБДЕНЬ(ДАТАЗНАЧ("03.01.16"); 5)
  =РАБДЕНЬ(ДАТАЗНАЧ("03.01.16"); 5; {42376; 42377})
  =РАБДЕНЬ("03.01.16"; 5; {"7.1.2016";"8.1.2016"})
  =РАБДЕНЬ("03.01.16"; 5; F1:F30)
  =РАБДЕНЬ("1/3/16"; 5; F1:F30) Нач_дата ("1.6.2015") - начальная дата для вычисления рабочих дней. Если нач_дата является недопустимой датой, то функция вернет значение ошибки #ЧИСЛО! (#NUM!) .
  число_дней (5) - количество рабочих и не праздничных дней до или после нач_дата . Положительное число будет означать нахождение будущей даты от указанной аргументом нач_дата ; Положительное число - прошедшей даты от указанной аргументом нач_дата .
  Праздники (F1:F30) - необязательный аргумент, представляющий одну или нескольких дат в числовом формате, которые должны быть исключены из подсчета рабочих дней. Обычно это государственные праздники(новогодние, майские и т.п.). Как правило для этого аргумента отводится отдельный диапазон ячеек(F1:F30), в котором перечисляются праздничные даты. Но так же может быть представлен статичным массивом дат: {"7.1.2016";"8.1.2016"}
• ДОЛЯГОДА (YEARFRAC) - Возвращает долю года, которую составляет количество дней между двумя указанными датами (начальной и конечной). Предназначена для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период:
  =ДОЛЯГОДА("01.01.16";"30.06.16";0)
  =ДОЛЯГОДА("01.01.16";"01.07.16";3)
  =ДОЛЯГОДА(A1 ; B1 ;4)
  =YEARFRAC(A1 , B1) нач_дата - дата в текстовом формате, непосредственно дата или ссылка на ячейку с начальной датой.
  кон_дата - дата в текстовом формате, непосредственно дата или ссылка на ячейку с конечной датой.
  Базис - тип используемого способа вычисления дня:
  • 0 или не указан - US (NASD) 30/360
  • 1 - Фактический/фактический
  • 2 - Фактический/360
  • 3 - Фактический/365
  • 4 - Европейский 30/360

  Замечание: для версий Excel ниже 2007 функция может быть недоступна. Если функции нет в списке, то следует установить надстройку Пакет Анализа(если она не установлена), а затем подключить его с помощью команды Сервис -Надстройки

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

Для добавления данных используют дополнительный функционал

Для того чтобы решить поставленную задачу, необходимо воспользоваться одной из двух функций программы: «Текущая дата» либо «Сегодня». Обратите внимание, что приведённые инструменты не подходят для случаев, когда число должно быть фиксированным, то есть не меняться каждый новый день.

Первым делом определите ячейку, с которой будете работать. Далее, перейдите на вкладку «Формулы». В блоке «Библиотека функций» нажмите «Дата и время». Затем выберите «Сегодня» и кликните по кнопке «ОК». После этого выбранная вами область будет содержать текущее значение, которое будет изменяться каждый день. То же самое можно проделать при помощи комбинации клавиш Ctrl+Shift+4, в некоторых случаях Ctrl+Ж либо ввести «=СЕГОДНЯ()» в специальное поле для формул.

Для добавления времени в том же разделе ленты инструментов кликните по пункту «Время». Обратите внимание, что все значения будут выставлены в соответствии с информацией из календаря и часов вашего компьютера.

Установленные вами значения будут обновляться каждый раз при открытии книги, но, если необходимо

Эта статья посвящена вопросам работы с датами в Excel 2007. В ней мы постараемся найти решения для самых популярных проблем пользователей при работе с датами.

Как мне ввести дату? Я набираю дату, а Эксель показывает число!

Нужно помнить, что Excel хранит даты в виде чисел. То есть, если Вы вводите 19.08.2011, Excel сохраняет это значение как 40774. Это число означает количество дней, отсчитанных от 1 января 1900 года.

Если при вводе даты Excel отображает число, необходимо выделить нужную ячейку или их диапазон, вызвать окно Формат ячеек (например, через контекстное меню), и на вкладке Число установить формат Дата .

Я работаю продавцом, и каждый день отправляю начальству отчет по продажам. Поэтому каждый день меняю дату вручную. Можно ли сделать так, что бы дата менялась автоматически?

Конечно. Специально для таких ситуаций Excel предлагает формулу СЕГОДНЯ. В нужную ячейку необходимо внести:

СЕГОДНЯ()

И ежедневно отчет будет выводить текущую дату.

А мне нужно не сегодня, а за прошлый понедельник? Что мне делать?

Если сегодня у нас тоже понедельник, достаточно отнять от текущей даты 7 дней:

СЕГОДНЯ()-7

Для других дней недели нужно отнять количество дней между этими днями недели.

Мне нужно, чтобы формула могла посчитать период в календаре. Например, человек отсутствовал с 15.09.09г. по 31.12.09г., то есть 16 дней в сентябре, плюс ещё три месяца (весь октябрь, ноябрь, декабрь), или 108 дней в общей сумме. Как написать формулу?

Как мы уже отмечали, Excel хранит даты как количество дней, отсчитанных от одной точки. Поэтому, чтобы найти разницу между двумя датами, достаточно написать формулу арифметической разницы между ячейкой с поздней датой и ячейкой с более ранней. Например, в нашем случае ячейка А1 содержит дату 15.09.09, ячейка А2 - 31.12.09. Формула будет иметь вид:

А2-А1+1

Единицу нужно добавлять, поскольку 15.09.09 человек уже отсутствовал на работе.

В случае, если нужно вывести количество месяцев и дней (в нашем примере - 3 месяца 16 дней), можно воспользоваться формулами МЕСЯЦ и ДЕНЬ.

Формула МЕСЯЦ извлекает из введённой даты номер месяца с начала года, а формула ДЕНЬ - номер дня с начала месяца. Получив эти номера из наших дат, мы можем легко найти между ними разницу:

МЕСЯЦ(А2)-МЕСЯЦ(A1) - результатом будет 3 =ДЕНЬ(B5)-ДЕНЬ(A5) - результатом будет 16

Эти числа можно вывести в одну ячейку в виде "3 месяца 16 дней" с помощью операнда & :

="Период отсутствия составил "&A2&" месяца и "&B2&" дней"

или функции СЦЕПИТЬ:

СЦЕПИТЬ("Период отсутствия составил "; A2;" месяца и ";B2;" дней")

где А2 и В2 - ячейки, в которых находятся наши формулы, вычисляющие количество, соответственно, месяцев и дней.

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

Помогите перевести из чисел в года и месяцы. Например, у нас есть 6,2 и 6,8 - то есть 6 лет 2 месяца и 6 лет 8 месяцев. Нам нужно получить сумму не 13 , а 12 лет 10 месяцев.

В этом условии присутствует ошибка. Excel определяет числа, введённые после запятой, как десятичные дроби. А это значит, что число 6,2 означает не "6 лет 2 месяца", а "6 целых 2 десятых года", что приблизительно даёт 6 лет 2 месяца и 13 дней. Если нужный период времени невозможно ввести в формате даты, мы рекомендуем сохранять целые значения лет, месяцев и дней в разных ячейках - это поможет избежать ошибок в вычислениях, и очень упростит операции. Так, чтобы получить нужный нам результат в 12 лет и 10 месяцев, достаточно будет просуммировать соответствующие ячейки:

Ну а как вывести эти числа в составе нужной фразы, мы уже написали выше.

А что делать, если мы суммируем больше чем два периода? Тогда у нас получается глупость типа 37 лет и 45 месяцев!

Согласны. Тогда давайте вычислим, сколько целых лет составляют 45 месяцев, прибавим их к 37 годам, и рассчитаем остаток месяцев:

Сначала с помощью формулы ЦЕЛОЕ узнаем, сколько целых лет составляет 45 месяцев:

ЦЕЛОЕ(C11/12)

Ответ - 3 года.

Нам остается прибавить к общему количеству лет 3 года, и отнять от общего количества месяцев 3х12 = 36 месяцев.

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

B11+ЦЕЛОЕ(C11/12)

И для остатка месяцев:

C11-ЦЕЛОЕ(C11/12)*12

Результат: 40 лет и 9 месяцев

• закрепить на практике работу с мастером функций;
• научиться использовать функции Даты и время.

Программное обеспечение: Ms Office: Ms Excel 2007

Ход урока

1. Организационная часть

1.1. Проверить присутствующих на уроке;
1.2. Проверить готовность учащихся к уроку.

2. Вводный инструктаж

2.1. Проверка знаний пройденного материала (фронтальный опрос);

1. Что такое функция?
2. Сколько функций входит в Excel, и на какие категории распределяются?
3. Какие существуют способы вызова окна “Мастера функций”?
4. Статистические функции и их синтаксис?

В процессе повторения, на экране отображается презентация по прошлому уроку при изучении темы “Статистические функции”.

2.2. Сообщить тему и цели нового урока;
2.3. Объяснение нового материала;

2.3.1. Объяснить и показать при помощи проектора синтаксис функций Даты и время;

2.4. Повторение требований охраны труда.

3. Текущий инструктаж.

3.1. Выдать раздаточный материал;
3.2. Целевой обход;
3.3. Проверка правильной организации рабочих мест;
3.4. Проверка правильности выполнения приемов;
3.5. Работа с отстающими;
3.6. Контроль знаний.

4. Заключительный инструктаж.

4.1. Провести анализ выполненного задания;
4.2. Разобрать ошибки;
4.3. Сообщить оценки.

Конспект вводного инструктажа

Функции дат и времени используют для преобразования форматов даты и времени, вычислений промежутков времени, а также для вставки на лист автоматически обновляемых значений даты и времени. Всего в категории "Дата и время" имеется 20 функций.

Excel хранит даты в виде чисел от 1 до 2 958 465 (в версиях ранее Excel 97 - до 65 380). Числа эти - "номер по порядку" данного дня, отсчитанный от 1 января 1900 года. Порядковое число 1 соответствует 1 января 1900 года, число 2 – это 2 января 1900 года и т.д. Дата написания этой статьи - 20 октября 2012г. имеет порядковый номер 41202.

Такая система позволяет использовать даты в формулах. Например, с ее помощью легко подсчитать количество дней между двумя датами. Как это ни удивительно, но в программе Excel есть дата с порядковым числом, равным нулю, – 0 января 1900 года. Эта несуществующая дата используется для представления временных данных, которые не связаны с какой-либо конкретной датой.

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

Максимальная доступная дата – 31 декабря 9999 года (в версиях до Excel 97 – 31 декабря 2078 года).

Время Excel хранит так же в виде чисел, точнее, в виде десятичных дробей чисел соответствующих дат. То есть к порядковому номеру даты добавляется дробная часть, соответствующая части суток. Например, 06 марта 2011 года имеет порядковый номер 40608, а полдень 06 марта 2011 года (12:00) будет соответствовать числу 40608,5 так как 12 часов – это 0,5 от 24.

Максимальное значение времени, которое можно ввести в ячейку (без значения даты), составляет 9999:59:59 (без одной секунды 10 тыс. часов).

Список функций даты и времени

Для закрепления нового материала, учащимся предлагается выполнить практические задания на компьютере.

Текущий инструктаж

1. =ВРЕМЯЗНАЧ (время_как_текст), преобразует время из текстового формата в число. Введя формулу, задайте для ячеек формат “Время”;

2. =ВРЕМЯ (час; минута; секунда), функция преобразует заданные в виде чисел часы, минуты и секунды в число в виде кода Excel;

3. =ГОД (дата_в_числовом_формате), возвращает год, соответствующий заданной дате от 1900 до 9999;

4. =ДАТА (год; месяц; день), возвращает целое число, представляющее определенную дату;

5. =ДАТАЗНАЧ (дата_как_текст), функция возвращает числовой формат даты, представленной в виде текста. Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат;

6. =ДАТАМЕС (нач_дата; число_месяцев), функция возвращает порядковый номер даты, отстоящей на заданное количество месяцев вперед или назад от заданной даты;

7. =ДЕНЬ (дата_как_число), - преобразует дату в числовом формате в день месяца (от 1 до 31);

8. =ДЕНЬНЕД (дата_в_числовом_формате; тип), функция возвращает число от 1 до 7, соответствующему номеру дня недели для заданной даты. В данном примере выбираем “Тип” 2, т.е. понедельнику присваивается номер 1;

9. =ДНЕЙ360 (нач_дата; кон_дата; метод), функция вычисляет кол-во дней между двумя датами на основе 360 дневного года (12 месяцев по 30 дней).

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

15. = РАБДЕНЬ (нач_дата; число_дней; праздники), функция возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты;

16. = СЕГОДНЯ, функция возвращает дату в числовом формате (у данной функции нет аргументов);

17. =СЕКУНДЫ (время_в_числовом_формате), функция возвращает секунды в виде числа от 0 до 59;

18. = ТДАТА, функция возвращает текущую дату и время в числовом формате (у данной функции нет аргументов);

19. = ЧАС (время_в_числовом_формате), функция возвращает часы в виде числа от 0 до 23;

20. = ЧИСТРАБДНИ (нач_дата; кон_дата; праздники), функция возвращает количество полных рабочих дней между двумя датами;

Практическая работа 1

В ячейках выделенных зелёным цветом произведите соответствующие вычисления, используя функции “Даты и время” (ДЕНЬНЕД, НОМНЕДЕЛИ, РАБДЕНЬ);

Практическая работа 2

В ячейках выделенных синем цветом произведите соответствующие вычисления, используя функции “Даты и время” (ГОД, СЕГОДНЯ);

Практическая работа 3

В ячейках выделенных серым цветом произведите соответствующие вычисления, используя функции “Даты и время”.

Заключительный инструктаж

1. Провести анализ выполненного задания и контроля знаний;
2. Разобрать ошибки;
3. Сообщить оценки.