какая система дат используется в эксель
ДАТА (функция ДАТА)
С помощью функции ДАТА в Excel можно объединить три отдельных значения в дату.
Функция ДАТА возвращает порядковый номер определенной даты.
Аргументы функции ДАТА описаны ниже.
Год — обязательный аргумент. Значение аргумента год может содержать от одной до четырех цифр. Excel интерпретирует аргумент год в соответствии с используемой системой дат, используемой на вашем компьютере. По умолчанию в Microsoft Excel для Windows используется система дат 1900, то есть первой датой считается 1 января 1900 г.
Совет: Во избежание непредвиденных результатов используйте в качестве значения аргумента год четыре цифры. Например, значение «07» может интерпретироваться как «1907» или «2007». Четырехзначное обозначение года предотвращает возникновение путаницы.
Если аргумент год находится в диапазоне от 0 (нуль) до 1899 (включительно), Excel для вычисления года прибавляет это значение к числу 1900. Например, функция ДАТА(108;1;2) возвращает 2 января 2008 (1900+108) года.
Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, функция ДАТА(2008;1;2) возвращает 2 января 2008 года.
Если значение аргумента год меньше 0 или больше 9999, Excel возвращает значение ошибки #ЧИСЛО!.
Месяц — обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 (январь) до 12 (декабрь), представляющее месяц года.
Если значение аргумента месяц больше 12, аргумент месяц добавляет число месяцев к первому месяцу указанного года. Например, функция ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года.
Если значение аргумента месяц меньше 1, аргумент месяц вычитает значение этого числа месяцев, увеличенное на 1, из первого месяца указанного года. Например, функция ДАТА(2008;-3;2) возвращает число, соответствующее 2 сентября 2007 года.
День — обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.
Если значение аргумента день больше числа дней в указанном месяце, аргумент день добавляет это число дней к первому дню месяца. Например, функция ДАТА(2008;1;35) возвращает число, соответствующее 4 февраля 2008 года.
Если значение аргумента день меньше 1, аргумент день вычитает значение этого числа дней, увеличенное на 1, из первого дня указанного месяца. Например, функция ДАТА(2008;1;-15) возвращает число, соответствующее 16 декабря 2007 года.
Примечание: В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. Дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней. Чтобы вывести правильную дату, нужно изменить формат ячейки.
Например, =ДАТА(C2;A2;B2) объединяет значение года из ячейки C2, месяца из ячейки A2 и дня из ячейки B2 и помещает их в одну ячейку в виде даты. В примере ниже показан результат в ячейке D2.
Нужно вставить даты, не используя формулу? Никаких проблем! Вы можете вставить в ячейку текущую дату и время или обновляемую дату. Вы также можете автоматически заполнить ячейки листа данными.
Щелкните правой кнопкой мыши ячейки, которые вы хотите изменить. На компьютере Mac щелкните ячейки, удерживая нажатой клавишу CTRL.
На вкладке Главная щелкните Формат > Формат ячеек или нажмите клавиши CTRL+1 (COMMAND+1 на компьютере Mac).
3. Выберите нужный язык (местоположение) и формат даты.
Дополнительные сведения о форматировании дат см. в статье Пользовательское форматирование даты.
Функцию ДАТА можно использовать для формирования даты на основе даты в другой ячейке. Например, с помощью функций ГОД, МЕСЯЦ и ДЕНЬ можно создать дату годовщины, основанную на другой ячейке. Допустим, что сотрудник начал работать 01.10.2016. С помощью функции ДАТА можно рассчитать пятилетний юбилей его работы.
Функция ДАТА формирует дату.
Функция ГОД получает значение «2012» из ячейки C2.
К этому значению прибавляется пять лет («+5»), после чего в ячейке D2 выводится «2017».
Функция МЕСЯЦ получает значение «3» из ячейки C2 и добавляет его в ячейку D2 в качестве значения месяца.
Функция ДЕНЬ получает значение «14» из ячейки C2 и добавляет его в ячейку D2 в качестве значения дня.
Если открыть файл, полученный из другой программы, Excel попытается распознать даты в данных. Но иногда это не удается сделать. Возможно, это связано с тем, что числа не похожи на обычные даты или отформатированы как текст. В таком случае их можно преобразовать в даты с помощью функции ДАТА. Например, на приведенном ниже рисунке ячейка C2 содержит дату, которая имеет формат ГГГГММДД. Она также отформатирована как текст. Для ее преобразования в дату функция ДАТА используется в сочетании с функциями ЛЕВСИМВ, ПСТР и ПРАВСИМВ.
Функция ДАТА формирует дату.
Функция ЛЕВСИМВ получает первые 4 знака слева в ячейке C2. При этом значение «2014» становится годом преобразованной даты в ячейке D2.
Функция ПСТР ссылается на ячейку C2. Она получает 2 знака справа от 5-го знака. При этом значение «03» становится месяцем преобразованной даты в ячейке D2. «0» не включается в результат из-за формата Дата, заданного для ячейки D2.
Функция ПРАВСИМВ получает первые 2 знака в ячейке C2 от правого края и добавляет «14» в ячейку D2 в качестве значения дня.
Чтобы увеличить или уменьшить дату на определенное количество дней, просто добавьте его к значению либо ссылке на ячейку с датой или вычтите его.
В приведенном ниже примере ячейка A5 содержит дату, которую нужно увеличить или уменьшить на 7 дней (значение в ячейке C5).
Системы дат в 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. Подробнее об этих различиях.
Как работать с датами в Excel?
Научитесь использовать все прикладные инструменты из функционала MS Excel.
История о том, как я пропустил свидание с очаровательной блондинкой…
Вы никогда не попадали впросак из-за того, что неправильно читали дату? «Да детей в первом классе учат читать календарь, называть месяца по порядку, считать количество дней, прошедших с того или иного момента и так далее», – скажете вы.
И я с вами соглашусь. Но, позвольте спросить: 07-08-2017 и 07.08.2017 – это одна и та же дата?
Вот и мне казалось, что это глупый вопрос, пока не познакомился с одной симпатичной девушкой. Мы долго переписывались, выяснилось, что она живет в Америке и скоро приедет в Россию. Мы договорились о встрече. День Х был назначен на 07-08-2017.
Наступил июль. А я с нетерпением ждал августа. 7 июля рассерженная девушка разрывала мою телефонную трубку, крича на весь ресторан какой я «сякой» (цитировать не буду). Проблема была в том, что я сидел дома, считая дни до 7 августа, а она ждала меня в назначенном месте встречи.
Вот тогда стало все понятно: в США формат даты выглядит следующим образом: MM-DD-YYYY, а мы привыкли к иной записи: DD.MM.YYYY. Во многих европейских странах используется совсем другой стандарт: YYYY-MM-DD. Вот и поди, разберись какая дата написана! Да и в голову не придет ни нам, ни им, что бывает по-разному.
С тех пор я очень внимательно отношусь к написаниям даты. Хорошо, что хоть формат времени у всех одинаковый… а то представляете, какая путаница была бы!
Вообще всегда дата и время были самыми распространенными типами данных и одновременно самыми сложными. Любые таблицы содержат дату или время, будь то данные о транзакциях, о продажах, расписание чего-либо, статистика, финансы…
Поэтому этой теме уделяется пристальное внимание. Мы вам расскажем о том, какие процессы и преобразования с датами можно делать с помощью специальных функций в Excel.
Как в Excel хранятся даты
Источником большей части путаницы с датами и временем в Excel является способ хранения информации программой.
Иногда бывает, что пишешь в ячейку дату, например, 03.06.2014, а на выходе получаешь что-то непонятное: 41793. Откуда взялось это число?!
Дело в том, что Excel хранит даты в виде целых чисел, который представляют количество дней, прошедших с начала 1900 года, а затем уже числа форматируются для отображения в виде даты.
То есть 1 января 1900 года — это 1.
2 января 1900 года – это 2.
20 августа 2020 года будет сохранено как 44063 и так далее.
Чтобы посмотреть, какое целое число присвоено определенной дате, достаточно перевести формат ячейки из Даты в Числовой.
Это кажется запутанным, но зато благодаря такому подходу намного проще складывать, вычитать и считать количество дней между датами (операцию с двумя, даже большими числами выполнять проще и быстрее, чем с шестью: день, месяц, год).
У самых внимательных уже должен был назреть вопрос: как сохраняются даты до 1 января 1900 года? Дело в том, что они не распознаются в Excel как дата. То есть нет «отрицательных» порядковых номеров даты. Поэтому, как ни меняйте формат ячейки, как была дата — 19.02.1861, такой и останется.
Как в Excel хранится время
Excel сохраняет время, используя тот же формат порядковой нумерации, что и для дат. День у Excel «начинается», как и у нас с вами в полночь. Каждый час составляет 1/24 дня, поэтому он представлен в виде десятичного значения: 0,041666…
Если Вы хотите узнать, что «видит» Excel, когда вы записываете в ячейку 9:00 утра, то умножьте: 0,041666 на 9.
Одна минута в Excel представлена числом: 1 / (24 * 60) = 1/1440 = 0,000694.
Секунда составляет 1 /(24 * 60 * 60 )= 1/86400 = 0,00001157407…
Поэтому если вы запишете в ячейку 8:30 утра, Excel совершит следующий расчет:
(8 * (1/24)) + (30 * (1/1440)) = 0,354167
Так что, если вы опаздываете на работу на полчаса и хотите сообщить об этом своему боссу, напишите ему / ей и скажите, что будете на месте через 0,02083333….
Если же время указано вместе с датой, то проблем не возникнет. Так 11:00 20 августа 2020 года будет сохранено как 44063,46.
Функции, задающие дату и время
CЕГОДНЯ()
Функция СЕГОДНЯ всегда возвращает текущую дату в формате даты:
ТДАТА()
Аналогичная функция всегда возвращает текущую дату и время:
ДАТАМЕС () и КОНМЕСЯЦА ()
Функция ДАТАМЕС возвращает порядковый номер даты, отстоящей на заданное количество месяцев вперед или назад от заданной даты.
Функция КОНМЕСЯЦА возвращает порядковый номер последнего дня месяца, отстоящего на указанное количество месяцев от даты, указанной в аргументе.
Аргумент число_месяцев может быть любым числом, но будет использоваться только целочисленное значение (например, если Вы напишете 2,8, то программа округлит до 2).
РАБДЕНЬ()
Иногда может быть полезно рассчитывать дату отстоящую от некоторой даты на несколько рабочих дней вперед или назад. Нам часто говорят: «Посылка придёт через 10 рабочих дней». Вот и сиди-считай, когда ее нужно получать… В такой ситуации удобно использовать функцию Excel РАБДЕНЬ. Функция возвращает порядковый номер даты. Поэтому, если вам нужна дата в формате: ДД.ММ.ГГГГ, то ячейку придется отформатировать.
В качестве аргументов функции выступают: начальная дата (от которой считаем); количество рабочих дней; праздники (если таковые имеются, то указываются даты праздников).
Если нужно найти дату, следующую за известной нам, то количество дней указывается целым положительным числом, как на рисунке выше. Если нужно найти предыдущие даты, то – целым отрицательным.
Аргумент [праздники] не является обязательным. Но если таковые имеются, то нужно указывать дату праздника. Если такой день один, то его можно указать непосредственно в формуле в кавычках. Если же праздники выпадают на несколько дней, например, на 21.08.2020 и 25.08.2020, то эти даты нужно внести в отдельные ячейки и в качестве параметра указать диапазон этих ячеек.
РАБДЕНЬ.МЕЖД ()
Для более новых версий Excel (начиная с 2010) существует еще одна версия предыдущей формулы: РАБДЕНЬ.МЕЖД. Она тоже возвращает порядковый номер даты, отстоящей вперед или назад на заданное количество рабочих дней. Но РАБДЕНЬ.МЕЖД дает возможность настраивать дни, которые являются выходными.
= РАБДЕНЬ.МЕЖД (нач_дата, число_дней, [выходные], [праздники])
Предположим, человек работает в понедельник, среду и пятницу, а вторник и четверг у него выходные. При этом праздники те же, что и в предыдущем примере.
Параметры: нач_дата, число_дней, и [праздники] те же, что и в примере работы функции РАБДЕНЬ.
Основная «фишка» этой функции — моделирование любого графика.
В качестве параметра [выходные] могут выступать следующие значения:
Если Вы не нашли подходящего режима в предложенной таблице, то можете задать любой график с помощью строки из семи цифр (0 или 1). 0 – рабочий день, 1- выходной. Каждое значение соответствует дню недели.
Так, если человек работает только в понедельник, среду и пятницу, то его рабочий режим можно описать следующей строкой: «0101011».
Функции извлечения дат
ДЕНЬ(), МЕСЯЦ() и ГОД()
Все предыдущие функции в качестве результата возвращают, как мы уже упомянули, порядковый номер даты. Но эти номера не позволяют легко извлечь годы, месяцы и дни, не говоря уже о часах, минутах и секундах.
Для этого в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД. Синтаксис прост:
Функции извлечения времени
ЧАС(), МИНУТА() и СЕКУНДА()
Данные функции аналогичны функциям извлечения дат.
Посмотрим на примере:
Дополнительные функции извлечения даты
ДЕНЬНЕД() и НОМНЕДЕЛИ()
Довольно часто, бывает так: нам говорят: «Давайте встретимся 24.08.2020!». Многие люди не могут по дате сориентировать: свободны они или нет. У нас так условно «заведено» — мыслить днями недели. Для нас понедельник, например, (какая бы дата ни была) – день тяжелый, а вот пятница – «проказница».
Иногда полезно найти зависимость продаж от дней недели. Как это сделать?! Не станешь же все даты размечать.
На такой случай есть полезная функция: ДЕНЬНЕД(). Эта функция выдает в качестве результата число от 1 до 7, которое соответствует дню недели. Синтаксис очень прост:
= ДЕНЬНЕД (дата_в_числовом_формате, [тип])
Параметр [тип] показывает, с какого числа начинается неделя и может принимать следующие значения:
Замечание! Кто внимательно смотрел, те заметили, что варианты 1 и 17; 2 и 11 – одинаковые. Действительно, функционально эти значения одинаковы. Но первые значения предназначены для совместимости с более ранними версиями Excel.
По умолчанию параметр [тип] принимает значение 1 (то есть неделя начинается с воскресенья). Но в России первым днем недели принято считать понедельник, поэтому для расчетов мы будет использовать значение параметра [тип] = 2.
Функция НОМНЕДЕЛИ() возвращает, как понятно из названия, номер недели, на которую приходится конкретная дата.
Синтаксис совпадает с предыдущей формулой.
Рассмотрим работу функции на примере:
Видно, что 20 августа 2020 года – это четвертый день недели (четверг) и выпадает на 34 неделю с начала года.
Значение параметра [тип_возвр] принимает фиксированные значения из списка:
Подсчет и отслеживание дат
Как мы уже говорили, благодаря тому, что Excel хранит даты в виде чисел, с датами можно делать операции сложения и вычитания. Операцию сложения иллюстрировали функции РАБДЕНЬ и РАБДЕНЬ.МЕЖДУ. Рассмотрим функции, позволяющие вычитать одну дату из другой.
ДНИ()
Самая простая функция. Возвращает число, соответствующее количеству дней между датами. При этом результат может быть и отрицательным, если начальная дата больше конечной.
ЧИСТРАБДНИ() и ЧИСТРАБДНИ.МЕЖДУ()
ЧИСТРАБДНИ – функция рассчитывающая количество рабочих дней при пятидневной рабочей неделе. Синтаксис формулы следующий:
= ЧИСТРАБДНИ (нач_дата, кон_дата, [праздники])
Аргументы нач_дата и кон_дата могут быть любыми датами в кавычках или ссылками на ячейки.
Параметр [праздники], как и в функции РАБДЕНЬ является необязательным, но позволяет дисквалифицировать определенные дни из расчета.
ЧИСТРАБДНИ.МЕЖДУ используются в более новых версиях Excel (2010 и более поздних версий). По аналогии с РАБДЕНЬ.МЕЖДУ функция дает возможность настраивать режим «выходных» дней.
Синтаксис ЧИСТРАБДНИ.МЕЖДУ() следующий:
= ЧИСТРАБДНИ.МЕЖДУ (нач_дата, кон_дата, [выходные], [праздники])
Аргументы нач_дата, кон_дата и [праздники] работают точно так же, как и в ЧИСТРАБДНИ().
[Выходные] могут принимать следующие значения:
ДОЛЯГОДА ()
Иногда полезно измерить, какова разница между двумя датами относительно целого года.
Давайте проведем грубые расчеты того, сколько россияне находятся в «нетрезвом» состоянии на новогодних праздниках относительно всего года. Воспользуемся функцией ДОЛЯГОДА():
=ДОЛЯГОДА нач_дат, кон_дата, [базис])
Первые аргументымогут быть любыми датами или ссылками на ячейки.
[базис] является необязательным аргументом, он позволяет указать «правила» для измерения разницы. В большинстве случаев используется вариант 1:
0.02(2) года… Какая маленькая цифра. А еще говорят, что у нас самый «загульный» народ. А мы опровергли ложные домыслы!
Преобразование даты и времени из текста
ДАТАЗНАЧ () и ВРЕМЯЗНАЧ ()
Все вышеперечисленные функции отлично работают с порядковыми номерами в формате даты в Excel. К сожалению, даты и время часто импортируются на листы в виде текста. Чтобы перевести текстовые строки в формат даты и времени используют функции ДАТАЗНАЧ() и ВРЕМЯЗНАЧ(). Синтаксис для каждого следующий:
= ДАТАЗНАЧ (дата_как_текст)
= ВРЕМЯЗНАЧ (время_как_текст)
Аргументы функций могутпринимать любую текстовую строку, которая выглядит как дата или время.
Примеры работы функций:
Будьте внимательны!
Одна из проблем, с которой часто сталкиваются пользователи Excel, заключается в том, что программа иногда неверно интерпретирует текстовые поля как даты. Пример здесь:
Видите, что разное положение цифры 20 влияет на исходный результат. Поэтому будьте осторожны при вводе дат, особенно если Вы импортируете данные из других источников. Чтобы не получилось, как в моей истории в начале статьи))
Помните: время очень ценный ресурс. И каждая секунда на счету. Excel знает об этом все. Поэтому будьте внимательнее и не теряйте время!
Научитесь использовать все прикладные инструменты из функционала MS Excel.