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

Переводы статьи Переводы статьи
Код статьи: 214094 - Vizualiza?i produsele pentru care se aplic? acest articol.
 

Дополнительные сведения о 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 и ознакомление с ними

Свойства

Код статьи: 214094 - Последний отзыв: 29 ноября 2013 г. - Revision: 2.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
Ключевые слова: 
kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com