Как сделать дату в 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), но в разных книгах оно интерпретируется по-разному.

Существует одно преимущество использования системы дат 1904. Эта система позволяет отображать отрицательные значения времени. Другими словами, при использовании системы дат 1900 результат вычитания 4:00-5:30, не может быть отображен. При использовании системы дат 1904 результат отображается как отрицательное время -1:30 (т.е. разница составляет один час и 30 минут).

Ввод даты

Конечно, можно вводить дату как порядковое число, но, как правило, дата вводится с использованием одного из нескольких поддерживаемых в 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, которое можно открыть, выбрав команду ГлавнаяРедактированиеНайти и выделитьНайти или нажав комбинацию клавиш <Ctrl+F>. Имейте в виду, что Excel достаточно требовательна к точности ввода искомой информации. Вам придется ввести полный четырехразрядный год даты в поле Найти диалогового окна поиска данных. Формат даты должен соответствовать краткому представлению даты, принятому в системе. Это представление можно увидеть в строке формул.

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

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

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

=1/(24*60)

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

=1/(24*60*60)

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

0,99999999

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

Ввод времени

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

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

Примечание

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

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

18.06.2010 11:30:00

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

01.01.2000 1:00:00

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

02.09.2010 1:00:00

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

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

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

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

При создании формул со ссылками на ячейку, содержимое которой представляет дату или время, Excel автоматически преобразует результат выполнения формул в формат даты или времени. В некоторых случаях это может быть весьма кстати, в других же создает определенные неудобства и даже раздражает. К сожалению, Excel не позволяет отключить автоматическое форматирование ячейки. Тем не менее в подобных случаях можно воспользоваться специальной комбинацией клавиш, чтобы отменить форматирование всех чисел ячейки и вернуться к формату Общий, заданному по умолчанию. Для этого достаточно выделить ячейку и нажать комбинацию клавиш <Ctrl+Shift+~>.

Иногда можно столкнуться с тем, что ни один из встроенных форматов не подходит для нужного представления даты и времени. Не отчаивайтесь – 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.2.1990

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

29.02.1901

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

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

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

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

4 июль, 1776

Если планируете сортировать старые даты, введенные как текст, или хотите избавиться от проблем с форматами, применяйте даты в формате 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.

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

В начало

Полезное

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть