Evolcom.ru

Бытовая техника
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как получить месяц из номера недели и года в Excel?

Работа с датой и временем в Excel

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

Дата 4 июня 2019 года находится в ячейке B2. Возьмите Year из D2 и поместите его в другую ячейку:

  1. Поставить курсор в ячейку D2.
  2. Кликнуть на значке формулы .
  3. В появившемся окне «Вставка функции» в поле «Категория» выбрать Дата и время, а в поле «Выберите функцию» выделить ГОД. Нажать [OK].

Теперь в ячейке D2 будет храниться значение 2019. При изменении года в дате в ячейке В2 автоматически изменится год в ячейке D2.
Аналогичным образом поступать в случаях, когда из ячейки B2 необходимо вычленить месяц, номер недели с начала года или день месяца. Соответственно в п.3 надо будет выбирать функцию не ГОД, а МЕСЯЦ, НОМНЕДЕЛИ или ДЕНЬ.

Шаг 3: Если в ячейке хранится время и только время, необходимо выбрать ЧАСЫ или МИНУТЫ, чтобы внести эту информацию в отдельную ячейку.

Вычисление разницы между датами

Для этого используется математическая операция «-» (минус) и функция DATEDIF.

При вычитании знак минус или плюс должен быть окружен пробелами. Дни используются для вычисления разницы между двумя датами. Если в дате есть время, то оно выражается в дробных днях. Ошибки нет, если вы вычитаете большую дату из меньшей — результат будет иметь знак минус:

А вот в применении функции РАЗНДАТ есть нюансы.
Первая загвоздка в том, что эту функцию вы не найдете в Мастере функций после нажатия на кнопку . Но ее возможно вписать в поле значения ячейки вручную. Синтаксис функции:

= ОТПРАВИТЬ(Начальная дата; Конечная дата; Единица измерения)

Начальная_дата – это меньшая дата;
Конечная_дата – это бОльшая дата;
Единица_измерения – этот аргумент определяет в каких временных единицах выводить разницу дат:

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

Вычисление разницы во времени

Для этой задачи существует математическая операция «-» (минус), а к разности значений ячеек применяются функции HOURS, MINUTES, SECONDS.

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

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

Практические примеры

1. Проверка превышения запланированного времени доставки груза

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

В ячейку E2 из столбца E мы выведем разницу между разгрузкой и погрузкой:

Ячейка F2 должна содержать формулу для использования IF:

В формуле мы видим эту логику:

  • Записывается фактическая разница между сбросом и нагрузкой;
  • Эта разница сравнивается со стандартом;
  • Если разница больше стандарта, пишется «Поздно»;
  • Если разница меньше или равна стандарту, пишется «Стандарт».

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

То формула в каждой последующей ячейке будет автоматически преобразована в формулу с адресами ячеек в соответствующей строке. Проделайте то же самое с ячейкой E2 — умножьте ее на столбец E.

2. Красивое представление даты и времени в одной ячейке

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

В ячейке Е9 это выглядит следующим образом:

=СЦЕПИТЬ(ТЕКСТ(C9-B9;»дд»); » дн. «; ТЕКСТ(C9-B9;»чч»); » час. «; ТЕКСТ(C9-B9;»мм»); » мин.»)

Здесь функция ТЕКСТ переводит дату в число дней, или часов, или минут. Во что преобразовывать, зависит от второго параметра функции – «дд», «чч» и «мм» соответственно. А функция СЦЕПИТЬ склеивает наши числа и тексты в одну строку.
После размножения формулы по столбцу E получаем красивый результат:

Маленькие хитрости

Для быстрого ввода текущей даты в ячейку используйте сочетание горячих клавиш Ctrl+Z.

Чтобы внеси в ячейку номер дня недели надо воспользоваться функцией ДЕНЬНЕД, для которой есть дополнительный параметр «Тип» – он влияет на начало отсчета номера дня недели. У нас принято начинать отсчет с понедельника, этому типу соответствует значение 2. Таким образом, функция по отображению в какой-то ячейке дня недели из даты 4 июня 2019 г., хранящейся в ячейке B2 будет выглядеть так: =ДЕНЬНЕД(B2;2)
Результатом срабатывания формулы будет 2, т.е., вторник.

Как соотнести заданную дату с номером недели в Excel

Для вставки текущей даты в MS Excel существует комбинация клавиш, а именно Shift+Ctrl+4. Нажмите эту комбинацию клавиш, когда вы находитесь в ячейке, в которую хотите ввести дату. Shift+Ctrl+6 отобразит текущее время.

Как в Excel автоматически проставить даты в таблице?

Использование маркера с заливкой

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

Как выделить выходные дни в Excel?

Как выделить цветом выходные дни в Excel

  1. Выделите область ячеек с датами и проставленными часами работы. …
  2. Нажмите на ленте Главная – Стили – Условное форматирование – Создать правило
  3. В открывшемся окне выберите пункт «Использовать формулу для определения форматируемых ячеек»
  4. В нижней части этого окна запишите формулу: =ИЛИ(ДЕНЬНЕД(B$2)=1;ДЕНЬНЕД(B$2)=7)

Как сделать месяц из даты в Excel?

Чтобы добавить, например, 5 месяцев к дате 28.02.2011 в ячейке A1, можно использовать следующую формулу: =DATE(ГОД(A1);МЕСЯЦ(A1)+5;ДЕНЬ (A1)).

Сколько недель в 2020?

В 2020 году 52 недели. Чтобы рассчитать, сколько недель будет в 2020 году, разделите количество дней в году на количество дней в неделе. Разделите 366 дней на 7, чтобы получить 52,29 дня в 2020 году (високосный год).

Как определить номер недели в году?

Чтобы посчитать количество недель в году нужно количество дней в году разделить на количество дней в неделе. В обычном году — 365 дней, в високосном — 366. Обычный год: 365 / 7 = 52,14 — 52 недели и 1 день (52*7=364, 365-364=1).

Как посчитать количество дней между датами в Excel?

Есть ли способ в Excel подсчитать дни между двумя датами?

  1. На чистом листе бумаги введите текущую дату в ячейку A1, нажав CTRL+ ;.
  2. В ячейке A2 введите промежуточный период в днях, например, 127.
  3. В ячейку A3 введите формулу:=A1+A2.

Как в Excel к дате прибавить календарные дни?

Затем нажмите RETURN в ячейке C1. Эта формула добавляет 30 дней к дате в ячейке A1. Нажмите клавишу RETURN в ячейке D1. Эта формула вычитает 15 дней из даты в ячейке C1.

Как сделать столбец с датами в Excel?

Как ввести даты и время в Excel

  1. 16:45.
  2. 16:45:30.
  3. Чтобы ввести сегодняшнюю дату в текущую ячейку, можно воспользоваться сочетанием клавиш Ctrl+Z (или CTRL+SHIFT+4, если у вас другой язык системы по умолчанию).

Что значит на текущую дату?

Текущая дата/настоящее время — это

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

Developing.ru

Как с помощью ФОРМУЛЫ получить в любой из ячеек на основании данных ячейки 1 и ячейки 2 — дату — первый день указанной недели указанного года?
Решал ли кто-либо такую задачу?

  • Ссылка.

Предположим, что год находится в A1, а неделя в B1, то

Возвращает дату первого дня недели, указанной в B1, если первая полная неделя года указана в A1.

  • Упоминание.

Genyaa,
по-моему, первая неделя года, это та, в которой присутствует день 1 января сего года
Очень часто 1 неделя года также является 53 неделей предыдущего года (это когда 1 января не понедельник)

я погонял вашу формулу, она абсолютно верно считает !
без всяких оговорок «если» по поводу первой полной недели

Вы вычисляете дату понедельника (т.е. первого дня недели) 1-ой недели года,
а затем смещаетесь от этой даты на (количество недель*7) дней для получения требуемого результата

  • Цитирования.
  • Цитирую.

Функция NOMNEWS не мыслит подобным образом

вот еще одно определение для ГОСТа.
взято отсюда — http://forum.developing.ru/showthread.p . #post24551
Первой неделей считается неделя, содержащая первый четверг в новом году.

  • Цитата

ГОСТ ИСО 8601-2001
Заглавие на русском языке:
Система стандартов по информации, библиотечному и издательскому делу. Представление дат и времени. Общие требования

Статус: Действует
Дата ввода в действие: 01.07.2002
Взамен ГОСТ 7.64-90
ГОСТ ИСО 8601-2001

.
———————————————————————
2.16 неделя: Период времени, состоящий из семи дней.
2.17 неделя календарная: Период времени, состоящий из семи дней внутри календарного года.

Неделя начинается с понедельника и идентифицируется своим порядковым номером в году. Первой календарной неделей года считают первую неделю, содержащую первый четверг текущего года. В григорианском календаре — это неделя, содержащая 4 января.
———————————————————————
3.3.3 В календарном году календарная дата имеет порядковый номер.
Первой календарной неделей считают неделю, которая содержит первый четверг этого года.
Последней календарной неделей календарного года считают неделю, которая предшествует первой календарной неделе следующего года.

3.3.4 Один календарный год содержит 52 или 53 календарные недели.
3.3.5 Первая календарная неделя года может включать до трех дней предыдущего календарного года. Последняя календарная неделя календарного года может включать до трех дней следующего года.
3.3.6 Период времени, представленный датами недели календарного года, не совпадает с периодом времени, представленным календарными датами или порядковыми датами того же года.
3.3.7 Первой календарной неделей считают неделю, которая содержит дату 4 января.
———————————————————————

голоса
Рейтинг статьи
Читайте так же:
Как правильно суммировать числа с единицами в диапазоне в Excel?
Ссылка на основную публикацию
Adblock
detector