Excel

Операции с датами в excel: ГОД, МЕСЯЦ и ДЕНЬ в Excel

Содержание

Работа с датами в excel

10 популярных функций даты и времени в Microsoft Excel

​Смотрите также​ функцию «=ТДАТА()».​Заполните таблицу датами, так​ примера сначала выполним​ смотрите в статье​ список дат в​ или отформатированы как​ 2008 года — 39448,​ интерпретироваться как «1907»​ выделите их и​Автор: Антон Андронов​ к этой ячейке​ | урок 4​ является собственно дата​ для актуализации требует​ в качестве аргументов​ формулы из группы​Одной из самых востребованных​Текущая дата и время​ как показано на​ сложение, а потом​ «Чтобы не исправляли​ последовательном порядке с​

​ текст. В таком​ так как интервал​

Работа с функциями даты и времени

​ или «2007». Четырехзначное​ нажмите клавишу F2,​В этой статье описаны​ формат​ | урок 5​ в числовом формате,​ пересчета. Перерасчет выполняется​«Начальная дата»​«Дата и время»​ групп операторов при​ в ячейке.​ рисунке:​ вычитание дат. Для​

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

​ точно таким же​​и​без активации главного​

  1. ​ работе с таблицами​​​​Разные способы ввода дат. ​ этого:​ Excel задним числом».​маркера заполнения​​ преобразовать в даты​​ составляет 39 447 дней.​ возникновение путаницы.​

  2. ​ клавишу ВВОД. При​ использование функции​.​​ в приложении Excel,​​ которой нужно установить.​

  3. ​ образом.​«Конечная дата»​​ окна Мастера функций.​​ Excel являются функции​

  4. ​Вставка текущей даты в​ В столбце А​На чистом листе в​Текущая дата в Excel.​или команды​ с помощью функции​ Чтобы вывести правильную​​Если аргумент​​ необходимости измените ширину​СЕГОДНЯ​Время в Excel трактуется​

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

​ Excel реализуется несколькими​​ – способ ввода,​ ячейку А1 введите​​Если нужно установить​​Заполнить​ ДАТА. Например, на​ дату, нужно изменить​​год​​ столбцов, чтобы видеть​в Microsoft Excel. ​​ как дробная часть​​ датами или временем.​

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

Иногда перед пользователем во время работы в Эксель встаёт задача – вывести название дня недели, соответствующее указанной в определенной ячейке дате. Что делать в таком случае? Сверяться с календарем и прописывать день недели вручную? Конечно же, нет. Программа Excel обладает очень мощным функционалом, и вполне способна справиться с такой, казалось бы, непростой задачей. Более того, ее можно выполнить по-разному.

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

1. Отображение дня недели через формат ячеек

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

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

  1. Например, у нас есть ячейка таблицы, в которой указана какая-то конкретная дата.
  2. Щелкаем по ячейке с датой правой кнопкой мыши. Затем в появившемся списке команд кликаем по строке “Формат ячеек…”.
  3. В открывшемся окне переходим во вкладку “Число” (если вдруг по какой-то причине мы сразу не оказались в ней). В перечне “Числовые форматы” кликаем по варианту “(все форматы)”. После этого обращаем внимание на поле “Тип”, кликаем по области ввода значений и пишем в ней “ДДДД” (без кавычек). После этого нажимаем OK, чтобы сохранить изменения и закрыть окно форматирования.
  4. В результате дата в ячейке таблицы будет преобразована в название дня недели согласно календарю. Для того, чтобы увидеть саму дату, отмечаем эту ячейку, и в поле для ввода формул будет отображена исходное значение в формате даты.

Примечание: Вместо “ДДДД” можно задать формат “ДДД” (т. е. 3 буквы, а не 4), и в этом случае день недели будет выведен в ячейку в сокращенном виде. Посмотреть, как это будет выглядеть в итоге можно сразу же в окне форматирования в поле “Образец”.

2. Определение дня недели с помощью оператора ТЕКСТ

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

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

  1. Итак, у нас есть определенная дата в таблице. Выбираем другую ячейку, где планируется отобразить название дня недели и отмечаем ее мышью. Далее нажимаем кнопку “Вставить функцию” (слева от строки формул).
  2. В открывшемся окне щелкаем по текущей категории функций и в раскрывшемся перечне кликаем по строке “Текстовые”.
  3. Из предложенного списка функций нам нужен “ТЕКСТ”, щелкаем по нему и далее жмем кнопку OK.
  4. В результате перед нами откроется окно с настройками функции “ТЕКСТ”, где требуется заполнить значения его аргументов. Сама формула функции выглядит следующим образом:
    =ТЕКСТ(Значение;Формат вывода)    . Для заполнения предлагаются 2 аргумента:
    • “Значение” – это та самая дата, день недели которой требуется вывести. В этом поле можно как прописать дату вручную, так и указать координаты ячейки. Чтобы это сделать щелкаем по полю ввода значений, затем – по требуемой ячейке с датой.
    • В поле “Формат” пишем нужный нам вариант отображения дня недели: “дддд” – полное название, “ддд” – сокращенное. По завершении кликаем OK.
  5. В результате в ячейке с формулой будет выведен день недели, а дата останется в своей ячейке.
  6. Кроме того, если мы теперь изменим дату, то и день недели изменится автоматически, что действительно, очень удобно.

3. Номер дня недели через оператор ДЕНЬНЕД

Есть еще одна функция для выполнения поставленной задачи – это оператор ДЕНЬНЕД, однако, при его использовании будет выводиться не название дня недели, а его порядковый номер. Более того, вовсе не обязательно, чтобы понедельник был под номером 1, так как порядок нумерации дней недели устанавливается пользователем.

Для лучшего понимания давайте попробуем применить это метод на практике.

  1. Как всегда, для начала нам нужна ячейка с датой. Кликаем по другой ячейке, куда будет выводиться результат. После этого жмем кнопку “Вставить функцию”.
  2. В открывшемся окне вставки функций выбираем категорию “Дата и время” и из перечня операторов – “ДЕНЬНЕД”. Подтверждаем действие нажатием OK.
  3. Откроется окно настройки функции, формула которой имеет следующий вид: =ДЕНЬНЕД(дата;[тип]). Как мы видим, у нее тоже 2 аргумента, значения которых требуется указать.
    • В поле “Дата” печатаем нужную дату или указываем координаты ячейки, содержащей дату.
    • В поле “Тип” указываем день недели, с которого будет производиться отсчет. Значениями этого аргумента могут быть только цифры 1, 2 или 3:
      • 1 – первым днем недели будет воскресенье
      • 2 — отсчет начнется с понедельника
      • 3 — как и в случае с цифрой “2”, отсчет начнется с понедельника, но ему будет присвоена цифра “0”.
      • если аргумент “Тип” не будет указан, по умолчанию ему будет присвоено значение “1”, т.е. первым днем недели будет считаться воскресенье. Нам же привычнее вести отсчет с понедельника, поэтому ставим значение “2” и завершаем настройку нажатием кнопки OK.
  4. В ячейку с функцией будет отображен результат в виде числа, соответствующего дню недели. В нашем случае день недели – пятница, и ему присвоен порядковый номер “5” согласно выбранному нами варианту отсчета.
  5. Если изменить дату в исходной ячейке, то в конечной также будут автоматически внесены изменения.

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

Заключение

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

Как в Excel вставить текущую дату и время, как найти количество дней между датами (в т.ч. рабочих)

Доброго дня!

В некоторых случаях при работе с документами Excel очень нужно, чтобы проставлялась текущая дата: например, вы часто делаете отчеты по одной и той же форме (глаз со временем «замыливается» и их можно перепутать); или, скажем, у вас есть десяток-другой задач и вы каждый день по ним просматриваете как продвигается дело, и сколько дней осталось до завершения. ..

Для решения всех подобных «загвоздок» в Excel есть несколько специальных формул. Собственно, о них и будет сегодняшняя заметка.

Примечание: скриншоты в статье приведены из Excel 2019 (материал актуален также для версии 2016, 2013).

 

Дополнение!

Если с вашими документами иногда работают посторонние люди, рекомендую некоторые ячейки защищать от изменения — https://ocomp.info/zashhita-yacheek-v-excel.html

 

*

Работа с датами в Excel

ШАГ 1: вставка текущей даты

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

Формула «ТДАТА()»

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

Формула «СЕГОДНЯ()»

 

 

ШАГ 2: сколько всего дней между двумя датами

Это еще один популярный вопрос. ..

Определить общее количество дней между двумя датами можно с помощью формулы «=ДНИ(B2;A2)» (где B2 и A2 — конечная и начальная дата, соответственно). Разумеется, зная значение в днях — легко узнать и значение в годах (достаточно разделить число на 365…).

Ко-во дней посчитано

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

Второй вариант

 

 

ШАГ 3: определение кол-ва рабочих дней между датами

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

В этом случае может помочь формула «=ЧИСТРАБДНИ(A2;B2)» (где A2 и B2 — начальная дата и конечная, соответственно).

51 рабочий день

 

Кстати, если вам нужно подсветить ячейки красно-зеленым цветом (чтобы сразу же видеть те строки, где, например, горят сроки — рекомендую использовать инструмент «Условное форматирование» в разделе «Главная»). Пример ниже.

Правила выделения ячеек

В общем-то, выглядит это все достаточно наглядно (например, те задачи, которым осталось времени на выполнение меньше 4 дней — у меня в отчете подсвечиваются красным. Удобно!)

Примечание: для обновления текущей даты и времени — нажмите сочетание кнопок Ctrl+R.

План-отчет!

*

На этом сегодня пока всё…

Удачной работы!

✌

Полезный софт:

  • Видео-Монтаж

    • Отличное ПО для начала создания своих собственных видеороликов (все действия идут по шагам!).
    Видео сделает даже новичок!
  • Ускоритель компьютера

    • Программа для очистки Windows от мусора (ускоряет систему, удаляет мусор, оптимизирует реестр).

Другие записи:

«СЕГОДНЯ», «ТДАТА», «ГОД», «МЕСЯЦ», «ДЕНЬНЕД».

 

Функции: «СЕГОДНЯ», «ТДАТА», «ГОД», «МЕСЯЦ», «ДЕНЬНЕД».

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

С самыми востребованными функциями мы и познакомимся.

Первая функция «СЕГОДНЯ».

Поможет поставить дату в таблицу Excel.

Эта функция присваивает ячейке сегодняшнее число в формате даты.


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

  • Установить курсор в ячейку;
  • Выбрать в мастере функций либо набрать вручную функцию «СЕГОДНЯ»
  • Нажать «OK»

Вторая функция «ТДАТА».

Очень похожа на функцию «СЕГОДНЯ», но имеет небольшое отличие – эта функция присваивает ячейке не только текущую дату, но и текущее время.

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

  • Установить курсор в ячейку;
  • Выбрать в мастере функций либо набрать вручную функцию «ТДАТА»
  • Нажать «OK»

Третья функция «ГОД».

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

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

  • Установить курсор в ячейку;
  • Выбрать в мастере функций либо набрать вручную функцию «ГОД»;
  • Выбрать ячейку или указать число, которое будет преобразовано;
  • Нажать «OK»

Данные ячейки преобразуются в год.

Четвертая функция «МЕСЯЦ» аналогична функции «ГОД», только преобразует ячейки не в годы, а в месяцы в числовом формате.

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

  • Установить курсор в ячейку;
  • Выбрать в мастере функций либо набрать вручную функцию «МЕСЯЦ»;
  • Выбрать ячейку или указать число, которое будет преобразовано;
  • Нажать «OK»

Данные ячейки преобразуются в номер месяца.

Пятая функция «ДЕНЬНЕД» аналогично функциям «ГОД» и «МЕСЯЦ» присваивает ячейке значение дня недели.

Подробное видео о функциях для работы с датами:

 

 

Учет финансов в Эксель: как составить таблицу

Этот текст написал читатель Т—Ж. Если вы тоже хотите поразмышлять о чем-то вслух и открыть свою дискуссию, заполните эту форму.

Я сделал собственное приложение для контроля за финансами.

Lazy IT Man

провел самоизоляцию с пользой

Лет десять назад я, как и большинство жителей России, пользовался исключительно наличными. Позже появилась зарплатная карта в том самом банке. Вопросов, на что уходят деньги и сколько их у меня вообще, не возникало, так как денег в виде сбережений особо и не было ¯\_(ツ)_/¯

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

Первые попытки ведения бюджета

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

Что мне не нравилось в мобильных приложениях по ведению бюджета:

  1. Обязательная регистрация. Этого никогда не понимал!
  2. Требование дать доступ к конфиденциальной информации смартфона — контактам, файлам, системным настройкам. Особенно актуально для уже устаревших версий Андроида, где приложения запрашивали сразу все необходимые разрешения при установке.
  3. Неприятные нюансы вроде поддержки только одной валюты или наличия рекламы.
  4. Непонятно, как и кем используются мои данные и что вообще с безопасностью удаленного хранения финансовой информации.

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

Таблица в «Экселе»: плюсы, минусы, подводные камни

Возвращаться к мобильным приложениям я не хотел, так как помнил, какие с ними были проблемы. Делать свое веб-приложение — тогда я зарабатывал на жизнь именно этим — было лень. К тому же не хотелось зависеть от наличия интернета и тратить время и деньги на поддержку сервера. И тут на помощь пришел старый добрый «Эксель».

Преимуществ у электронных таблиц масса:

  1. Независимость от платформы. Хочешь — фиксируй траты на телефоне с Андроидом, а хочешь — анализируй сводку на Макбуке или традиционном компьютере с Виндоус.
  2. Функциональность ограничена только фантазией.
  3. Формулы либо элементарны, либо хорошо задокументированы.
  4. Абсолютно бесплатно!

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

В итоге таблица обрела следующую структуру.

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

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

Звучит слишком сложно? Полностью с вами согласен! Наиболее утомительная часть всего учета — переводы между счетами — была реализована в виде пары операций: расходной с одного счета и доходной для другого.

Лист 2. Счета. Таблица со списком всех счетов, которая состоит из названия, текущего баланса и валюты. В первой версии было поле с начальным балансом счета, но позже для экономии места я заменил его на доходные операции. Позже дополнил таблицу полем с датами завершения действия вкладов. Чтобы сократить количество проверяемых операций, если показания расходятся с данными банков, я добавил поле с датой последней сверки баланса счета в таблице и в приложении банка.

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

Лист 3. Банки. Эта таблица складывает строки счетов и группирует данные по валютам. Благодаря автоматическому импорту курсов доллара и евро с сайта Центробанка РФ можно вычислить итоговую сумму в рублях, по которой строится диаграмма с долями финансов в разных банках.

Лист 4. Категории. Раньше назывался «Бюджет», но когда я понял, что по факту еще не дорос до этой темы, лист превратился в источник категорий. Одно время я делал сводные таблицы по месяцам и категориям, но особой пользы не нашел.

Лист 5. Ценные бумаги. На этот лист пришлось потратить больше всего времени: нужно было свести в одном месте данные по акциям, фондам и облигациям у четырех разных брокеров в разных валютах.

Все остальные листы — это эксперименты или сводки по выборкам данных с предыдущих листов. Накопленные данные об операциях по всем счетам позволяют за несколько минут узнать, как повлияла покупка кофемашины в офис на «кофейные» расходы или сколько ушло на свадьбу.

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

Можно копировать сразу для сотни или тысячи строк, но это все равно неудобно и не меняет главного: таблица «тормозит» все больше и больше. Первый год этого не замечаешь, затем терпишь. К концу второго года накопилось пять тысяч строк операций и терпение закончилось.

Как сделать свое приложение на самоизоляции

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

Я решил заменить электронную таблицу мобильным приложением на Андроиде. Во-первых, оно позволило бы импортировать мою существующую историю расходов и доходов за два года. Во-вторых, приложение не тормозило бы при таком объеме данных. Кроме того, оно не уступает по основной функциональности электронной таблице, так как в нем можно реализовать:

  1. Управление операциями: расходы, доходы, переводы. Это само собой.
  2. Поддержку категорий и счетов. Обязательно с возможностью архивации, чтобы неактуальная категория или закрытый вклад не мозолили глаза.
  3. Мультивалютность и выделение цветом.
  4. Возможность выгрузить данные обратно в электронную таблицу для бэкапа или детального анализа.

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

Все задуманное удалось реализовать. Конечно же, пришлось дополнительно изучить массу документации по работе с базами данных и многопоточности на Андроиде, внедрить рекомендуемые «Гуглом» компоненты для построения архитектуры приложения, которое позже не будет мучительно больно поддерживать.

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

Я с самого начала планировал опубликовать приложение в «Гугл-плее», поэтому добавил пару подсказок для новых пользователей и перевод на английский для мира за пределами РФ. Хотя изначально приложение рассчитано на русскоязычную аудиторию.

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

Итого на онлайн-курсы по мобильной разработке я потратил три недели, на создание базовой версии приложения — четыре. И курсами, и приложением занимался по вечерам после работы, по выходным и праздникам. На публикацию приложения в «Гугл-плее» ушла ровно одна неделя и 25 $. За это время я верифицировал аккаунт разработчика, а приложение прошло проверку сотрудников «Гугла» на соответствие правилам платформы и отсутствие вреда для пользователей.

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

План развития

Основные функции реализованы, но развитие на этом не останавливается. План следующий.

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

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

Ценные бумаги. Та же проблема, что и с банками: исторически сложилось, что ИИС открыт у одного брокера, ОФЗ-н куплены у другого, а акции иностранных компаний — у третьего. Так что нужен инструмент для мониторинга суммарного баланса, роста и уменьшения стоимости. Это единственная область учета финансов, которая временно продолжит жить в моей электронной таблице. Но, так как от таблицы хочу полностью отказаться, учет ценных бумаг тоже перенесу в приложение.

Выводы

  1. В любой неожиданной и даже неприятной ситуации надо искать возможности! Из-за ограничений на перемещения, введенных по всему миру, у меня сорвались две долгожданные поездки, но взамен появилось свободное время. Мне удалось потратить его с пользой.
  2. Даже самое удобное банковское приложение не защищает клиента от ошибочных списаний его денег со счета. А если банков несколько? Для контроля нужен независимый источник информации об операциях.
  3. Главное в учете финансов — это понимание того, чего вы хотите добиться с его помощью. Единственно верного ответа нет: цели, задачи, потребности у всех разные. И здорово, что нам доступен большой выбор инструментов для решения наших задач.

Функции даты и времени (справочная информация)

Функция ДАТА

Возвращает порядковый номер определенной даты

РАЗНДАТ (функция РАЗНДАТ)

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

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

Преобразует дату в текстовой форме в серийный номер

Функция ДЕНЬ

Преобразует серийный номер в день месяца

Функция ДНИ

Возвращает количество дней между двумя датами

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

Вычисляет количество дней между двумя датами на основе 360-дневного года.

Функция ДАТА

Возвращает порядковый номер даты, которая представляет собой указанное количество месяцев до или после даты начала

EOMONTH (функция EOMONTH)

Возвращает порядковый номер последнего дня месяца до или после указанного количества месяцев

ЧАС функция

Преобразует серийный номер в час

ISOWEEKNUM функция

Возвращает номер недели по ISO в году для заданной даты

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

Преобразует серийный номер в минуты

МЕСЯЦ (функция МЕСЯЦ)

Преобразует серийный номер в месяц

ЧИСТРАБДНИ (функция ЧИСТРАБДНИ)

Возвращает количество полных рабочих дней между двумя датами

NETWORKDAYS. Функция INTL

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

СЕЙЧАС функция

Возвращает порядковый номер текущей даты и времени

ВТОРАЯ функция

Преобразует серийный номер во второй

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

Возвращает порядковый номер определенного времени

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

Преобразует время в текстовой форме в серийный номер

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

Возвращает порядковый номер сегодняшней даты

ДЕНЬ НЕД.

Преобразует серийный номер в день недели

НЕДЕЛ. Функция

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

РАБДЕНЬ (функция РАБДЕНЬ)

Возвращает порядковый номер даты до или после указанного количества рабочих дней

РАБОЧИЙ ДЕНЬ.Функция INTL

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

ГОД (функция ГОД)

Преобразует серийный номер в год

ГОД (функция ГОД)

Возвращает долю года, представляющую количество полных дней между start_date и end_date

Справка в Интернете — Быстрая справка

Date_Conversion_Origin_Excel

Последнее обновление: 25. 08.2017

Origin использует 12-часовое смещение от астрономического числа юлианского дня как 0 (1 января 4713 г. до н.э., 12:00 AM).Не делается никаких предположений о часовых поясах или какой-либо схеме переключения времени (например, о переходе на летнее время). Например, 11 июня 1998 года в 21:23:01 имеет исходное число по юлианскому календарю 2450975.8.

Excel поддерживает две системы серийных номеров даты: 1900 и 1904. Microsoft Excel для Windows по умолчанию использует систему дат 1900, которая определяет 1 января 1900 года как порядковый номер даты 1. Microsoft Excel для Macintosh использует систему дат 1904 года, которая определяет 2 января 1904 года как порядковый номер даты 1.

В следующих таблицах показано, как переносить данные разных форматов в Origin.

Формат даты в Excel (система дат 1900) 28.02.1900 или ранее 01.03.1900 или позже
Числовой Добавить 2415019 Добавить 2415018
Текст Преобразование не требуется Преобразование не требуется
Дата Добавить 1 день Преобразование не требуется
Формат даты в Excel (система дат 1904) 01. 03.1904 или ранее 02.03.1904 или позже
Числовой Добавить 2416480 Добавить 2416480
Текст Преобразование не требуется Преобразование не требуется
Дата Добавьте 4 года и 2 дня Добавьте 4 года и 1 день
Примечания:
  • Есть два преобразования (до или после 28.02.1900), потому что в Excel 1900 считается високосным, хотя на самом деле это не так.
  • Пользователи должны понимать, что даты, импортированные из Excel, могут выглядеть как даты в Origin, но все же интерпретироваться Origin как данные Text & Numeric . Чтобы Origin правильно обрабатывал данные даты и времени, необходимо установить параметры Формат и Отображать в диалоговом окне столбца Свойства .

Если вы скопируете данные даты в формате даты из Excel в системе 1904 и вставите их в Origin, вы обнаружите, что это неверно. Чтобы сделать это правильно, у вас есть два варианта:

1. Исправление данных даты в источнике

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

Примечание: Если вы установили формат столбца Дата на Формат даты в Origin перед вставкой из Excel, вы можете игнорировать как Step5 , так и Step6 .
  1. Выберите столбец, в котором находятся данные даты, а затем щелкните правой кнопкой мыши, чтобы выбрать Установить значения столбца … в контекстном меню.
  2. В диалоговом окне « Set Values» введите сценарий, как показано ниже, где C — краткое имя столбца.
  3. Нажмите кнопку OK , и тогда данные даты будут добавлены 1462 и также преобразованы в текстовый и числовой формат .
  4. Выберите столбец, в котором снова находятся данные даты, а затем щелкните правой кнопкой мыши, чтобы выбрать Свойства… в контекстном меню.
  5. В диалоговом окне Свойства столбца перейдите и выберите Дата в раскрывающемся списке Формат в элементе Параметр .
  6. Нажмите кнопку OK , и вы увидите правильные данные даты.
2. Изменение системы дат в Excel

В этом сценарии вы можете перейти к File: Option из меню Excel, чтобы открыть диалоговое окно Excel Options , а затем выбрать Advanced на левой панели и прокрутить вниз до При вычислении этой книги .В этом пункте вы можете изменить систему данных, сняв отметку с , использовать систему дат 1904 .

Ключевые слова: число юлианских дней, числовая система дат, система порядковых номеров даты, Excel

Как превратить «1 января 2017 года» в узнаваемую дату #Excel?

Довольно давно я написал сообщение, чтобы обсудить трюк для форматирования даты с помощью «st», «nd», «rd» и т. Д.

В этом посте я собираюсь обсудить обратный путь: преобразование даты, написанной на английском языке с «Dst MMMM, YYYY», в дату (т.е.е. число), которое распознает Excel. Следующий снимок экрана иллюстрирует ситуацию:

В чем проблема?

Прежде всего, давайте разберемся, с какой проблемой мы здесь столкнулись. Дата была представлена ​​как реальная текстовая строка , а не число, хранящееся в тексте. Например, «21 декабря 2017 г.» — это настоящая текстовая строка . Это означает, что мы не можем повернуть эту текстовую строку с помощью простых приемов, описанных ранее. К счастью, мы имеем дело с проблемой Excel, которую можно решить с помощью формулы.

Вот решение: 
 = (
IF (ISERROR (MID ($ A2,2,1) +0), LEFT ($ A2,1), LEFT ($ A2,2)) & "/" &
MID ($ A2, НАЙТИ ("", $ A2) +1,3) & "/" &
ВПРАВО (A2,4 $)
) +0
Как это работает?

Эта формула выглядит сложной. На самом деле это не так. Давайте погрузимся в это.

  1. Получи день
  2. Получить месяц
  3. Получить год
  4. Соедините день, месяц и год, затем превратите его в число
Get the Day 
 ЕСЛИ (ISERROR (LEFT ($ A2,2) +0), LEFT ($ A2,1), LEFT ($ A2,2))

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

 ЛЕВЫЙ ($ A2, 2)

Тем не менее, мы понимаем, что немного странно видеть дату, написанную как «01 декабря 2017 года». Нет проблем … Мы можем исправить это простым логическим тестом.

Мы прибавляем 0 к результату LEFT ($ A2, 2). Мы получим ошибку, если день будет однозначным. Например, LEFT ($ A2, 2) от «1 декабря 2017 года» даст результат «1 с», который при добавлении 0 даст вам в результате ошибку.

Мы могли бы обернуть формулу с помощью ISERROR, а затем поместить ее в простой оператор IF:

 ЕСЛИ (ISERROR (LEFT ($ A2; 2) +0); TRUE; FALSE)
ИСТИНА -> ошибка -> День однозначный, используется ЛЕВЫЙ (A2,  1 )
ЛОЖЬ -> ошибки нет -> День двузначный, используется ЛЕВЫЙ (A2,  2 )

Имеет смысл?

Примечание. Если вы используете Excel 2010 или более позднюю версию, вы можете использовать ЕСЛИОШИБКА для более короткой формулы:

 = ЕСЛИ ОШИБКА (ЛЕВЫЙ (A2; 2) + 0; ЛЕВЫЙ (A2; 1))
Получить месяц 
 = MID ($ A2; НАЙТИ (""; $ A2) +1,3)

Эта формула довольно проста.НАЙТИ используется для определения позиции первого пробела ”” в текстовой строке, который является разделителем непосредственно перед частью месяца в текстовой строке. Затем развертывается MID, чтобы получить часть месяца в текстовой строке.

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

Получить год 
 ПРАВЫЙ (A2,4 $)

Эта часть получает четыре буквы справа в текстовой строке.Мы используем это, чтобы получить часть года. Никаких особых уловок.

Новичок в функциях MID, LEFT, RIGHT? Вы можете обратиться к публикации ЗДЕСЬ, чтобы узнать больше.

Что у нас осталось?

На примере «21 декабря 2017 года» получаем

  1. День с 1 или 2 цифрами. Например. «21«
  2. Месяц из трех букв: например. «Декабрь»
  3. Год из четырех букв: например, «2017»

Объединив эти результаты с «/», мы получим текстовую строку « 21 / декабря / 2017 ».Чтобы превратить эту текстовую строку в распознаваемую Excel дату, мы добавляем к ней 0 и форматируем результат как Date. Вот так просто.

Вы также можете узнать больше об определении дат проблем.

Объединяя все краткие формулы в одну, мы получаем решение:

 = (
IF (ISERROR (MID ($ A2,2,1) +0), LEFT ($ A2,1), LEFT ($ A2,2)) & "/" &
MID ($ A2, НАЙТИ ("", $ A2) +1,3) & "/" &
ВПРАВО (A2,4 $)
) +0

Думаете, что написать длинную формулу сложно? Взгляните на пост — Написание длинной формулы по шагам.

Не нравится формула? Не беспокойся. Мы также можем решить эту проблему с помощью Get and Transform , обычно известного как Power Query . Поговорим об этом в следующем посте. Оставайтесь в курсе. 🙂

Если вам это нравится, поделитесь:

Нравится:

Нравится Загрузка …

Связанные

Функции даты в Excel (примеры + образцы файлов)

На главную ➜ Функции Excel ➜ Функции даты Excel

Файлы примеров

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

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

Синтаксис

ДАТА (год, месяц, день)

Аргументы

  • год: Число для использования в качестве года.
  • месяц: Число для использования в качестве месяца.
  • день: Число для использования в качестве дня.

Пример

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

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

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

2. Функция DATEVALUE

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

Синтаксис

DATEVAUE (date_text)

Аргументы

  • date_text: Дата, которая хранится в виде текста, и вы хотите преобразовать этот текст в фактическую дату.

Пример

В приведенном ниже примере мы вставили дату непосредственно в функцию, используя двойные кавычки. Если вы пропустите добавление этих кавычек, будет возвращено #NAME? ошибка в результате.

В приведенном ниже примере все даты слева представлены в текстовом формате.

  1. Простая текстовая дата, которую мы преобразовали в действительную дату.
  2. Дата со всеми тремя компонентами (Год, Месяц или День) в числах.
  3. Если в текстовой дате нет года, в качестве года будет использоваться текущий год.
  4. И если у вас есть название месяца в алфавитном порядке, а года нет, в качестве года будет использован текущий год.
  5. Если в текстовой дате нет дня, в качестве номера дня будет использоваться 1.

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

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

Синтаксис

ДЕНЬ (серийный_номер)

Аргументы

  • серийный_номер: Действительный порядковый номер даты, из которой вы хотите извлечь номер дня.

Пример

В приведенном ниже примере мы использовали ДЕНЬ, чтобы просто получить день из даты.

И в приведенном ниже примере мы использовали ДЕНЬ с СЕГОДНЯ, чтобы создать динамическую формулу, которая возвращает номер текущего дня, и она будет обновляться каждый раз, когда вы открываете свой рабочий лист или когда вы пересчитываете свой рабочий лист.

5. Функция ДНИ

Функция

ДНЕЙ возвращает разницу между двумя датами. Он берет дату начала и дату окончания, а затем возвращает разницу между ними в днях. Эта функция была введена в Excel 2013 и поэтому недоступна в предыдущих версиях.

Синтаксис

ДНЕЙ (end_date, start_date)

Аргументы

  • start_date: Это допустимая дата, с которой вы хотите начать подсчет дней.
  • end_date: Это допустимая дата, с которой вы хотите завершить расчет дней.

Пример

В приведенном ниже примере мы указали ячейку A1 как дату начала, а B1 как дату окончания, и в результате мы получили 9 дней.

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

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

6.EDATE Функция

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

Синтаксис

ДАТА (начальная_дата, месяцы)

Аргументы

  • start_date: Дата, с которой вы хотите начать вычисление.
  • месяцев: Количество месяцев для расчета будущей или прошедшей даты.

Пример

Здесь мы использовали EDATE с разными типами аргументов.

  • В первом примере мы использовали 5 в качестве числа месяцев, добавили ровно 5 месяцев в 1 января 2016 года и вернули 01 июня 2016 года.
  • Во втором примере мы использовали -1 месяц, и он дал 31 декабря 2016 года, т.е. дату, которая ровно на 1 месяц раньше 31 января 2016 года.
  • В третьем примере мы вставили дату прямо в функцию.

7. Функция EOMONTH

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

Синтаксис

EOMONTH (начальная_дата, месяцы)

Аргументы

  • начальная_дата: Действительная дата, с которой вы хотите начать вычисление.
  • месяцев: Количество месяцев, которое вы хотите вычислить до и после даты начала.

Пример

В приведенном ниже примере мы использовали EOMONTH с различными типами аргументов:

  • Мы указали 01 января 2016 года как дату начала и 5 месяцев для получения даты в будущем. Поскольку июнь ровно через 5 месяцев после января, в результате он вернулся 30 июня 2016 года.
  • Как я уже упоминал, EOMMONTH достаточно умен, чтобы оценить общее количество дней в месяце.
  • Если вы укажете отрицательное число, оно просто вернет прошедшую дату, то есть количество месяцев назад, которое вы указали.
  • В четвертом примере мы использовали дату в текстовом формате, и она вернула дату без каких-либо ошибок.

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

МЕСЯЦ Функция возвращает номер месяца (от 0 до 12) из ​​допустимой даты. Как вы знаете, в Excel дата представляет собой комбинацию дня, месяца и года, МЕСЯЦ получает месяц от даты и игнорирует остальную часть.

Синтаксис

МЕСЯЦ (серийный_номер)

Аргументы

  • серийный_номер: Допустимая дата, с которой вы хотите получить номер месяца.

Пример

В приведенном ниже примере мы использовали МЕСЯЦ тремя разными способами:

  • В ПЕРВОМ примере мы просто использовали дату и вернули 5 в результате, который является номером месяца МАЙ. .
  • Во ВТОРОМ примере мы указали дату непосредственно в функции.
  • В ТРЕТИМ примере мы использовали функцию СЕГОДНЯ, чтобы получить текущую дату, и МЕСЯЦ вернул из нее номер месяца.

9.ЧИСТРАБДНИ Функция

ЧИСТРАБДНИ Функция возвращает количество дней между датой начала и датой окончания. Проще говоря, с NETWORKDAYS вы можете рассчитать разницу между двумя датами, за исключением субботы и воскресенья, а также праздников (которые вы укажете).

Синтаксис

ЧИСТЫЕ ДНИ (начальная_дата, конечная_дата, праздники)

Аргументы

  • начальная_дата : действительная дата, с которой вы хотите начать вычисление.
  • end_date : допустимая дата, до которой вы хотите рассчитать рабочие дни.
  • [праздники] : допустимая дата, которая представляет собой выходной день между датой начала и датой окончания. Вы можете ссылаться на ячейку, диапазон ячеек или массив, содержащий даты.

Пример

В приведенном ниже примере мы указали 10 января 2015 года как дату начала и 20 февраля 2015 года как дату окончания.

У нас есть 41 день между этими двумя датами, из которых 11 дней — выходные.После вычета этих 11 дней он вернул 30 рабочих дней.

Теперь в приведенном ниже примере с одинаковыми датами начала и окончания мы указали праздничный день, и после вычета 11 дней выходных и 1 праздничного дня возвращено 29 рабочих дней.

Опять же, с теми же датами начала и окончания, мы использовали диапазон из трех ячеек для праздников, чтобы вычесть их из расчета, и после вычитания 11 выходных дней и 3 праздничных дней, которые я упомянул, он вернул 27 рабочих дней.

10. Функция NETWORKDAYS.INTL

NETWORKDAYS.INTL Функция возвращает количество дней между датой начала и датой окончания. В отличие от NETWORKDAYS, NETWORKDAYS.INTL позволяет указать, какие дни вы хотите исключить из расчета.

Синтаксис

NETWORKDAYS.INTL (начальная_дата, конечная_дата, выходные, праздничные дни)

Аргументы

  • начальная_дата: с , где вы хотите начать расчет.
  • end_date: Допустимая дата, до которой вы хотите рассчитать рабочие дни.
  • [выходные]: Число означает исключение выходных из расчета.
  • [праздники]: Список дат, представляющих праздники, которые вы хотите исключить из расчета.

Пример

В приведенном ниже примере мы использовали 01 января 2015 года как дату начала и 20 января 2015 года как дату окончания. И мы указали 1, чтобы взять воскресенье — субботу как выходные.Функция вернулась через 14 дней после исключения 6 выходных дней.

Ниже мы использовали те же даты. И я использовал 11 дюймов для выходных дней, что означает, что в качестве выходных будет считаться только воскресенье. Вместе с тем, 10 января 2015 года мы также использовали как выходной.

У нас 3 воскресенья между датами и праздником. После исключения всех этих дней функция вернула результат за 16 дней. В приведенном ниже примере мы использовали диапазон для указания праздников. Если у вас более одной даты на праздники, вы можете ссылаться на весь диапазон.

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

Здесь 0 представляет рабочий день, а 1 — нерабочий день. И семь цифр представляют 7 дней недели.

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

Функция

СЕГОДНЯ возвращает текущую дату и время в соответствии с системной датой и временем.Дата и время, возвращаемые функцией NOW, постоянно обновляются всякий раз, когда вы обновляете что-либо на листе.

Синтаксис

СЕГОДНЯ ()

Аргументы

  • В функции СЕГОДНЯ нет аргумента, все, что вам нужно сделать, это ввести его в ячейку и нажать Enter, но будьте осторожны, поскольку СЕГОДНЯ изменчивая функция, которая обновляет свое значение каждый раз, когда вы обновляете вычисления на листе.

Пример

В приведенном ниже примере мы использовали СЕГОДНЯ с другими функциями, чтобы получить номер текущего месяца, текущего года и текущего дня.

12. WEEKDAY Function

WEEKDAY Функция WEEKDAY возвращает номер дня недели (от 0 до 7) от даты. Проще говоря, функция WEEKDAY принимает дату и возвращает номер дня этой даты.

Синтаксис

WEEKDAY (serial_number, [return_type])

Аргументы

  • serial_number : допустимая дата, с которой вы хотите получить номер недели.
  • [return_type] : число, представляющее день недели, с которого начинается неделя.

Пример

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

В приведенном ниже примере мы использовали WEEKDAY с IF для создания формулы, которая сначала проверяет день недели и возвращает значение «Weekday» или «Weekend» на основе значения, возвращаемого из WEEKDAY.

13. WEEKNUM Функция

WEEKNUM функция возвращает номер недели даты. Проще говоря, WEEKNUM возвращает номер недели даты, который вы указали в диапазоне от 1 до 54.

Синтаксис

WEEKNUM (serial_number, return_type)

Аргументы

  • serial_number для даты: которой вы хотите получить номер недели.
  • [return_type]: Число, указывающее начальный день первой недели года.У вас есть две системы, чтобы указать дату начала недели.

Пример

В приведенном ниже примере мы использовали TODAY с WEEKNUM, чтобы получить номер недели текущей даты. Номер недели будет обновляться автоматически каждый раз при изменении даты.

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

14. Функция ГОД

ГОД Функция возвращает номер года из допустимой даты.Как вы знаете, в Excel дата представляет собой комбинацию дня, месяца и года, а функция ГОД получает год от даты и игнорирует остальную часть.

Синтаксис

ГОД (дата)

Аргументы

  • дата: Дата, с которой вы хотите получить год.

Пример

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

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

Другие функции Excel

Дата и время в Excel

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

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


Как Excel сохраняет дату и время

Excel сохраняет дату и время в виде числа представляет количество дней, прошедших с января 1900 г., плюс дробный часть 24-часового дня: ддддд.tttttt . Это называется серийной датой или серийной датой . дата-время .

Даты

Целая часть числа, ддддд, представляет количество дней с 0 января 1900 года. Например, дата 19-янв-2000 хранится как 36 544, так как с тех пор прошло 36 544 дня. 1900-янв-0. Цифра 1 представляет 1 января 1900 года. Должен быть отметил, что число 0 не , а не представляет 1899-декабрь-31. Оно делает не.Если вы используете МЕСЯЦ функция с датой 0 вернет январь, а не декабрь. Более того, ГОД функция вернет 1900, а не 1899.

На самом деле это число на единицу больше, чем фактическое количество дней. Это потому, что Excel ведет себя так, как будто дата 1900-фев-29 существовал. Это не так. 1900 год не был скачком год (2000 год — високосный). В Excel на следующий день после 1900-28 февраля — 1900-февраля-29. На самом деле, на следующий день после 28 февраля 1900 г. 1900-март-1.Это не «ошибка». Действительно, это по дизайн. Excel работает так, потому что это действительно ошибка в Lotus 123. Когда был представлен Excel, у 123 был почти весь рынок программное обеспечение для работы с электронными таблицами. Microsoft решила продолжить ошибку Lotus в для полной совместимости. Пользователи, перешедшие с 123 на Excel, не нужно вносить никаких изменений в свои данные. Пока все твои датируется позже 1 марта 1900 г., это не должно вызывать беспокойства.

раз

Дробная часть числа ttttt, представляет собой дробную часть 24-часового дня.Например, 6:00 AM хранится как 0,25 или 25% от 24-часового дня. Аналогичным образом, 18:00 хранится в 0,75, или 75% процентов от 24-часового дня.

Как как видите, любую дату и время можно сохранить как сумму даты и время. Например, 15:00 19 января 2000 года хранится внутри как 36544.625. Когда вы вводите время без значения, например, вводите 15:00 в ячейку, часть даты равна нулю. Ноль означает, что есть дата не связана со временем.Вы должны помнить, что ввод только time , а не , автоматически подставляет текущую дату.

Как преобразовать текстовые даты в Excel

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

Настройте себя

Общий обзор работы с датами см. В моей предыдущей статье «Как преобразовать текстовые даты в числовые».

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

Excel настроен на автоматическое распознавание дат в коротком и длинном форматах, которые вы используете в Windows. В Windows 10 их можно найти и изменить, выбрав «Настройки»> «Время и язык»> «Дата и время»> «Изменить форматы даты и времени».

Короткая дата d / MMM / yyyy означает, что ожидаемый формат — это день без начального нуля, за которым следует сокращенное название месяца, а затем год из четырех цифр, с тремя частями, разделенными «/», как в « 1 января 2001 г. ». Длинная дата дддд, д ММММ ггггг определяет формат как день недели, за которым следует день без начального нуля, полное название месяца и затем четырехзначный год, как в «Среда, 6 / июнь / 2018 ». Короткое время h: mm tt будет «5:30 pm», а длинное время h: mm: ss tt «5:30:12 PM».

Excel распознает некоторые дополнительные распространенные форматы, например, с числовыми месяцами с начальным нулем или без него и гггг-ММ-дд (например, «2011-12-31»).

Встроенные функции для управления датами

В Excel 2016 вы можете найти инструменты для работы с датами, щелкнув «Дата и время» на ленте формул.

Самой важной функцией для преобразования даты в текст в Excel является функция ДАТАЗНАЧ. Он используется для преобразования текстовых дат в число, которое Excel распознает как дату.Даты сохраняются как последовательные числа, начиная с 1, представляющего «Янв. 1, 1990 ». Например, если ввести = DATEVALUE («23.02.2011»), получится «40597». Функция ВРЕМЗНАЧ аналогичным образом преобразует текстовое время («12:20 AM») в число, которое Excel распознает как время.

После завершения преобразования мы можем либо отформатировать число в любой формат даты, который мы выберем, либо использовать специальные функции Excel для работы с датами. Мы можем изменить форматирование, щелкнув ячейку правой кнопкой мыши, выбрав «Формат ячеек», а затем выбрав нужный формат из доступных типов даты.

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

Excel предоставляет функции для извлечения частей даты, таких как ДЕНЬ, ГОД и ДЕНЬ НЕДЕЛИ. Другие полезные функции включают СЕЙЧАС для получения текущей даты и времени и ДНИ для вычисления количества дней между двумя датами.

Преобразование в нестандартные форматы

К сожалению, если ваши даты имеют более сложный или менее распространенный формат, Excel может не сразу распознать их.Придется добавить дополнительный шаг для преобразования дат в распознанный формат. Мы можем прибегнуть к извлечению частей текстовой строки с помощью функций LEFT, MID и RIGHT, а затем объединить части с помощью & или CONCATENATE в формат, поддерживаемый Excel. Например, если моя текстовая строка — «2018-февраль-02», которую Excel не распознает, я могу использовать следующую формулу, чтобы поменять местами части текста дня и года на «02-февраль-2018». , который Excel распознает.

= ВПРАВО (A2,2) & «-» и СРЕДНЯЯ (A2,6,3) & «-» & ЛЕВАЯ (A2,4)

Зарегистрируйтесь в Displayr

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *