Evolcom.ru

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

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

КАК: Правильно введите даты в Excel с помощью функции DATE — 2022

Работа с датами и временем в Microsoft Excel (Февраль 2022).

Table of Contents:

Обзор функции DATE

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

Например, если в ячейку рабочего листа ввести следующую функцию DATE

Возвращается серийный номер 42370, который относится к дате 1 января 2016 года.

Изменение серийных номеров на даты

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

Ввод сроков в качестве дат

С помощью DATE и других функций Excel можно создавать широкий спектр формул даты, как показано на изображении выше.

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

Синтаксис и аргументы функции DATE

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

Синтаксис функции ДАТА:

= DATE (год, месяц, день)

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

Месяц — (требуется) введите месяц года как целое положительное или отрицательное число от 1 до 12 (с января по декабрь) или введите ссылку на ячейку для расположения данных.

День — (требуется) введите день месяца как целое положительное или отрицательное число от 1 до 31 или введите ссылку на ячейку для размещения данных.

Заметки

  • Excel интерпретирует Год аргумент в соответствии с системой даты, которую использует ваш компьютер. По умолчанию Microsoft Excel для Windows использует систему даты 1900.
  • При использовании этой системы даты функция DATE не будет показывать даты до 1900 года.
  • Если значение, введенное для Год аргумент находится между 0 (ноль) и 1899, это значение добавляется к 1900 году для определения года. Например, DATE (0,1, 2) возвращается 2 января 1900 года (1900 + 0), а DATE (1899,12,31) возвращается 31 декабря 3799 (1900 + 1899). По этой причине в течение года должны вводиться четыре цифры, чтобы избежать ошибок, таких как «2016», а не «16».
  • Если год аргумент находится между 1900 и 9999 годами, это значение вводится как год для итоговой даты. Например, DATE (2016,1, 2) возвращается 2 января 2016 года.
  • Если год аргумент меньше 0 или больше 9999, Excel возвращает #NUM! значение ошибки.
  • Если значение, введенное для Месяц аргумент больше 12, значение делится на 12, а число полных лет добавляется к Год аргумент. Например, DATE (2016,15,1) возвращает дату 1 марта 2017 года (15 — 12 = 1 год + 3 месяца).
  • Если Месяц аргумент меньше 1, то количество месяцев вычитается с текущей даты, чтобы дать более раннюю дату. Например, DATE (2016, -5,1) возвращает дату 1 июля 2015 года.
  • Если значение, введенное для День аргумент больше, чем количество дней в указанном месяце, функция добавляет это количество дней в следующий месяц. Например, DATE (2016,1,35) возвращает серийный номер, представляющий 4 февраля 2016 года, который через четыре дня после 31 января 2016 года.
  • Если День аргумент меньше 1, День вычитает это количество дней, начиная с предыдущего месяца. Например, DATE (2016,1, -15) возвращает серийный номер, представляющий собой 16 декабря 2015 года, который составляет 15 дней до 31 декабря 2015 года.
Читайте так же:
Как переместить всю строку на другой лист на основе значения ячейки в Excel?

Пример функции DATE

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

В следующих формулах мы покажем один пример использования функции DATE. Формула в:

  • Строка 5 переходит на первый день текущего месяца;
  • Строка 6 преобразует текстовую строку (ячейка A5) в дату;
  • Строка 7 показывает день недели на данную дату;
  • Строка 8 считает дни между текущей и предыдущей датой;
  • Строка 9 преобразует номер юлианского дня (ячейка A9) в текущую дату;
  • Строка 10 преобразует текущую дату (ячейка A10) в юлианский день.

Ниже описаны шаги, используемые для ввода функции DATE в ячейку B4. В данном случае результатом функции является составная дата, сформированная путем объединения отдельных элементов даты в ячейках A2-C2.

Ввод функции DATE

Входные параметры функции и ее аргументы: :

  1. Введите всю функцию: = DATA (A2, B2, C2) в ячейку B4
  2. Выберите функцию и ее аргументы с помощью диалогового окна функции DATA.

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

Ниже описаны шаги по вводу функции DATE в ячейку B4 на изображении выше с помощью диалогового окна функции.

  1. Нажмите на ячейку B4, чтобы сделать ее активной ячейкой
  2. Нажми на Формулы вкладки меню ленты
  3. выберите Дата и время из ленты, чтобы открыть раскрывающийся список функций
  4. Нажмите на ДАТА в списке, чтобы открыть диалоговое окно функции
  5. Нажмите кнопку «Год» в диалоговом окне
  6. Нажмите ячейку A2, чтобы ввести ссылку на ячейку в качестве функции Год аргумент
  7. Нажмите на строку «Месяц»
  8. Нажмите ячейку B2, чтобы ввести ссылку на ячейку
  9. Нажмите на кнопку «День» в диалоговом окне
  10. Нажмите ячейку C2, чтобы ввести ссылку на ячейку
  11. Нажмите «ОК», чтобы закрыть диалоговое окно и вернуться на рабочий лист.
  12. Дата 11/15/2015 должна появиться в ячейке B4
  13. Когда вы нажимаете на ячейку B4, полная функция = ДАТА (А2, В2, С2) появляется в строке формул над листом
Читайте так же:
Как посчитать количество дней кроме воскресенья / выходных в Excel?

После ввода функции в ячейку B4 возможно, что формат ячейки неверен. Ниже перечислены шаги для изменения формата даты.

Изменение формата даты в Excel

Изменить формат ячейки, содержащей функцию DATE, очень просто — достаточно выбрать один из форматов по умолчанию, доступных в диалоговом окне Формат ячеек. Ctrl + 1 (цифра один) открывает диалоговое окно Формат ячеек, как показано ниже.

Для изменения формата даты

  1. Выделите ячейки на листе, которые содержат или будут содержать даты
  2. Нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек
  3. Нажмите Число в диалоговом окне
  4. Нажмите Дата в категории (левая сторона диалогового окна)
  5. В поле Тип (правая сторона) выберите нужный формат даты
  6. Если выбранные ячейки содержат данные, в окне Образец будет показан выбранный формат
  7. Нажмите OK, чтобы сохранить изменение формата и закрыть диалоговое окно.

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

  1. Щелкните правой кнопкой мыши по выделенным ячейкам, чтобы открыть контекстное меню.
  2. Выберите команду Формат ячеек…. из меню, чтобы открыть диалоговое окно Формат ячеек

###########

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

Юлийские номера

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

Вот пример номера юлианского дня в ячейке A9 — 2016007 — он состоит из семи цифр, первые четыре из которых обозначают год, а последние три — день года. Как показано в ячейке B9, это число представляет седьмой день 2016 года или 7 января 2016 года.

Читайте так же:
Как поменять местами знаки значений в ячейках в Excel?

Аналогично, число 2010345 обозначает 345-й день 2010 года, т.е. 11 декабря 2010 года.

Вычесть даты в Excel с помощью функции YEAR

Используйте функцию ГОД для вычитания дат в Excel. В этом учебном пособии показан пример использования функции DAY для вычитания двух дат в Excel. и две даты.

Как найти начальные и конечные даты проекта с помощью функции Excel WORKDAY

Как найти начальные и конечные даты проекта с помощью функции Excel WORKDAY

Используйте функцию WORKDAY в Excel для расчета дат начала или окончания проекта с учетом выбранного диапазона рабочих дней. Обновлено для работы с Excel 2019.

Ввод даты с помощью функции DATE в таблицах Google

Ввод даты с помощью функции DATE в таблицах Google

Узнайте все о функции DATES в Google Spreadsheets и о том, как правильно вводить даты в электронную таблицу или другие формулы дат в электронной таблице.

Excel условное форматирование даты до выходных / праздничных дней

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

Как написать функцию условного форматирования, чтобы окрасить ячейки с зарплатой в красный цвет и сделать это для всего года? Набор данных представляет собой календарные даты за весь год на одном листе. Я использую Excel 2016

1 ответ 1

День выплаты зарплаты может выпасть на один из восьми возможных дней:

  • 12-е (если 13-е — это Страстная пятница, потому что тогда 13-е, 14-е и 15-е будут трехдневными выходными)
  • 13 (если это последний рабочий день недели * )
  • 14-е (если это последний рабочий день недели * )
  • 15 (если это рабочий день * )
  • с 4-го по последний день месяца (если с 3-го по последний день — Страстная пятница, потому что тогда последние три дня месяца образуют трехдневные выходные)
  • с третьего по последний день месяца (если это последний рабочий день недели * )
  • 2-ой-последний день месяца (если это последний рабочий день недели * )
  • последний день месяца (если это рабочий день * )
Читайте так же:
Как подсчитать все ячейки со значением / данными в диапазоне в Excel?

__________
* Рабочий день — с понедельника по пятницу, за исключением Страстной пятницы.

В Excel 2013 WEEKDAY(date, 2) возвращает 1, если date — понедельник,…, 7, если date — воскресенье. Это указано как недавнее расширение; если это не сработает для вас, так и скажите, и мы можем обойти это. Используя эту WEEKDAY функцию, и EOMONTH функцию , которая сообщает нам в последний день месяца, мы можем перевести приведенный выше список к следующей формуле:

=AND(OR(AND(DAY(A1)=12,A1+1=F$1), AND(DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(DAY(A1)=15,WEEKDAY(A1,2)<=5), AND(EOMONTH(A1,0)-A1=3,A1+1=F$1), AND(EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5)), A1<>F$1)

Где дата Страстной пятницы (14 апреля 2017 года) (вручную) вводится в ячейку F1 .

Эта формула может быть грубо разложена в виде

=AND( OR( AND(something1), AND(something2), AND(something3), AND(something4), AND(something5), AND(something6) ), something7 )

  • something1 — это DAY(A1)=12,A1+1=F$1 , который проверяет первую пулю (четверг, 12-е число месяца, когда следующий день — Страстная пятница).
  • something2 — это DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1) , что проверяет вторую и третью пули (13-й или 14-е в пятницу или в день (четверг) до Страстной пятницы).
  • something3 — это DAY(A1)=15,WEEKDAY(A1,2)<=5) , который проверяет четвертую пулю (15-е число в будний день).
  • something4 — это EOMONTH(A1,0)-A1=3,A1+1=F$1 , что проверяет пятую пулю (с 4-го по последний день месяца, когда следующий день — Страстная пятница).
  • something5 — это EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1) , который проверяет наличие 6-я и 7-я марки (2-й или 3-й до последнего дня месяца, в пятницу или за день до Страстной пятницы).
  • something6 — это A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5 , что проверяет восьмую пулю (последний день месяца, в будний день).
  • something7 — это A1<>F$1 , что на сегодняшний день является Страстной пятницей и исключает ее. Без этого теста мы выделим (цвет / оттенок) четверг, 13 апреля, и пятницу, 14 апреля.
Читайте так же:
Как подсчитать и удалить дубликаты из списка в Excel?

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

  • Фактическая Страстная пятница этого года: 14 апреля, которая заставляет день выплаты жалованья быть в четверг, 13 апреля.
  • 13 января, что приведет к выплате в четверг, 12 января.
  • 15 сентября (или 15 декабря), что приведет к выплате в четверг 14-го числа.
  • Пятница, 28 апреля, что приведет к выплате в четверг, 27 апреля.
  • 29 сентября, что приведет к выплате в четверг, 28 сентября.
  • 31 марта (или 30 июня), что приведет к тому, что выплаты начнутся со второго по последний день этого месяца.

Примечание. В случае, если F1 не является пятницей, вы увидите некорректные результаты (например, более двух дней оплаты в месяц).

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector