Auditportal.ru

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

Как найти или получить квартал с заданной даты в Excel?

Функции даты и времени

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

Excel поддерживает две системы дат: 1900 и 1904. По умолчанию используется система дат 1900. Выберите вариант системы дат 1904 года на вкладке Вычисления в меню Вычисления после выбора команды Параметры в меню Сервис.

Существует таблица, в которой указаны первая и последняя даты каждой системы, а также их номера.

Система датПервая датаПоследняя дата
19001 января 1900 г. (значение 1)31 декабря 9999 г. (значение 2958465)
19042 января 1904 г. (значение 1)31 декабря 9999 г. (значение 2957003)

Функция ВРЕМЗНАЧ

В результате из аргумента time_as_text возвращается числовой формат времени суток. В числовом формате время суток представляет собой десятичную дробь в диапазоне от 0 до 0,99999999, то есть от 0:00:00 (12:00:00 ночью) до 23:59:59 (11:59:59 ночью). VREAMSNATCH преобразует текстовое время в числовое, преобразуя его в представление времени суток.

  • Time_as_text — текстовая строка, содержащая значение времени суток в любом формате, допустимом в Excel. Информация о дате в аргументе time_as_text игнорируется.

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

Это результат введенных аргументов в числовом формате. Это десятичная дробь в диапазоне от 0 до 0,99999999, представляющая время суток от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 ночи).

  • Часы — число от 1 до 24 (число часов);
  • Минуты — число от 1 до 59 (число минут);
  • Секунды — число от 1 до 59 (число секунд).

Функция ГОД

Результат: год, соответствующий введенному серийному номеру. Год определяется как целое число от 1900 до 9999.

  • Date_in_numeric_format — серийный номер. Этот аргумент может быть представлен в виде текста, например, «19/Mar/1955» или «19-3-55». Это автоматически преобразует текст в дату в числовом формате.

В панели Вычисления диалогового окна Параметры выбрана опция Система даты 1904, поэтому в качестве начальной даты используется 02/01/1904 вместо 01/01/1900.

Функция ДАТА

Результат: порядковый номер (от 0 до 2958456), соответствующий дате в числовом формате (за точку отсчета было взято 01 января 1900 года).

  • Год — число (год) от 1900 до 9999 (или от 0 до 8099 соответственно);
  • Месяц — номер месяца в году (если значение аргумента больше 12, число делится на 12, целое число прибавляется к указанному значению года, а остаток выступает в качестве номера месяца; например, функция DATA(98,14,2) возвращает числовой формат даты 2 февраля 1999 года);
  • Day — количество дней в месяце (если аргумент day больше количества дней в указанном месяце, алгоритм вычисления аналогичен описанному для аргумента month’; например, функция DATA(99,1,35) возвращает числовой формат даты 4 февраля 1999 года).
Читайте так же:
Как масштабировать все вкладки в один размер в Excel?

Функция ДАТАЗНАЧ

Результат: Порядковый номер, соответствующий дате, представленный в текстовом виде, Функция DATASNACH используется для преобразования даты из текстового представления в числовой формат.

  • Date_as_text — Текст, содержащий дату в формате даты Excel. При использовании системы данных Excel по умолчанию для Windows 95/98 аргумент date_as_text должен представлять дату в диапазоне от 1 января 1900 года до 31 декабря 9999 года. При использовании системы данных Excel по умолчанию для Macintosh аргумент date_as_text должен представлять дату в диапазоне от 1 января 1904 года до 31 декабря 9999 года. Функция DATASNACH возвращает значение ошибки #DATE!, если значение аргумента date_as_text не находится в указанных диапазонах. Если год опущен в аргументе date_as_text, функция DATASNACH использует текущее значение года из встроенных часов компьютера. Информация о времени суток в аргументе date_as_text игнорируется.

Функция ДАТАМЕС

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

  • Nach_date — порядковый номер, соответствующий начальной дате;
  • Number_months — количество месяцев (может быть положительным или отрицательным).

Функция ДЕНЬ

Результат: Номер месяца, соответствующий указанной дате (целое число в диапазоне от 1 до 31).

  • Date_in_numeric_format — значение даты, определенное как порядковый номер от 0 до 65380 или как текст в стандартных форматах Excel, например, «29/12/63» или «15-01-01». В этом случае текст будет автоматически преобразован в дату в числовом формате.

Функция ДЕНЬНЕД

Результат: День недели, соответствующий заданному серийному номеру. День недели определяется как целое число от 1 (воскресенье) до 7 (суббота).

  • Дата_в_числовом_формате — порядковый номер; этот аргумент может быть задан в виде текста, например, «15/Jan/1999» или «04- . . . , 15-99. «
  • Type» — число (1, 2 или 3), определяющее тип подсчета недель (Sun=1 to Sat=7; Mon=1 to Sun=7 или Mon=0 to Sun=6 соответственно).

Функция ДНЕЙ360

Количество дней между двумя датами на основе 360-дневного года (12 30-дневных месяцев). Функция рассчитывает платежи, когда бухгалтерские операции основаны на 30-дневных месяцах.

  • Begin_date, end_date — две даты, количество дней между которыми вы хотите определить (аргументами могут быть текстовые строки с цифрами для указания месяца, дня и года (например, «30/01/99» или «30-01-99»), или даты в числовом формате; если аргумент begin_date превышает аргумент end_date, функция DAY360 возвращает отрицательное число) Этот аргумент может быть FALSE (американский метод; это значение по умолчанию) или TRUE (европейский метод).

Если вы хотите найти количество дней между двумя датами в обычном году, используйте обычное вычитание. Например, «31/12/99» минус «01/01/99» равно 364.

Функция ДОЛЯГОДА

Результат: часть года, соответствующая количеству дней между начальной и конечной датой (продолжительность года считается равной единице)

  • Start_date, end_date — порядковые номера для начальной и конечной даты
  • Base — число от 0 (принимается по умолчанию; указывает на стандарт США) до 5, указывающее на режим вычислений.
Читайте так же:
Как заставить пользователей сохранять как книгу с поддержкой макросов?

Функция КОНМЕСЯЦА

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

  • Nach_dat — порядковый номер, представляющий дату начала
  • Number_month — количество месяцев (может быть положительным или отрицательным).

Функция МЕСЯЦ

Результат: номер месяца, соответствующий введенному серийному номеру. Номер месяца определяется как целое число от 1 (январь) до 12 (декабрь).

  • Date_in_numeric_format — порядковый номер. Вы можете указать этот аргумент в виде текста, например, «15-4-1999» или «15-Jan-1999», а не в виде числа. Это автоматически преобразует текст в дату в числовом формате. См. функцию ГОД.

Функция МИНУТЫ

Количество минут (целое число от 0 до 59) для значения времени суток, представленного данным порядковым номером.

  • Date_in_numeric_format — серийный номер. Этот аргумент может быть указан в виде текста, например, «16:48:00» или «16:48:00 PM», а не в виде числа. В этом случае текст автоматически преобразуется в дату в числовом формате.

Функция НОМНЕДЕЛИ

Результат: Возвращает число, указывающее, на какую неделю года приходится указанная дата. Чтобы эта функция была доступна, необходимо установить надстройку Analysis Pack.

  • Date_in_numeric_format — дата в числовом формате;
  • Type — число, обозначающее первый день недели (1 — воскресенье, 2 — понедельник).

Функция РАБДЕНЬ

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

  • Nach_date — начальная дата как порядковый номер;
  • Number_days — количество дней;
  • Holidays — задает массив порядковых номеров, соответствующих праздникам.

Функция СЕГОДНЯ

Результат: Порядковый номер текущей даты.

Функция СЕКУНДЫ

Результат: количество секунд (целое число от 0 до 59) в значении времени, соответствующем указанному номеру пробега. Функция SECONDS используется для получения значения количества секунд времени суток, заданного датой, в числовом формате.

  • Date_in_numeric_format — серийный номер. Этот аргумент может быть указан в виде текста, например, «16:48:23» или «16:48:47 PM», а не в виде числа. Это автоматически преобразует текст в дату в числовом формате.

Функция ТДАТА

Серийный номер, соответствующий текущим дате и времени (внутренние часы операционной системы).

См. также функцию YEAR; результат функции обновляется только при новом расчете таблицы.

Функция ЧАС

Результаты: Количество часов, соответствующих данному серийному номеру. Определяет время между 0:00 (12:00 утра) и 23:00 (23:00 вечера).

Как найти или получить квартал с заданной даты в Excel?

Excel преобразовывает числа в даты. Причины и способы решения.

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

Читайте так же:
Как извлечь гиперссылки из нескольких изображений в Excel?

  1. Несоответствие регионального формата исходных данных региональным настройкам вашей операционной системы. В исходных данных в качестве десятичного разделителя используется точка (американский формат), а у вас – региональные настройки, в которых десятичным разделителем является запятая, а точка воспринимается как разделитель отдельных составляющих даты – день, месяц, год (например, 17.09.202.
  2. Особое поведение Excel для ячеек в «Общем» числовом формате (который установлен по умолчанию): если вводить в ячейку что-то похожее на число, то такое значение сохраняется как число, если вводить что-либо, похожее на дату – то такое значение сохраняется как дата. Если вводимое значение не похоже ни на число, ни на дату – то это текст.

В этом примере (выше) данные испорчены:

Исходные данные записывались в текстовой форме:

  • Ячейка D3 – исходное значение (179.3287) мало похоже на дату, так как номер месяца 179 не существует (а вот год 3287 возможен, в далеком будущем).
  • Ячейка D4 – исходное значение похоже на дату (июль 1275 года). Однако в Excel точкой отсчета дат является 1 января 1900 года. Поэтому число 1275 (ранее 1900 года) не воспринимается как год.

Необработанные данные были преобразованы в даты (и так потеряны):

  • Ячейка D5 – в исходном значении (7.1972) число до точки похоже на номер месяца 7 (июль), а число после точки – на год 1972 (на самом деле произошло преобразование в дату 01.07.1972)
  • Ячейка D6 – в исходном значении (11.2024) число до точки воспринимается как номер месяца (ноябрь), а после точки – год 2024 (произошло преобразование в дату 01.11.2024)

Как можно решить данную проблему?

Очевидно, что начало с самого драматичного не будет самым лучшим.

Способ-1. Поменять региональные настройки операционной системы (на американские).

Да, но это глобальное изменение затронет другие приложения и службы (не только Excel).

Способ-2. Локально вExcel(в параметрахExcel) заблаговременно изменить десятичный разделитель с запятой на точку:

Способ-3. Заблаговременно установить «текстовый» формат ячеек (вместо «общего»), в которые будут вставляться значения.

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

Или еще лучше — формулы :

=NUMBERVALUE(A3;".") (в диапазоне E3:E9)

Русская версия этих формул:

Способ-4. Формулой (без какой-либо предварительной подготовки):

Вы можете попытаться восстановить уже испорченные номера с помощью формулы

Формула в ячейке G3 :

Или русская версия :

=1*ЕСЛИ(ЯЧЕЙКА("формат";D3)="G"; ПОДСТАВИТЬ(D3;".";","); ТЕКСТ(D3;"М,ГГГГ"))

Формула действует следующим образом:

CELL() (ЯЧЕЙКА()) – функция из категории «Информационные». Если первый ее аргумент (тип информации) указан “format”, то результат работы функции — "G" — для текста или чисел и "D3" — для дат.

Читайте так же:
Как изменить несколько дат на день недели в Excel?

Функция SUBSTITUTE() заменяет точку запятой, а умножение на 1 в начале формулы (применение арифметической операции) преобразует текст, состоящий из цифр и запятой, в число.

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

Используя код формата в функции TEXT, нужно учитывать то, что и даже в англоязычной версии, в зависимости от региональных настроек, вместо “M,YYYY”, возможно, нужно указывать “М,ГГГГ” (где “M” – кириллическое).

ВАЖНО: Формула не является универсальным решением.

Это хорошо работает, если за точкой следует число с тремя или более цифрами.

А что если у нас есть это:

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

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

Если в первом случае можно было бы еще добавить проверку дополнительных условий в формуле, то в последнем – исходные значения безвозвратно утеряны и узнать какие они были – уже не представляется возможным. Очевидно, в таком случае, единственный вариант – это не допустить автоматического преобразования «как бы чисел» в даты, то есть указанный выше способ 3 – чуть ли не единственное решение (не считая кардинальных изменений региональных настроек).

Такие и подобные тонкости работы в Excel мы прорабатываем на курсе "Excel бизнес-анализ и прогнозирование"

Мы гарантируем, что после 27 часов изучения Excel у вас не останется вопросов, а если они возникнут, наши онлайн тренеры помогут вам.

Автор генеральный директор и DATAbi тренер Евгений Довженко

Excel формат даты-квартал и год

Можно ли изменить форматирование ячейки, чтобы квартал и год отображались как q yyyy, а не как dd-mm-yyy?

5 ответов

  • Какой лучший способ хранения квартала и года в SQL Server?

Как в базе данных должны храниться квартал и год? У меня есть таблица платежей, и мне нужно присвоить квартал/год, чтобы я мог легко определить, за какой квартал был платеж. Я думал добавить два целочисленных столбца к каждому платежу, добавить еще одну таблицу и добавить возможные варианты.

У меня есть разные даты в столбце. Например: 20080102 20070821 Я хочу преобразовать эти даты в календарные годы и кварталы. Например, год Квартал 2008 2008 2008-Q1 2008-Q1 2007 2007-Q3 Я могу получить первый столбец, используя: select left(date,4) as year from table Как я могу создать второй столбец?

Нет, кварталы не являются настраиваемым форматом

Читайте так же:
Как извлечь последнее число из текстовой строки в Excel?

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

  1. Вместо текущего значения
  2. , которое в этом случае уже не является датой

Вернет квартал (на основе календарного года) и год даты в A1, т.е. на сегодняшнюю дату декабрь 2011 года.

Этого вполне достаточно, если вам нужен только один номер блока.

Указать формат не представляется возможным.

Например, 15/6/2015 становится 2015-Q3.

Если поле даты пустое, эта формула выведет N/A вместо 1900-Q1

Попробуйте этот рецепт:

Для этого возьмите 31/12/2016 в ячейке AZ2 и измените на Q4-2016.

Похожие вопросы:

Мне доступен набор данных с форматом даты SAS 01JAN1980. Переменная квартала должна иметь формат 1980Q1 или некоторую комбинацию годов и кварталов. Вот что представляет собой мой код SAS.

У меня есть много дат в формате yyyy-mm-dd, и я хотел бы преобразовать их в формат yyy-q, где qq — это квартал года (поэтому месяцы 1, 2, 3 сопоставляются с q=1, а 4, 5, 6 — с q=2 и т.д.).

Я ищу плагин jquery для выбора года (не месяцев или дат) [я могу в основном использовать обычный выпадающий список для этого]. Семестр — Первый семестр и второй семестр (полугодие), например.

Каков наилучший способ хранения квартала и года в базе данных? У меня есть таблица платежей, и мне нужно назначить квартал/год, чтобы я мог легко увидеть, какому кварталу соответствует платеж. Я думал о: a).

У меня есть разные даты в столбце. Например: 20080102 20070821 Я хочу преобразовать эти даты в годы и календарные кварталы. Например, Год Квартал 2008 2008 2008 2008-Q1 2007 2007-Q3 Я могу получить первый столбец из.

В моей голове идет спор о том, как спроектировать квартал и финансовый год. В некоторых случаях: Для некоторых компаний квартал может меняться из года в год, например: 2011 Квартал 1 (Q1).

Чтобы преобразовать этот квартальный формат в MM/DD/YYYY в Excel, конвертируйте квартал в первый день квартала. Например, Q1-2014 до 1/1/2014, Q2-2015 до 4/1/2015, Q3-2016 до.

Я хочу отобразить переменную даты, и она должна отображаться в виде квартального года. Например, 1/6/19 должно быть: Q2-19 Я могу использовать формулу roundup(month), чтобы достичь этого, но мне нужно, чтобы это было в.

Вы хотите разделить квартал и год на отдельные колонки df.head() Period Q1/2012 Q2/2012 Q2/2012 Q3/2012 Q3/2012 Q4/2012 Q4/2012 Q1/2013 Вы хотите, чтобы колонка отображалась следующим образом Period Year Q1 2012 Q2 2012 Q2 2012 Q2 2012 Q3 2012.

В Excel я хочу вернуть правильный финансовый год и квартал для определенной даты. Например, мы начинаем финансовый год 1 апреля 2020 года.

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