В настоящее время вы работаете в автономном режиме; ожидается повторное подключение к Интернету
Войти

Использование функций даты и времени в приложении Excel

Поддержка Office 2003 завершена

8 апреля 2014 г. корпорация Майкрософт прекратила поддержку Office 2003. Это повлияло на обновления программного обеспечения и параметры безопасности. Узнайте, что это значит для вас и какие меры по безопасности можно предпринять.

 

Дополнительные сведения о Microsoft Office Excel 2007 см. на веб-странице справки и инструкции Office Online Excel 2007.
Аннотация
В статье приведены пошаговые инструкции по использованию функций даты и времени приложения Microsoft Excel для выполнения сложных вычислений со значениями этого типа.

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

В этой системе серийный номер 1 представляет собой полдень 1 января 1900 года. Значения времени сохраняются в виде десятичных дробей в диапазоне ,0–,99999, где ,0 — это 00:00:00, а ,99999 — это 23:59:59. Целые числа, представляющие значения даты, и десятичные дроби, представляющие значения времени, могут объединяться в числа, имеющие целую и десятичную составляющие. Например, число 32 331,06 представляет такую дату и время: 07.07.1988 01:26:24.

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

Функции TODAY() и NOW()

Функция TODAY определяет серийный номер текущей даты по системным часам без учета времени. Функция NOW возвращает серийный номер текущей даты с учетом времени.

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

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

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

Способы сравнения дат по серийному номеру

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

Например, при использовании функции NOW для сравнения даты с текущей датой, как в формуле
   =IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
 формула возвращает значение FALSE, даже если текущая дата — 01.10.1992; она возвращает значение TRUE, только если эта дата — полдень 01.10.1992. Если при сравнении двух

дат с помощью формулы нет необходимости включать в результат время, эту проблему можно решить, используя функцию TODAY:
   =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

Порядок работы с формулами дат

Вычисление количества дней между текущей датой и датой в будущем

Вот какая формула используется для вычисления количества дней между текущей датой и датой в будущем
   ="мм/дд/гг"-NOW()
где "мм/дд/гг" — это дата в будущем. Для форматирования ячеек формулы используется формат "Общий".

Вычисление количества дней, месяцев и лет между двумя датами

Вот как вы можете вычислить количество дней, месяцев и лет между двумя датами (начальная и конечная дата вводятся в ячейки A1 и A2 соответственно).

1. Создайте новую книгу.
2. Выберите вариант Чистая книга.
3. Введите в книгу следующие данные:

A1:25.03.1994A2: 01.05.1998
В ячейку D1 введите такую формулу:
 =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" лет, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" месяцев, "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" дней"

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

4. Если формула введена правильно, в ячейке D1 появится следующее значение:

4 лет, 1 месяцев, 6 дней


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

Примечание. При копировании приведенных формул удалите разрывы строк, иначе формулы не будут работать.
   Интервал времени                     Формула   ------------------------------------------------------------------------   Число дней между          =A2-DATE(YEAR(A2),MONTH(A2)-   двумя датами, без учета          IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"дней"   лет и месяцев      Число месяцев          =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2)   между двумя датами,        <=MONTH(A1), DAY(A2)<DAY(A1)),11,   без учета лет и дней          IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=                                    DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),                                    DAY(A2)<DAY(A1)),-1)))&" месяцев"   Число полных лет        =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<   между двумя датами                MONTH(A1),AND(MONTH(A2)=MONTH(A1),                                    DAY(A2)<DAY(A1))),1,0)&" лет"
Примечание В приведенных выше формулах вводить слова &" дней", &" месяцев" и &" лет" необязательно. Они служат только для удобства представления результата.

Вычисление количества рабочих дней между двумя датами

Вот как вы можете вычислить количество рабочих дней между двумя датами (начальная и конечная дата вводятся в ячейки A1 и A2 соответственно).

1. Создайте новую книгу.
2. Выберите вариант Чистая книга.
3. Введите в книгу следующие данные:
A1: 25.03.1994 A2: 01.05.1998

4. В ячейку D1 введите такую формулу:

=NETWORKDAYS(A1,A2)"1071" появляется в ячейке D1 в качестве результата.

Примечание. Если вместо результата появится #NAME, выберите пункт Надстройки в меню Сервис в Excel 2003. Установите флажок Пакет анализа и нажмите кнопку Да, если будет предложено установить пакет. В Excel 2007 и 2010 надстройка "Пакет анализа" уже встроена. 

Увеличение даты с определенным шагом

Чтобы увеличить дату на определенное число лет, месяцев или дней, воспользуйтесь следующей формулой, в которой ссылка — значение даты или ссылка на ячейку, содержащую значение даты, а значение1, значение2 и значение3 — значения приращений, на которые необходимо увеличить значения лет, месяцев и дней соответственно.
   =DATE(YEAR(ссылка)+значение1,MONTH(ссылка)+значение2,DAY(ссылка)+значение3)


Например, чтобы увеличить дату на один месяц, используйте такую формулу:
   =DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,    DAY(DATEVALUE("6/20/96")))

Порядок работы с формулами времени

Вычисление прошедшего времени

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

В следующем примере ячейки C2 и D2 содержат формулу =B2-A2, причем для ячейки C2 используется формат "Общий", то есть в ней отобразится десятичное число (в данном случае 0,53125 — это 12 часов 45 минут в форме серийного номера).
   A1: Время начала   B1: Время окончания    C1: Разница  D1: Разница                                        (Общий)       (ч:мин)   A2: 06:30      B2: 19:15     C2: 0,53125     D2: 12:45

Если между начальным и конечным значением времени оказывается полночь, необходимо учитывать 24-часовую разницу во времени. Для этого добавляется единица (1), представляющая 24-часовой интервал времени. Например, можно создать следующую таблицу, допускающую возможность попадания полуночи в интервал времени.
   A1: Время начала    B1: Время окончания    C1: Разница  D1: Разница                                         (Общий)       (ч:мин)   A2: 19:45       B2: 10:30    C2: 0,614583333  D2: 14:45


Вот какую формулу необходимо ввести для этого в ячейки C2 и D2:
   =B2-A2+IF(A2>B2,1)

Отображение значений времени, превышающих 24 часа

Для правильного отображения значения времени, превышающего 24 часа, можно использовать встроенный формат 37:30:55. Если вместо этого используется пользовательский формат, необходимо заключить параметр часов в квадратные скобки, например:
   [ч]:мин

Преобразование даты в десятичный формат

Для преобразования даты (ч:мин:сек) в десятичный формат (0,00) необходимо преобразовать серийный номер в десятичный по 24-часовому основанию. Для этого исходное значение времени умножается на 24 таким образом
   =Время*24
где Время — это число, которое необходимо преобразовать из формата времени в десятичный формат. Это значение может быть ссылкой на ячейку или рядом чисел в функции TIMEVALUE.

Вот какой будет формула, если ячейка A1 содержит время 4:30 (четыре часа 30 минут):
   =A1*24
Результат: 4,5.

Если ячейка содержит не только время, но и дату, используйте следующую формулу:
   =(Время-INT(Время))*24
Вот какой будет формула, если ячейка A1 содержит значение 20.06.1996 04:30:
   =(A1-INT(A1))*24
Результат снова составит 4,5.

Преобразование десятичного числа в формат даты

Для преобразования десятичного формата (0,00) в формат даты (ч:мин:сек) необходимо преобразовать десятичный номер в серийный по 24-часовому основанию. Для этого следует поделить исходное значение времени на 24 следующим образом:
   =Время/24
где Время — число, которое необходимо преобразовать из десятичного формата в формат времени. Это значение может быть числом или ссылкой на ячейку. Вот какой будет формула, если ячейка A1 содержит значение 4,5 (четыре часа 30 минут):
   =A1/24
Результат: 04:30.

Обмен файлами между Microsoft Excel для Mac и Microsoft Excel для Windows

По умолчанию приложение Excel для Mac использует систему дат 1904, а Excel для Windows — систему дат 1900. Это означает, что, если ввести в приложение Excel для Mac серийный номер 1 и применить формат даты, Excel отобразит 02.01.1904 12:00. Excel для Windows отобразит порядковый номер 1 как 01.01.1900 12:00.

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

 Вот как вы можете перейти в систему дат 1904 в Excel для Windows.  
  1. В Excel 2003 в меню Сервис выберите команду Параметры. В Excel 2007 и более новых версиях перейдите к элементу Параметры Excel.
  2. В Excel 2003 во вкладке Вычисления установите флажок Система дат 1904. В Excel 2007 и более новых версиях выберите параметр Дополнительно. Прокрутите вниз до списка При пересчете этой книги: и установите флажок Использовать систему дат 1904.
 Вот как вы можете перейти в систему дат 1904 в Excel для Mac.  
  1. В меню Excel выберите команду Параметры.
  2. В разделе Формулы и списки выберите элемент Вычисление.
  3. В разделе Параметры книги установите флажок Система дат 1904
Ссылки
Подробнее об этом см. такие статьи базы знаний Майкрософт:
214233 Текст или число в приложении Excel преобразуются в неправильный числовой формат
214386 Максимальные значения времени в приложении Microsoft Excel
241072 При просмотре формул значения даты и времени отображаются в виде серийных номеров
264372 Управление параметрами в диалоговом окне "Формат ячеек" в Excel и ознакомление с ними
 reviewdocID XL2007 XL2010
Свойства

Номер статьи: 214094 — последний просмотр: 11/29/2013 20:17:00 — редакция: 2.0

  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094
Отзывы и предложения