Вычисления с датами и временем

Excel. Лабораторная работа 3 Даты и время

Цель работы

Научиться использовать формулы и форматы для работы с датами и временем.

Задание

Ознакомьтесь с теоретическими сведениями, приведенными ниже.

Создайте новую книгу Excel и сохраните ее под именем вида «Excel. Л.р. 3 группа ФИО».

Все задания выполняется на одном листе Excel. В начале листа в качестве заголовка указывать номер и название работы, сведения о том, кто выполнил работу (ФИО, группа).

Нумеровать задания необязательно, но нужно подписывать содержимое ячеек. Ячейки с формулами закрасьте зеленым цветом, например:

Задание 1. Определите сегодняшнюю дату и текущее время. Вычислите:

- какая дата будет через 45 дней?

- сколько времени будет через 2 часа?

- сколько времени будет через 95 минут?

- какое число будет через 2 месяца?

- сколько лет осталось до 2100 г.?

- какое число будет через 14 рабочих дней?

- сколько дней прошло с начала календарного года?

- сколько из них было рабочих дней и выходных?

- сколько недель прошло с начала семестра?

Чтобы выполнить вычисления с рабочими днями, на отдельном листе выпишите все праздничные даты за этот год, и подставьте их в формулы.

Задание 2. Введите в отдельную ячейку дату рождения (свою или любую другую). Определите, какой это был день недели. Вычислите:

- сколько вам лет;

- сколько всего вы прожили дней;

- сколько всего вы прожили часов;

- сколько всего вы прожили минут.

Задание 3. С помощью автозаполнения и формул составьте таблицу, содержащую следующие столбцы:

1) номер месяца;

2) название месяца;

3) дата начала месяца в текущем году;

4) дата конца месяца в текущем году;

5) сколько в месяце было рабочих дней.

Теоретическая часть

Представление даты и времени

Дата и время представляются в компьютере одним числом – сколько дней прошло с 1 января 1900 г.

Примеры:

0,0 = 00:00:00

0,5 = 12:00:00

0,8 = 19:12:00

1,0 = 01.01.1900 г. 00:00:00 (или просто 01.01.1900)

1,6 = 01.01.1900 г. 14:24:00

32 = 01.02.1900 г.

366 = 31.12.1900г.

Соответственно, час = 1/24 ≈ 0,04167, минута 1/24/60 ≈ 0,000694, год = 365 (если не високосный) и т. д. Поскольку компьютер хранит дробные числа со многими знаками после запятой, то время сохраняется с точностью не до секунд, а до миллисекунд, и даже мельче.

Форматы даты и времени

В зависимости от формата ячейки дату и время можно отобразить по-разному (например, 01.09.2016; 1 сентября 2016; сен.2016; 2016/09/01 и др.).

Стандартные форматы представления даты и времени можно найти в списке на ленте на вкладке «Главная». Но это не полный список. Другие варианты отображения данных в ячейке можно выбрать через пункт «Другие числовые форматы…» или через правый щелчок – пункт меню «Формат ячеек…» – вкладка «Число».

В разделах «Дата» и «Время» можно выбрать один из списка наиболее распространенных форматов или задать свой в пункте «(все форматы)».

При этом используются следующие обозначения:

Обозначение	Пояснение	Примеры
Д	День месяца одной или двумя цифрами	1, 6, 12, 31
ДД	День месяца всегда двумя цифрами	01, 06, 12, 31
ДДД	День недели (кратко)	пн, вт, ср, чт, пт, сб, вс
ДДДД	День недели (полностью)	понедельник, вторник
М	Номер месяца одной или двумя цифрами	1, 2, 3, 4, 10, 11, 12
ММ	Номер месяца всегда двумя цифрами	01, 02, 03, 11, 12
МММ	Краткое название месяца	янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек
ММММ	Полное название месяца	январь, февраль
ГГ	Год двумя цифрами	98, 01, 16
ГГГГ	Год четырьмя цифрами	1998, 2001, 2016
ч	Час одной или двумя цифрами в 24-часовом формате	1, 2, 20, 23
чч	Час всегда двумя цифрами в 24-часовом формате	01, 02, 20, 23
м	Минуты одной или двумя цифрами	1, 5, 15, 40, 59
мм	Минуты всегда двумя цифрами	01, 05, 15, 40, 59
с	Секунды одной или двумя цифрами	1, 5, 15, 40, 59
сс	Секунды всегда двумя цифрами	01, 05, 15, 40, 59
AM/PM	Часы будут выводиться в 12-часовом формате, утро обозначается AM, вечер PM	10:00 PM 08:16 AM

Символы точка (.), слеш (/), дефис (-), запятая (,), круглые скобки, пробелы, а также текст в кавычках отображаются «как есть».

Например:

Д МММ ГГГГ “г.” 1 сен 2016 г.

ДД/ММ/ГГ (ДДД) 01/09/16 (Чт)

ДДДД, ДД МММ четверг, 01 сен

ММММ ГГГГ сентябрь 2016

ММ-ГГ 09-16

Д.М 1.9

ДД.ММ.ГГ ч:мм 01.09.16 8:30

чч:мм:сс/ДДД 08:30:00/Чт

мм:сс 30:00

Таким образом, чтобы узнать день недели, как это требуется в задании, достаточно отобразить дату в формате «ДДДД».

Распространенная ошибка! Если сбить формат отображения даты, то Excel покажет ее «как есть» – числом дней. Например, 01.09.2015 = 42 248. Ровно столько дней прошло с 01.01.1900. В этом случае необходимо вернуть правильный формат ячеек.

Вычисления с датами и временем

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

Наиболее часто используемые действия:

– дата + число = какая дата будет через «число» дней

– дата1 – дата2 = сколько дней прошло между двумя датами

– время * 24 = сколько всего часов (сутки в часы)

– время * 24 * 60 = сколько всего минут (сутки в минуты)

– время * 24 * 60 * 60 = сколько всего секунд (сутки в секунды)

Например, формула

вычисляет, какое число будет через 50 дней после 1 сентября 2016 г.

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

Вычисление, сколько дней осталось до Нового Года, т.е. сколько дней между сегодняшней датой и 31 декабря:

Кроме того, Excel содержит ряд функций специально для работы с датами и временем. Их все можно найти на вкладке «Формулы» – группа «Библиотека функций» – кнопка меню «Дата и время».

Для каждой функции в справке по Office есть подробное описание с примерами. Основные функции записаны в таблице ниже.

Функция

Пояснение

Пример

ДЕНЬ(дата)

Номер дня месяца от 1 до 31

=ДЕНЬ(“12.10.2016”)

ДЕНЬНЕД(дата; [тип])

Номер дня недели:

если тип = 1, то от 1 (вс) до 7 (сб)

если тип = 2, то от 1 (пн) до 7 (вс)

если тип = 3, то от 0 (пн) до 6 (вс)

Если тип не указан, то он считается равным 1

=ДЕНЬНЕД(“13.10.2016”)

3 (вт)

=ДЕНЬНЕД(“13.10.2016”;2)

2 (вт)

=ДЕНЬНЕД(“12.10.2016”;3)

0 (пн)

МЕСЯЦ(дата)

Номер месяца от 1 до 12

=МЕСЯЦ(“12.10.2016”)

ГОД(дата)

Номер года от 1900 до 9999

=ГОД(“12.10.2016”)

2016

ЧАС(дата_время)

Извлекает час из даты и времени

=ЧАС(“14:15:07”)

МИНУТА(дата_время)

Аналогично минуты

=МИНУТА(“14:15:07”)

СЕКУНДА(дата_время)

Аналогично секунды

=СЕКУНДА(“14:15:07”)

ТДАТА()

Текущая дата и время (по системным настройкам)

=ТДАТА()

17.10.2018 23:12

СЕГОДНЯ()

Текущая дата без времени (время 00:00:00)

=СЕГОДНЯ()

17.10.2018

ДАТАМЕС(нач_дата; число_месяцев)

Какая будет дата через «число_месяцев» от «нач_дата»

=ДАТАМЕС("12.10.2016";1)

12.11.2016

=ДАТАМЕС("12.10.2016";6)

12.04.2017

КОНМЕСЯЦА(нач_дата; число_месяцев)

Дата конца месяца спустя «число_месяцев» от «нач_дата» (0 – конец того же месяца

=КОНМЕСЯЦА("12.10.2016";0)

31.10.2016

=КОНМЕСЯЦА("12.10.2016";2)

31.12.2016

=КОНМЕСЯЦА("12.10.2016";12)

31.10.2016

=КОНМЕСЯЦА("12.10.2016";-1)

30.09.2016

ДАТА(год;месяц;день)

Возвращает полную дату по номерам года, месяца и дня

=ДАТА(2016;10;11)

11.10.2016

РАБДЕНЬ(нач_дата; число_дней; праздники)

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

=РАБДЕНЬ("17.04.2017"; 7)

26.04.2017

ЧИСТРАБДНИ(нач_дата; кон_дата; праздники)

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

= ЧИСТРАБДНИ ("01.04.2017"; "20.04.2017")

Функции можно комбинировать друг с другом.

Например, чтобы получить дату последнего дня (31 декабря) текущего года можно использовать такую формулу:

Функция СЕГОДНЯ дает нам сегодняшнюю дату, функция ГОД берет из нее только год (отбрасывает день и месяц), а функция ДАТА ставит для этого года 12 месяц и 31 число.

Контрольные вопросы

1. Как представляются даты в компьютере?

2. Как вычислить, какое число будет завтра?

3. Что означает запись «мм» в формате даты?

4. Как записать следующий формат даты: «Понедельник: 17 апр.»?

5. Как указать праздничные дни в формулах для вычислений с рабочими днями?