Формат времени в excel

Как рассчитать и отобразить отрицательное время в Excel

При расчете разницы во времени в Excel вы иногда можете получить результат в виде ошибки ######, если вдруг она представляет собой отрицательное число. Это происходит, если время начала больше, чем время окончания события. Такое часто случается, если рассчитывают продолжительность каких-то событий без учета даты. Получается, что время окончания меньше, чем время начала, поскольку это просто время следующего дня. К примеру, мы начинаем работу в 17 часов и заканчиваем на следующий день в 10 часов.

Но есть ли способ правильно отображать отрицательное время в Excel? Конечно, способ есть, и даже не один.

Способ 1. Измените систему дат Excel на систему дат 1904 года.

Самый быстрый и простой способ нормально отображать отрицательное время (со знаком минус) — это перейти на систему дат 1904 года. Для этого нажмите « Файл» > «Параметры» > «Дополнительно », прокрутите вниз до раздела « При расчете этой книги» и поставьте галочку в поле «Использовать систему дат 1904».

Нажмите  , чтобы сохранить новые настройки, и теперь отрицательные величины времени будут отображаться правильно, как отрицательные числа:

Как видите, время начала больше, чем время окончания, и результат получен со знаком «минус».

Способ 2. Рассчитать отрицательное время в Excel с помощью формул

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

=ЕСЛИ(A2-B2>0; A2-B2; «-» & ТЕКСТ(ABS(A2-B2);»ч:мм»))

=ЕСЛИ(A2-B2>0; A2-B2; ТЕКСТ(ABS(A2-B2);»-ч:мм»))

Они обе проверяют, является ли отрицательной разница во времени (A2-B2). Если она меньше нуля, первая формула вычисляет абсолютное значение (без учёта знака) и объединяет этот результат со знаком «минус». Вторая формула делает точно такой же расчет, но использует отрицательный формат времени «-ч::мм «.

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

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

На случай если время окончания меньше, чем время начала, мы можем проверить это при помощи функции ЕСЛИ.

=ЕСЛИ(B2<A2;B2+1;B2)-A2

Если разница во времени отрицательная, ко времени окончания добавляем 1 день (24 часа).  В результате получим правильную разницу во времени, но без знака «минус».

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

• начальная дата;
• конечная дата;
• код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

• для отображения результата в днях – «d»;
• в месяцах – «m»;
• в годах – «y»;
• в месяцах без учета лет – «ym»;
• в днях без учета месяцев и лет – «md»;
• в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

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

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

Как представляется дата в Excel

Обработка информации о дате осуществляется, как о количестве суток с 0 января 1900 года. Да, вы не ошиблись. Действительно, с нулевого числа. Но это необходимо для того, чтобы была точка отсчета, чтобы уже 1 января считалось цифрой 1 и так далее. Максимально поддерживаемое значение, обозначающее дату – 2958465, что в свою очередь являет собой 31 декабря 9999 года.

Этот метод дает возможность использовать даты для расчетов и формул. Так, Excel дает возможность определить количество суток между датами. Схема проста: из одного числа вычитается второе, а потом полученное значение переводится в формат даты.

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

Чтобы определить количество дней, которое прошло от даты А до даты B, необходимо от последней отнять первую. В нашем случае это формула =B3-B2. После ее ввода результат оказывается следующий.

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

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

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

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

Добавление второй оси

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

Сначала строим график так, будто у нас одинаковые единицы измерения.

Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».

Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.

Это один из способов. Есть и другой – изменение типа диаграммы.

Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».

Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.

Всего несколько нажатий – дополнительная ось для другого типа измерений готова.

Мастер преобразования текста в столбцы — не требующий формул способ преобразования текста в дату.

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

Пример 1. Преобразование простых текстовых строк в даты.

В этом примере мы будем превращать текстовые строки, например 01 01 2015 (день, месяц и год разделены пробелами), в даты.

1. На рабочем листе выберите столбец, содержимое которого вы хотите конвертировать.
2. Перейдите на вкладку «Данные», и нажмите «Текст по столбцам».

1. На шаге 1 мастера преобразования текста в столбцы выберите «С разделителями» и нажмите «Далее».
2. На шаге 2 мастера снимите флажки со всех разделителей и нажмите Далее.
3. На последнем шаге выберите «Дата» в разделе «Формат данных столбца», укажите в выпадающем списке вид, соответствующий вашим данным.  Нажмите «Готово».

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

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

Заметка. Для правильной работы мастера «Текст по столбцам» все записи должны быть единообразными. Например, если некоторые из них выглядят как день / месяц / год , а другие — месяц / день / год , вы получите неверные результаты.

Пример 2. Преобразование сложных текстовых строк в даты.

Если ваши записи представлены более сложной фразой из нескольких частей, к примеру:

• 8 март 2020 г., воскресенье
• 8 март 2020 г. 15:00

Вам нужно будет приложить немного больше усилий и использовать рассматриваемый нами инструмент в комбинации с функцией ДАТА.

Важно! Название месяца при использовании этого метода должно быть в именительном падеже!

1. Выберите нужную область данных.
2. Вызовите мастер преобразования.
3. На шаге 1 укажите «С разделителями» и нажмите « Далее» .
4. На шаге 2 мастера выберите разделители, которые у вас используются.

Например, если вы преобразуете строки, разделенные запятыми и пробелами, например «8 март 2020г., воскресенье», то вам следует выбрать оба разделителя — запятую и пробел.

Также имеет смысл выбрать опцию «Считать последовательные разделители одним», чтобы игнорировать лишние пробелы или пробел после запятой, если они есть в ваших данных.

Я немного усложнил наш пример, добавив после порядкового номера года еще букву «г» с точкой. Поэтому эта буква и добавлена в список разделителей.

И, наконец, взгляните на окно Образец разбора данных и проверьте, правильно ли поделены ваши слова на столбцы. Затем нажмите «Далее».

1. На шаге 3 мастера убедитесь, что все колонки в разделе предварительного просмотра данных имеют формат «Общий». Если это не так, щелкните столбец и выберите Общий в разделе Формат данных.

Заметка. Не выбирайте формат «Дата», поскольку каждый столбец содержит только ее часть. Поэтому Excel не сможет понять, что вы на самом деле хотите сделать.

Если вам не нужен какой-то из столбцов, щелкните по нему и выберите «Пропустить

Как видите, я буду использовать только три первых столбца, а два последних не нужны.

Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выбираем ячейку B2.

Когда закончите, нажмите кнопку Готово.Как вы видите на скриншоте выше, мы пропускаем четвертую и пятую колонки с точкой и днями недели, разделяем остальные данные на 3 столбца (в общем формате) и вставляем их, начиная с ячейки В2.

На следующем рисунке показан результат с исходными данными в A и разделенными на части данными в В, C и D.

1. Наконец, вы должны объединить эти кусочки вместе, используя функцию ДАТА (DATE). Синтаксис её не требует особых пояснений:

В нашем случае год записан в колонке D и день — в В, с этим проблем нет.

Но не так просто с месяцем, потому что это слово, а нужен его номер. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ (MONTH), которая может изменить название месяца на его порядковый номер:

Чтобы функция МЕСЯЦ понимала, что она имеет дело с датой, мы выражаем это так:

Здесь C2 содержит название месяца, март в нашем случае. «1 &» добавляется для получения даты (1 март), чтобы формула могла превратить ее в соответствующий номер месяца. Повторюсь еще раз, что месяц должен быть здесь в именительном падеже, что не всегда удобно.

А теперь давайте встроим это выражение в качестве второго аргумента:

И вуаля, наши сложные комбинации слов и цифр успешно становятся датами:

Расчет времени при изменении даты (расчет и отображение отрицательного времени в Excel)

Рассмотренные до сих пор методы хорошо работают, если время окончания позже, чем время начала.

Но проблема возникает, когда время окончания меньше, чем время начала. Это часто случается, когда вы заполняете табели учета рабочего времени, где вы вводите только время, а не дату и время целиком.

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

Например, если вы начинаете свою работу в 18:00 вечером, а завершаете работу и отдыхаете в 9:00 утра.

Если вы работаете только со значениями времени, то вычитание времени начала из времени окончания даст вам отрицательное значение 9 часов (9–18).

И Excel не может обрабатывать отрицательные значения времени (и в этом отношении люди не могут, если вы не умеете путешествовать во времени).

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

К счастью, это очень просто исправить.

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

Как вы могли заметить, иногда время начала — вечером, а время окончания — утром (что означает, что это была ночная смена и день изменился).

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

= B2-A2Вот формула ЕСЛИ, которая определяет, является ли значение разницы во времени отрицательным или нет, а если оно отрицательное, оно возвращает правильный результат.= ЕСЛИ ((B2-A2) <0,1- (A2-B2), (B2-A2))Хотя это хорошо работает в большинстве случаев, этого все равно не хватает, если время начала и время окончания различаются более чем на 24 часа. Например, кто-то входит в систему в 9:00 в первый день и выходит из системы в 11:00 во второй день.

Поскольку это более 24 часов, невозможно узнать, вышел ли человек из системы через 2 часа или через 26 часов.

Хотя лучший способ решить эту проблему — убедиться, что записи включают дату, а также время, но если это просто время, с которым вы работаете, то приведенная выше формула должна решить большинство проблем ( учитывая, что вряд ли кто-то проработает больше 24 часов)

Как определить в Excel день недели по дате

Доброго времени суток друзья!

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

Определить день недели можно несколькими способами и в этой статье мы рассмотрим 3 варианта:

С помощью пользовательского формата

Очень простой способ определить день недели по дате, делается это следующим образом:

• выделяете диапазон ячеек, в которых хотите внести изменения в отображении даты;
• вызываете контекстное меню правой кнопкой мыши и выбираете пункт «Формат ячеек»;
• в появившемся окне выбираете вкладку «Число», в разделе «Числовые форматы» вам нужно выбрать пункт «(все форматы)» и в поле тип внести нужный вам формат даты.

Сами форматы данных можно разделить на 2 типа: полный и сокращенный.

• Полный формат обозначаеться полным названием дня недели или месяца (к примеру, среда, четверг, и т.д.), полный формат указывается «ДДДД» — для дней недели, «ММММ» — для месяцев.
• Сокращенный формат отображается только двумя буквами (к примеру, Ср, Чт, Пт, и т.д.) и для этого указывается формат «ДДД» — для дней недели, «МММ» — для месяцев.

С помощью функции ТЕКСТ

В этом случае мы будем использовать встроенную функцию ТЕКСТ, для того чтобы определить в Excel день недели по дате. Эта функция предназначена, чтобы форматировать число в ячейке и преобразовать его в текст. Используя эту функцию, вы первым значением указываете источник данных, а потом указываете формат преобразования данных. Формат вы указываете сокращенный или полный, как я описал выше в первом пункте.

Орфография формулы выглядит так:

=ТЕКСТ (А1;«дддд»), формула возвращает текстовое значение дня недели.

С помощью функции ДЕНЬНЕД

Третий вариант узнать день недели по дате, это с помощью функции ДЕНЬНЕД, которая относится к группе «Дата и время», там вы ее и найдете. Эта функция возвращает число от 1 до 7, что соответствуют дню недели. Формула выглядит так:

=ДЕНЬНЕД(А1;2), где:

• «А1 – это адрес ячейки где дата в числовом формате,
• «2 – это число которое определяет тип возвращаемого значения (существует 3 типа: 1 или отсутствует – возвращает число от 1 (воскресенье) до 7 (суббота), 2 – от 1 (понедельник) до 7 (воскресенье) и 3 – от 0 (понедельник) до 6 (воскресенье), нам привычней всего 2 тип).

Думаю вам станет интересной статья «Как определяется разница между двумя датами с помощью функции РАЗНДАТ» в ней вы найдете полезные советы и примеры при работе с датами.

А на этом у меня всё! Я очень надеюсь, что что тема как определить в Excel день недели по дате, была раскрыта и стала вам полезной . Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

Как вставить текущее время в Excel

Существует несколько способов вставки времени в Excel, какой из них использовать, зависит от того, хотите ли вы зафиксировать какой-то момент времени или иметь динамическое значение, которое автоматически обновляется, чтобы показать текущее время.

Как добавить неизменяемое значение времени

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

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

Добавьте сегодняшнюю дату и текущее время, используя функцию ТДАТА

Если вы хотите вставить текущую дату и время в качестве динамического значения, которое обновляется автоматически, используйте функцию Excel ТДАТА.

Формула настолько проста, насколько это возможно, и никаких аргументов не требуется:

=ТДАТА()

При использовании функции ТДАТА следует помнить о нескольких вещах:

• Функция ТДАТА извлекает время из системных часов вашего компьютера.
• Это одна из изменчивых функций, которая заставляет формулу пересчитываться каждый раз, когда рабочий лист повторно открывается или в нем изменяется хотя бы одно значение.
• Чтобы заставить функцию ТДАТА обновить значение даты и времени, нажмите либо для пересчета активного рабочего листа или для пересчета всех открытых книг.
• Чтобы показать только время, установите нужный вам формат времени, как описано выше.

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

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

Функция ЦЕЛОЕ используется для округления десятичного числа, возвращаемого ТДАТА(), до ближайшего целого. А затем вы из даты-времени вычитаете целую часть, представляющую сегодняшнюю дату, чтобы оставить только дробную часть, то есть текущее время.

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

Как добавить время с шагом часов / минут / секунд в Excel?

Предположим, вы инвестируете в акции и вам нужно каждые 20 минут регистрировать изменение цен определенной акции. Что бы вы сделали, чтобы быстро создать временной список с шагом в 20 минут? В этой статье показано несколько методов ее решения.

Добавьте время с шагом в часах / минутах / секундах с помощью формулы

Вы можете применить следующие формулы, чтобы добавить время с шагом часы / минуты / секунды в Excel.

1. Введите время начала в пустую ячейку (говорит A1), затем выберите следующие пустые ячейки, которые необходимо заполнить с шагом приращения. Здесь я выбираю A2: A15.

Для добавления времени с шагом 20 минут:

Введите формулу = A1 + ВРЕМЯ (0,20,0) в панель формул, а затем нажмите Ctrl + Enter ключ одновременно. Вы можете видеть, что время каждой ячейки добавляется с шагом 20 минут и сразу же отображается в выбранном диапазоне.

Для добавления времени с шагом 20 секунд:

Измените формулу выше на = A1 + ВРЕМЯ (0,0,20), а затем нажмите Ctrl + Enter ключи одновременно. Смотрите скриншот:

Для добавления времени с шагом 1 час:

Пожалуйста, измените формулу на = A1 + ВРЕМЯ (1,0,0), а затем нажмите Ctrl + Enter ключи одновременно. Смотрите скриншот:

Внимание: В формуле = A1 + ВРЕМЯ (0,20,0), первое число в скобках означает час, второе число — минуты, а последнее — секунды. Вы можете изменить их в зависимости от вашего приращения времени

Добавьте время с шагом часов / минут / секунд с помощью ручки заполнения

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

1. Введите время начала и время первого приращения в первых двух ячейках, говорит A1 и A2. Смотрите скриншот:

2. Выберите ячейки A1 и A2, затем перетащите маркер заполнения вниз, пока не заполнятся необходимые значения времени приращения. См. Снимок экрана:

Kutools for Excel собирает множество часто используемых формул. А его формулы добавления часов / минут / секунд к дате помогают быстро добавлять время с шагом в часы / минуты / секунды в Excel без запоминания формул.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку (A2) для вывода результата, затем щелкните Кутулс > Формула Помощник > Формула Помощник. Смотрите скриншот:

2. в Помощник по формулам диалоговое окно, настройте следующим образом.

• В разделе Выберите формулу выберите Добавить часы к дате.Tips: Вы можете проверить Фильтр поле введите ключевое слово, чтобы отфильтровать формулу сертификата по мере необходимости.
• Укажите ячейку времени, в которой нужно добавить час Дата Время коробка,
• Введите разницу во времени между этими двумя часовыми поясами в поле Число коробка,
• Нажмите OK кнопку.

Затем вы можете увидеть новое время с шагом в 1 час на основе вычисленного значения ячейки A1.

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

Вы можете применить формулу Добавить минуты к дате или Добавить секунды к дате, чтобы создать список времени с приращениями минут или секунд в Excel. Смотрите скриншот:

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Синтаксис

Аргументы

Обязательный аргумент. Значение аргумента год может содержать от одной до четырех цифр. Excel интерпретирует аргумент год в соответствии с используемой системой дат, используемой на вашем компьютере. По умолчанию в Microsoft Excel для Windows используется система дат 1900. Во избежание непредвиденных результатов мы рекомендуем использовать в качестве аргумента год четыре цифры. Например, значение «07» может интерпретироваться как «1907» или «2007». Четырехзначное обозначение года предотвращает возникновение путаницы.

• Если аргумент год находится в диапазоне от 0 (нуль) до 1899 (включительно), Excel для вычисления года прибавляет это значение к числу 1900. Например, формула возвращает 2 января 2008 (1900+108) года.
• Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, формула возвращает 2 января 2008 года.
• Если значение аргумента год меньше 0 или больше 9999, Microsoft Excel возвращает значение ошибки #ЧИСЛО!.

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

• Если значение аргумента месяц больше 12, аргумент месяц добавляет число месяцев к первому месяцу указанного года. Например, формула возвращает число, соответствующее 2 февраля 2009 года.
• Если значение аргумента месяц меньше 1, аргумент месяц вычитает значение этого числа месяцев, увеличенное на 1, из первого месяца указанного года. Например, формула возвращает число, соответствующее 2 сентября 2007 года.

Обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.

• Если значение аргумента день больше числа дней в указанном месяце, аргумент день добавляет это число дней к первому дню месяца. Например, формула возвращает число, соответствующее 4 февраля 2008 года.
• Если значение аргумента день меньше 1, аргумент день вычитает значение этого числа дней, увеличенное на 1, из первого дня указанного месяца. Например, формула возвращает число, соответствующее 16 декабря 2007 года.

Замечания

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. Дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней.

Функция ВРЕМЯ

Возвращает значение времени, составленное из отдельных частей, заданных числами.Синтаксис: =ВРЕМЯ(часы; минуты; секунды), где

• Часы – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 24.
• Минуты – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 60.
• Секунды – аналогично минутам.

Функция ВРЕМЯ не может возвращать значение от 24 и выше, т.е. если для аргумента «Часы» задать число 25, то результатом будет 1 час.

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

Как перевести секунды в часы, минуты и секунды

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

Давайте рассмотрим небольшой пример.

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

Вот как это будет:

Дни:

=ЦЕЛОЕ(A2/(60*60*24))

Часы:

=ЦЕЛОЕ(A2/(60*60)) — ЦЕЛОЕ(A2/(60*60*24))*24

Минуты:

=ЦЕЛОЕ(A2/60) — ЦЕЛОЕ(A2/(60*60))*60

Секунды – это просто две последние цифры:

=—ПРАВСИМВ(A2;2)

Если нужно рассчитать время только в часах, минутах и секундах, то изменим формулу подсчета часов:

=ЦЕЛОЕ(A2/(60*60))

Конечно, здесь результат может быть больше 24.

Минуты и секунды подсчитываем, как и прежде.

Еще один вариант перевода секунд в дни, часы, минуты и секунды вы можете посмотреть на скриншоте ниже.

Как выбрать дату на текущий месяц во всех ячейках таблицы Excel

​ и номера месяцев​В поле ввода введите​Результат выделения цветом каждого​ If Next cell​Колонтитулы позволяют нам не​ для установки текущего​ формула.​Вставить даты в​ можно установить несколькими​ в сочетании с​и формат даты.​вычитает значение этого​находится в диапазоне​ вам полезна. Просим​ на ноутбуке (ПК).​устанавливает в ячейке​Чтобы вставить время, введите​ и их номера​ формулу: =МЕСЯЦ(B2)=МЕСЯЦ($C$2) и​ вторника в списке​ End Sub​

Поиск даты в диапазоне дат Excel

​ только устанавливать даты​ времени. Недостаток –​=ДАТА(ГОД(D20)+1;МЕСЯЦ(D20);ДЕНЬ(D20))​Excel с определенным периодом​ способами — вставить​ функциями ЛЕВСИМВ, ПСТР​Дополнительные сведения о форматировании​ числа дней, увеличенное​ от 1900 до​ вас уделить пару​

1. ​Как обновить текущую дату​ текущую дату.​ его и щелкните​
2. ​ годов.​ нажмите на кнопку​
3. ​ дат поставок товаров:​При необходимости измените «чувствительный»​ и нумерации страниц.​ в данном способе​Получились такие даты.​.​ текущую дату, заполнить​ и ПРАВСИМВ.​ дат см. в​ на 1, из​ 9999 (включительно), Excel​

​ секунд и сообщить,​ и время в​Эти функции применяются​

​Главная​Вся формула достаточно просто​ «Формат», чтобы задать​В формуле мы использовали​ диапазон​ Так же можно​ не будет автоматически​Как автоматически заполнить список,​Первый вариант.​ таблицу датами с​Функция ДАТА формирует дату.​ статье Пользовательское форматирование​ первого дня указанного​ использует это значение​ помогла ли она​ Excel.​ в Excel, чтобы​>​ читается и ее​ желаемый цвет заливки​ функцию =ДЕНЬНЕД(), которая​»А2:А100″​ добавить место для​ обновляться значение ячейки​

​ смотрите в статье​Выделяем ячейку с​ определенным периодом, т.​=ДАТА(ЛЕВСИМВ(C2;4);ПСТР(C2;5;2);ПРАВСИМВ(C2;2))​ даты.​ месяца. Например, функция​ как год. Например,​ вам, с помощью​В Excel дата​

Выделения дат цветом только текущего месяца

​Числовой формат​ легко использовать для​ для ячеек. Например,​ возвращает номер дня​на свой собственный.​ подписи ответственного лица​ на текущие показатели,​ «Заполнить автоматически список​ датой. Нажимаем ПРАВОЙ​ д.​Функция ЛЕВСИМВ получает первые​Функцию ДАТА можно использовать​ ДАТА(2008;1;-15) возвращает число,​ функция ДАТА(2008;1;2) возвращает​ кнопок внизу страницы.​ и время автоматически​ в таблице, которая​>​ решения похожих задач​ зеленый. Нажмите ОК​

​ недели для исходной​ Если необходимо вставлять​ за отчет. Например,​ при открытии документа.​

1. ​ в Excel».​ мышкой на квадратик​Как вставить текущую дату​
2. ​ 4 знака слева​ для формирования даты​ соответствующее 16 декабря​
3. ​ 2 января 2008​ Для удобства также​ обновляется только при​ будет обновляться при​Время​ с помощью условного​ на всех открытых​ даты. В первом​ дату не в​

​ отредактируем теперь левую​ Но в некоторых​В Excel можно​

​ в правом нижнем​ в​ в ячейке C2.​ на основе даты​ 2007 года.​ года.​ приводим ссылку на​ открытии книги. Если​ открытии таблицы, или​.​ форматирования.​ окнах.​ аргументе функции указываем​ соседний столбец, а​ нижнюю часть страницы​ случаях данных недостаток​ сделать много разных​ углу и протягиваем​Excel​ При этом значение​ в другой ячейке.​Примечание:​Если значение аргумента​ оригинал (на английском​ нужно обновить дату​ для расчета дат,​

​Дата и время, обновляемые​Читайте так же: как​В результате визуально отчетливо​ ссылку на исходную​ правее — подставьте​

​ в области колонтитулов:​ является преимуществом.​ графиков. Рассмотрим еще​ на нужное количество​, смотрите в статье​ «2014» становится годом​ Например, с помощью​

​ В Excel даты хранятся​

​год​ языке) .​ или время в​ стажа, возраста, других​ при пересчете листа​

1. ​ посчитать разницу между​ видно дни текущего​ дату. Ссылка относительная​ в оператор​Таким образом, можно создавать​С помощью VBA макросов​ один вид диаграммы,​
2. ​ ячеек. Отпускаем мышку.​ «Текущая дата в​ преобразованной даты в​ функций ГОД, МЕСЯЦ​ в виде порядковых​меньше 0 или​С помощью функции ДАТА​

​ течение дня, то​ настроек таблицы.​ или открытии книги,​ датами в днях.​ месяца:​ так как будет​

​Offset(0,1)​ документы с удобным​ используя в коде​

exceltable.com>

​ которая будет сравнивать​

• Вычислить количество дней между датами в эксель
• Excel vba текущая дата
• Меняет число на дату эксель
• Количество дней между датами эксель
• Разница в датах эксель
• Эксель заменяет дату на число
• Эксель вместо числа ставит дату
• В эксель сравнить даты
• Эксель как вставить дату в
• В эксель число в дату
• В эксель текущее время
• Как в эксель прибавить к дате месяцы