какая самая ранняя дата с которой может работать excel

Принципы управления датами и временем в Excel

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

Примеры, которые будут использоваться здесь, соответствуют формату даты, принятому в русифицированном приложении Excel: дд.мм.гггг или дд/мм/гггг. Например, дата 1.03.1952 или 01/03/1952 означает 1 марта 1952 года. Имейте в виду, что это не 3 января 1952 года, как принято в региональном формате США.

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

Представление даты как порядкового числа

Для Excel дата – это порядковое число, которое представляет собой количество дней, прошедших с 0 января 1900 года. Это означает, что порядковое число 1 соответствует дате 1 января 1900 года; число 2 – дате 2 января 1900 года и т.д. Существование такой системы позволяет использовать формулы при работе с датами. Например, она позволяет создать формулу, которая будет вычислять количество дней между двумя датами.

Безусловно, виртуальная дата 0 января 1900 года может вызвать недоумение. Конечно, она не является датой сотворения мира, а просто означает определенное мировое время. Далее мы рассмотрим это понятие более подробно.

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

Примечание

Excel 2000 и более поздние версии этого приложения поддерживают даты в диапазоне от 1 января 1900 года до 31 декабря 9999 года (порядковое число этой даты 2958465). Диапазон дат, доступный в более ранних версиях приложения, значительно меньше. Здесь используются даты с 1 января 1900 года и до 31 декабря 2078 года (порядковое число 65380).

Выберите систему дат: 1900 или 1904

Excel поддерживает две системы дат: система дат, начиная с 1900 или 1904 года. Какую из них предпочесть в рабочей книге, вы определяете самостоятельно, в зависимости от того, какая дата служит основанием для других дат. В системе дат, начинающейся с 1900 года, день 1 января 1900 года имеет порядковое число 1. В системе дат 1904 года начало отсчета – 1 января 1904 года. По умолчанию для систем Windows приложение Excel использует систему дат, начинающую отсчет с 1 января 1900 года, а для систем Macintosh – дату, начиная с 1904 года. Тем не менее в Excel для Windows с целью обеспечения совместимости с файлами Macintosh тоже поддерживается система дат 1904. Систему дат можно изменить, используя диалоговое окно параметров программы. Чтобы открыть его, выберите команду Файл→Параметры Excel, а затем щелкните на вкладке Дополнительно. В зависимости от своих намерений установите или снимите в разделе При пересчете этой книги флажок Использовать систему дат 1904. Имейте в виду, что система дат не может изменяться, если в компьютере используется версия Excel для Macintosh.

Рекомендуем использовать систему дат, установленную по умолчанию (начиная с 1900 года). Будьте особенно осторожны, когда применяете две различные системы дат в связанных рабочих книгах. Предположим, что Книга1 использует систему дат, начиная с 1904 года, и содержит в ячейке А1 дату 15/1/1999, а Книга2, наоборот, использует систему дат с 1900 года и содержит ссылку на ячейку A1 книги Книга1. В книге Книга2 эта дата будет отображаться как 14/1/1995. Таким образом, обе рабочие книги используют один и тот же порядковое число дня (34 713), но в разных книгах оно интерпретируется по-разному.

Ввод даты

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

Например, если необходимо ввести дату 3 июня 2010 года, то это можно сделать весьма просто – 3 Июнь, 2010, используя данный формат или любой другой из имеющихся форматов даты. Excel преобразует введенную информацию и сохранит ее как числовое значение 39 251 – порядковое число, соответствующее этой дате. После этого Excel применит формат даты, заданный по умолчанию, поэтому содержимое ячейки может быть отображено в представлении, несколько отличном от введенного.

Примечание

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

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

Чтобы изменить заданный по умолчанию формат даты, необходимо изменить региональные параметры операционной системы. Для этого в системном меню Пуск операционной системы Windows 7 выберите команду Панель управления→Язык и региональные стандарты и выберите в соответствующих раскрывающихся списках краткий и полный форматы дат. В Excel по умолчанию применяется краткий формат даты.

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

• 1 июня 2010;
• Июн-1 2010;
• Июн-1/2010.

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

Существует еще один недостаток. Если введенная дата находится вне поддерживаемого диапазона дат, Excel также интерпретирует ее как текст. Если порядковое число, находящееся за пределами доступных для даты числовых значений, попытаться привести к формату даты, вместо значения будет отображаться ряд знаков “решетки” (#########).

Поиск даты

Представление времени как порядкового числа

В Excel в качестве времени применяется дробная часть порядкового числа, обозначающего дату. Другими словами, Excel считает сутки единицей, а все величины меньше суток – соответствующей частью единицы. Например, порядковое число даты 18 июня 2010 года равно 40347, а полдень того же дня имеет значение 40347,5.

Порядковое число одной минуты эквивалентно приблизительно 0,00069444. Формула, приведенная ниже, вычисляет этот номер следующим образом: 24 часа умножается на 60 минут, а затем 1 делится на полученный результат. Таким образом, в знаменателе получается количество минут в одном дне (1440).

Тем же способом в Excel определяется порядковое число одной секунды – приблизительно 0,00001157. Для этого 1 делится на 24 часа, умноженные на 60 минут и 60 секунд. В этом случае знаменатель содержит количество секунд в одном дне (86400).

Самая маленькая единица измерения времени в приложении Excel – одна тысячная доля секунды (миллисекунда). Порядковое число приведенного ниже значения представляет время 23:59:59,999, или без одной тысячной секунды полночь.

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

Ввод времени

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

Поскольку приведенные выше примеры времени не связаны ни с каким определенным днем, Excel по умолчанию использует порядковое число 0, соответствующее дате 0 января 1900 года, которая, по своей сути, не является датой.

Примечание

При использовании системы дат, начинающейся с 1904 года, временные значения без значения даты отсчитываются от первого января 1904 года. Все приведенные ниже примеры основываются на системе дат, начинающейся с 1900 года.

Чтобы объединить дату и время, используйте совместный формат даты и времени, который состоит из формата даты, пробела и формата времени. Например, если в ячейку ввести текст 18 июнь, 2010 11:30, приложение Excel вычислит порядковое число 39251,4791666667 и отобразит его в доступном формате даты и времени.

В некоторых случаях, когда вводится время, превышающее 24 часа, связанная с ним дата соответственно увеличивается. Например, если ввести в ячейку время 25:00:00, Excel увеличит ту часть, которая относится ко дню (поскольку время превышает 24 часа), и отобразит введенное значение следующим образом:

То же самое происходит, если ввести одновременно и дату, и время, превышающее 24 часа. Введенная дата при отображении будет откорректирована. Например, при вводе в ячейку 01.09.2010 25:00:00 на экране отображается следующая информация:

При вводе только времени без соответствующей даты максимальное время, которое можно ввести в ячейку равно 9999:59:59, т.е. менее 10000 часов. В этом случае Excel добавляет соответствующее количество дней, и на экране отображается полученная дата. Если же ввести время, превышающее предел 10000 часов, то оно будет представлено как простая текстовая строка.

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

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

Для форматирования ячейки с датой и временем необходимо выделить ячейку и щелкнуть во вкладке Главная на раскрывающемся списке числовых форматов. Этот список содержит два формата даты и один формат времени.

Иногда можно столкнуться с тем, что ни один из встроенных форматов не подходит для нужного представления даты и времени. Не отчаивайтесь – Excel предоставит вам прекрасную возможность создать пользовательский числовой формат. Выберите в списке пункт Другие числовые форматы (см. рисунок выше) и введите определение пользовательского формата в поле Тип.

Следующий пользовательский формат данных особенно полезен для отображения времени:

Заключите часы в квадратные скобки, чтобы иметь возможность отображать часы за пределами 24. Использовать этот способ весьма удобно при добавлении более 24 часов.

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

Несмотря на всю эффективность форматов дат, в Excel все же иногда возникают проблемы при работе с датами. Многие из них уходят в прошлое. Excel была разработана много лет назад, еще раньше, чем так называемая “проблема 2000 года” впервые потрясла мир. Но во многом разработчики Excel “отталкивались” от Lotus 1-2-3 с ее ограниченными возможностями управления датами и временем. Поэтому все самые серьезные технические дефекты были непреднамеренно перенесены в Excel. Кроме того, версии самой Excel не единообразно интерпретируют записи дат с годом из двух цифр. И наконец, то, как Excel понимает запись даты в ячейке, напрямую зависит от региональных параметров, установленных в Windows.

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

Ошибка високосного года

Високосный год, который наступает через каждые четыре года, имеет один дополнительный день – 29 февраля. Несмотря на то, что 1900 год не был таковым, Excel воспринимает его как високосный. Другими словами, если ввести в ячейку 29.02.1900, Excel не отобразит сообщение об ошибке, а интерпретирует это как значение даты, соответствующее 60-му дню с начала отсчета (порядковое число 60):

Однако если ввести другую недопустимую дату 29.02.1901, Excel отобразит сообщение об ошибке. Приложение не преобразует эту запись в дату, а, скорее, воспримет ее как текстовую строку:

Возникает вопрос: как программный продукт, который ежедневно используют миллионы людей, может содержать такую очевидную ошибку? Ответ кроется в истории его создания. Исходная версия Lotus 1-2-3, на которой базировалось приложение Excel, содержала дефект, по причине которого 1900 год рассматривался как високосный. Разработчики приложения Excel знали об этой ошибке и позднее, при выпуске приложения, специально перенесли этот дефект, чтобы обеспечить его совместимость с рабочими листами файлов Lotus.

Но почему же эта ошибка до сих пор существует в более поздних версиях Excel? Компания Microsoft утверждает, что проблемы, которые могут возникнуть при исправлении этой ошибки, значительно перевешивают преимущества исправления. Устранение этой ошибки привело бы в беспорядок сотни тысяч существующих рабочих книг. Кроме того, исправление указанной проблемы сказалось бы на совместимости самой Excel с другими программами, использующими дату. Именно поэтому данная ошибка влечет за собой гораздо меньше проблем, чем ее исправление. К тому же, большинство пользователей не используют даты, предшествующие 1 марта 1900 года.

Использование дат до 1900 года

Безусловно, мир начал свое существование не с 1 января 1900 года. Пользователи Excel, которые работают с исторической информацией, часто используют даты до 1 января 1900 года. Но, к сожалению, единственный способ работы с такими датами в общепринятых форматах – это ввести их в ячейку как текстовую информацию. Например, если ввести в текстовую ячейку следующую строку, Excel воспримет ее без сообщения об ошибке:

Если планируете сортировать старые даты, введенные как текст, или хотите избавиться от проблем с форматами, применяйте даты в формате UTC (Universal Time Coordinated – универсальное скоординированное время по Гринвичу). В формате UTC (стандарт ISO 8601) дата записывается так: гггг-мм-дд, например, 1776-07-24. Это стандартный международный формат даты и времени, используемый в авиационной и морской навигации. Его основное преимущество состоит в том, что он правильно считывается, обрабатывается и сортируется практически в любой системе (в том числе, в Excel) и при любых параметрах региональных форматов по той причине, что в нем все разряды по старшинству расположены слева направо. Для сравнения: в русском формате даты (например, 24.7.1776) год (старший разряд) расположен правее дня (младшего разряда). К сожалению, среди пользователей формат UTC мало популярен (как в США, так и в России), потому что для них он непривычный и по этой причине плохо воспринимается визуально.

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

Ввод противоречивых дат

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

В Excel 7 годы, обозначаемые двумя числами в диапазоне между 00 и 29, интерпретируются как принадлежащие к XXI столетию, а даты между 30 и 99 – как даты XX столетия. Например, если ввести в ячейку запись 5.12.28, то Excel интерпретирует ее как 5 декабря 2028 года. Но если ввести дату 5.12.30, Excel воспримет ее как 5 декабря 1930 года. В Excel 2000 или более поздней версии, работающей под управлением Windows 98 и выше, в качестве граничного года, заданного по умолчанию, используется 2029 год. В то же время вы можете самостоятельно установить граничный год с помощью окна региональных стандартов Windows.

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

Источник

3 примера, как функция ЕСЛИ работает с датами.

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

Примеры работы функции ЕСЛИ с датами.

Дата в качестве условия, с которым работает функция ЕСЛИ, может быть записана в какую-то ячейку Excel, либо же прямо вставлена в формулу. Вот тут-то и возникают некоторые особенности и сложности работы функции ЕСЛИ с датами.

Пример 1. Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

Иногда случается, что записать дату непосредственно в функцию ЕСЛИ, не ссылаясь ни на какую ячейку. В этом случае возникают некоторые сложности.

В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.

Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

Полная формула ЕСЛИ может иметь следующую форму:

=ЕСЛИ(B2

Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

Пример 2. Формула условия для дат с функцией СЕГОДНЯ()

В случае, когда даты записаны в ячейки таблицы Excel, применять ДАТАЗНАЧ нет необходимости.

В столбце C отметим товар, который уже поступил. В ячейке C2 запишем:

=ЕСЛИ(B2

Пример 3. Расширенные формулы ЕСЛИ для будущих и прошлых дат

Предположим, вы хотите отметить только те даты, которые отстоят от текущей более чем на 30 дней.

Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”,””)

Если условие не выполнено, то в ячейку запишем пустую строку “”.

А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”, ЕСЛИ(B2-СЕГОДНЯ()>30,”Ожидается”,””))

Источник

Системы дат в Excel

Excel поддерживает две системы дат: систему дат 1900 и систему дат 1904. В каждой системе дат используется уникальная начальная дата, с которой вычисляются все остальные даты книги. Все версии Excel для Windows вычисляют даты на основе системы дат 1900. Excel 2008 для Mac и более ранних Excel для Mac вычисляют даты на основе системы дат 1904. Excel 2016 для Mac и Excel для Mac 2011 используют систему дат 1900, которая гарантирует совместимость с Excel для Windows.

При копировании дат из книги, созданной в более ранней версии, в книгу, созданную в Excel 2016 или 2011 для Mac, они будут преобразовываться автоматически, если в параметрах > > Параметры даты не будет отключен параметр “Автоматически преобразовывать систему дат”. Если этот параметр отключен, вы получите сообщение с вопросом, следует ли преобразовывать даты при вклеии. У вас есть два варианта действий. Вы можете преобразовать даты, чтобы использовать систему дат 1900 (рекомендуется). Этот параметр делает даты совместимыми с другими датами в книге. Вы также можете сохранить систему дат 1904 только для вклеиных дат.

Примечание: Даты, скопированные в составе диаграммы, нельзя преобразовать из системы дат 1904 г. и могут различаться примерно на четыре года. Эти даты необходимо преобразовать вручную.

Система дат 1900

В системе дат 1900 даты вычисляются с использованием 1 января 1900 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1900 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 40729. Это стандартная система дат в Excel для Windows, Excel 2016 для Mac и Excel для Mac 2011. Если вы решите преобразовать вкопированные данные, Excel скорректирует значения, а вкопированные даты будут соответствовать скопированные даты.

Система дат 1904 г.

В системе дат 1904 даты вычисляются с использованием 1 января 1904 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1904 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 39267. Это стандартная система дат в более ранних версиях Excel для Mac. Если вы решили не преобразовывать данные и сохранить систему дат 1904 г., то вкопированные даты будут отличаться от дат, которые вы скопировали.

Разница между системами дат

Так как в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными номерами. Например, 5 июля 2011 г. может иметь две разные серийные номера:

Разница между двумя системами дат составляет 1462 дня. Это означает, что порядковый номер даты в системе дат 1900 всегда на 1462 дня больше, чем в системе дат 1904. 1462 дня — это четыре года и один день (включая один високосный день).

Изменение системы дат по умолчанию

Выберите Файл > Параметры.

(В Excel 2007 нажмите кнопку Microsoft Office кнопку и выберите Excel параметры.)

Выберите пункт Дополнительно.

В области При вычислении этой книгивыберите или clear Use 1904 date system.

В меню Excel выберите пункт Параметры.

В списке Формулы и спискивыберите вычисление .

Выберите или сберем его, чтобы использовать систему дат 1904.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Функции даты и времени (справка)

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

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.

Возвращает заданную дату в числовом формате.

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

Преобразует дату из текстового формата в числовой.

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

ДНИ

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

Вычисляет количество дней между двумя датами на основе 360-дневного года.

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

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

Преобразует дату в числовом формате в часы.

НОМНЕДЕЛИ.ISO

Возвращает номер недели по ISO для заданной даты.

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

Преобразует дату в числовом формате в месяцы.

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

ЧИСТРАБДНИ.МЕЖД

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

Возвращает текущую дату и время в числовом формате.

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

Возвращает заданное время в числовом формате.

Преобразует время из текстового формата в числовой.

Возвращает текущую дату в числовом формате.

Преобразует дату в числовом формате в день недели.

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

Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

РАБДЕНЬ.МЕЖД

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

Преобразует дату в числовом формате в год.

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Источник