Как отделить дату от времени в excel формула

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

---

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

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

2. в Формат ячеек диалоговом окне на вкладке Число щелкните Время от Категория раздел, перейти к Тип список, чтобы выбрать нужный тип даты. Смотрите скриншот:

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

4. Нажмите OK закрыть Формат ячеек диалог. Затем в первой ячейке Время столбец (кроме заголовка) введите эту формулу = ЦЕЛОЕ (A2) (A2 — это ячейка, которую нужно разделить), затем перетащите маркер заполнения в диапазон, необходимый для применения этой формулы. Смотрите скриншоты:

5. Перейдите в первую ячейку столбца «Время» (кроме заголовка) и введите эту формулу. = A2-C2 (A2 — это ячейка, на которую вы разделены, а C2 — это ячейка даты) и перетащите дескриптор заполнения в нужный диапазон. Смотрите скриншоты:

Затем дата и время были разделены на две ячейки.

---

Самый простой и удобный способ разделить дату и время — это применить Разделить клетки полезности Kutools for Excel, без сомнения!

После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)

1. Выберите ячейки даты и времени и нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:

2. в Разделить клетки диалог, проверьте Разделить на столбцы и Space параметры. Смотрите скриншот:

3. Нажмите Ok и выберите ячейку для вывода разделенных значений и щелкните OK. Смотрите скриншот:

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

Наконечник. С помощью утилиты «Разделить ячейки» вы можете разделить одну ячейку на строки или столбцы по разделителю или фиксированной ширине. Если вы хотите получить бесплатную пробную версию функции Split Cells, пожалуйста, перейдите на бесплатную пробную версию Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.

---

---

С удобной утилитой — Извлечь текст of Kutools for Excel, вы также можете быстро извлечь дату и время из одного столбца в два столбца.

После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)

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

2. Нажмите OK. И перейдите к другому диапазону столбцов и отформатируйте его как время. см. снимок экрана:

3. Выберите ячейки даты и времени (кроме заголовка) и нажмите Кутулс > Текст > Извлечь текст. Смотрите скриншот:

4. Затем в Извлечь текст диалог, тип * и пространство в Текст , затем нажмите Добавить добавить его в Извлечь список. Смотрите скриншоты:

 

5. Нажмите Ok и выберите ячейку, чтобы поставить даты. Смотрите скриншот:

6. Нажмите OK. Вы можете увидеть дату извлечения данных.

7. Снова выберите дату и время и нажмите Кутулс > Текст > Извлечь текст. Удалите все критерии из списка Извлечь и введите пространство и * в Текст поле и добавьте его в Извлечь список. Смотрите скриншоты:

  

8. Нажмите Ok чтобы выбрать ячейку для вывода результата и нажать OK чтобы закрыть диалог. Теперь данные разбиты на столбцы.

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

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

---

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

1. Выберите диапазон данных и нажмите Данные > Текст в столбцы, затем в появившемся диалоговом окне отметьте разграниченный вариант. Смотрите скриншот:

2. Затем нажмите Download для открытия Мастер преобразования текста в столбец, шаг 2 из 3 диалог, проверьте Space (вы также можете выбрать другие разделители по своему усмотрению) в Разделители раздел. Смотрите скриншот:

3. Нажмите Завершить чтобы закрыть диалоговое окно, диапазон данных разбивается на столбцы. Выберите диапазон дат и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню. Смотрите скриншот:

4. Затем в Формат ячеек диалоговое окно, нажмите Время от Категории раздел и выберите нужный тип даты в Тип раздел. Смотрите скриншот:

5. Нажмите OK. Теперь результат показан ниже;

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

Функции для извлечения различных параметров из дат и времени в Excel

​Смотрите также​ быть формулой сегодня​ августа 2010г придется​​ 100. Спасибо БОЛЬШОЕ!​​ только внешнее представление​ прямо в ячейке,​

​ или времени по​ суток от полудня​ или «чч» либо​Тип​Тип​ ввести в ячейку​и​ динамической даты или​ журнал или автоматически​ аргумент опущен, то​ГОД​Весь курс: урок 1​ (необезательно формула, главное​ извращатся, т.к. ДАТАЗНАЧ()​ Оказывается как все​

​ данных, а истинное​ то жмите Контрл​ умолчанию. Чтобы ввести​​ до полуночи. В​​ непосредственно перед кодом​​будет отображен код​​выберите нужный формат​ значение​ТДАТА​ времени в ячейке​ отображать их в​ это равносильно значению​, можно привести формулу,​ | урок 2​ дата того дня​ не работает, может​

​ просто)))​ значение остаётся прежним​ h, в «Найти»​ дату или время​ противном случае используется​ «сс», Excel отображает​ выбранного в действии​ даты или времени.​

ГОД()

​2/2​меняют свои значения​ на листе является​ ячейке при каждом​ один. Все возможные​ которая вычисляет количество​

​ |​ когда выполняется работа.​​ есть более простой​​Вадим​Юлия​ пишите /* /​ в особом формате,​

​ 24-часовая система. Код​ не месяцы, а​​ 3 формата даты​​Примечание:​, Excel автоматически интерпретирует​ только при расчете​ использование функции листа.​

​ пересчете формул. Вставить​ варианты аргумента Вы​ лет между двумя​урок 3​02.12.2009​ путь​: Добрый вечер всем!​: Я в ячейку​ без слешей (звездочка​ например​

МЕСЯЦ()

​ «м» или «мм»​ минуты.​ или времени. Встроенный​ Форматы даты и времени,​ его как дату​ листа или при​

ДЕНЬ()

​Для вставки обновляемой текущей​ текущие дату и​ можете увидеть на​ датами:​| урок 4​02.12.2010​

ЧАС()

​35476 (стрит)​Помогите пож-та. На​ ввожу формулу: =целое(D4)​ и 2 пробела),​январь 2010​ должен располагаться непосредственно​

МИНУТЫ()

​Часы, минуты и секунды​ формат даты или​ отмеченные звездочкой (*),​ и отображает в​ выполнении макроса, содержащего​ даты или времени​

СЕКУНДЫ()

​ время в ячейку​ рисунке ниже:​Если внимательно присмотреться к​ | урок 5​24.10.2011​Guest​

ДЕНЬНЕД()

​ форуме есть схожие​ правильно?​ в «Заменить на»​, можно отформатировать значение​ после кода «ч»​Для отображения​ времени невозможно изменить​ зависят от параметров​ ячейке дату​

​ эту функцию. Непрерывного​ используются функции​ можно несколькими способами.​Если в качестве второго​ датам, то становится​Microsoft Excel предлагает более​22.10.2016​: Не совсем понял.​ примеры, но именно​Нет, не правильно.​ ничего не пишите,​ как текст, выбрав​

​ или «чч» или​Используйте код​​ или удалить, поэтому​​ даты и времени​​02.фев​​ их обновления не​СЕГОДНЯ​Статические значения на листе​ аргумента функции​ очевидно, что формула​ 20 самых различных​​Или​​ Вы сказали, что​

​ как у меня​ Сначала прочитайте Правила​ «Заменить все»​ пункт​ непосредственно перед кодом​Часов в виде чисел​ можно безопасно перезаписать​ в панели управления​. Если такой формат​ происходит. В качестве​и​ — изменяются при​ДЕНЬНЕД​ вычисляет не совсем​ функции для работы​09.06.1990 г. по​

​ для даты в​

office-guru.ru

Вставка текущих даты и времени в ячейку

​ не нашел. Как​​ форума. Приложите свой​почему-то тут 2 пробела​Текстовый​ «сс», в противном​ от 0 до​ его.​ Windows. На форматы​ не подходит (например,​ значений даты и​ТДАТА​ пересчете или открытии​подставить​ верный результат. Это​ с датами и​ 02.04.1992 г.; с​ виде даты (как​ разделить дату от​ пример. А потом​ подряд преобразуются в​в списке​

​ случае Excel отображает​ 23​Внесите необходимые изменения в​ без звездочки эти​ нужно отобразить дату​ времени используются показания​, как показано в​ листа. При нажатии​2​ происходит потому, что​ временем. Кому-то этого​

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

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

1. ​ количества может показаться​ 30.11.2003 г.; с​ например 25.08.2010) реализовать​ ячейки, если исходный​

2. ​maccbekhruz​Kolomoets​

   • ​.​ минуты.​Часов в виде чисел​

   • ​Тип​Для отображения даты и​2 февраля 2009 г.​Чтобы изменить формат даты​

   • ​ Дополнительные сведения об​ такие как Ctrl​​ будут соответствовать номера​ значения месяцев и​

   • ​ много, а кому-то​ 21.04.2005 г. по​ это легко. Подскажите​ формат представления такой:​: Как сделать, чтобы​: Спасибо огромное, то​Чтобы быстро ввести текущую​

   • ​Создание пользовательских числовых форматов​ от 00 до​. При этом можно​ времени в формате​или​ или времени, щелкните​​ использовании этих функций​ +; Чтобы вставить​

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

​ от 1 (понедельник)​ дней, и оперирует​ будет катастрофически не​ 05.09.2008 г. и​ пожалуйста как именно.​1/12/2012 5:58:00 AM​ дата и время​ что нужно.​ дату, выберите любую​ может показаться сложной​

​ 23​ использовать любые из​ другого языка выберите​​02.02.09​​ ячейку правой кнопкой​​ см. в статьях​​ текущую дату в​ до 7 (воскресенье).​ только годами.​ хватать. Тем не​ с 28.10.2015​Guest​1/13/2012 8:33:00 AM​

Пример

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

1. ​ Функция СЕГОДНЯ и​ ячейку Excel «принимает​ В итоге функция​

2. ​Возвращает номер месяца, соответствующий​ менее, каждая из​09.06.1990​: Не совсем понял.​1/13/2012 2:54:00 PM​ ячейки?​Юлия​ листе и нажмите​

   ​ не делали этого​​Минут в виде чисел​ приведенных ниже таблицах.​ поле​ формат даты в​

3. ​Формат ячеек​ Функция ТДАТА​ в виде снимка»​ вернет нам значение​ заданной дате. Месяц​ предложенных в Excel​​02.04.1992​​ Вы сказали, что​​Спасибо!​​Спасибо.​​: А как сделать​​ клавиши CTRL+; (точка​

​ раньше. Дополнительные сведения​ от 0 до​Дни, месяцы и годы​Язык (местоположение)​

​ диалоговом окне​​. Затем в диалоговом​​Формула​​ текущую дату и​​6​​ возвращается как целое​ функций будет для​19.03.2003​ для даты в​Глупый​Сергей​ наоборот? Убрать время,а​ с запятой), а​ о создании пользовательских​ 59​

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

​   ​.​Формат ячеек​ окне​​Описание (результат)​​ вставляет дату в​:​​ число в диапазоне​​ Вас полезной и​​30.11.2003​​ виде даты (как​​: Здравствуйте. Помогит, плиз.​​: дата =ЦЕЛОЕ(A2)​​ дату оставить.​​ затем, если необходимо,​​ числовых форматов см.​​м​​Для отображения​​Число в активной ячейке​, как описано в​​Формат ячеек​​=СЕГОДНЯ()​

support.office.com

Форматирование чисел в виде значений даты и времени

​ ячейку. Так как​В данном уроке мы​ от 1 до​ обязательно найдет свое​21.04.2005​ я понял это​ Есть ячейка имеющая​время =ОСТАТ(A2;1)​китин​ нажмите клавишу ВВОД.​ в статье Создание​Минут в виде чисел​Используйте код​ выделения на листе​ приведенной ниже процедуре.​на вкладке​Текущая дата (значения могут​ значение этой ячейки​

В этой статье

​ вкратце рассмотрели 7​ 12.​

​ практическое применение.​05.09.2008​

​ например 25.08.2010) реализовать​ формат даты (24​

Отображение чисел в виде даты или времени

​PogodinVladimir​:​ Чтобы вставить дату,​ и удаление пользовательских​ от 00 до​​Месяцев в виде чисел​​ отображается в поле​ Аналогично, если вести​Число​ быть разными)​​ не изменится, он​​ функций Excel, которые​Возвращает значение дня, соответствующее​Все возможные функции Вы​28.10.2015​​ это легко. Подскажите​​ августа 2010г.) Берет​​: Датазнач() и Время​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЦЕЛОЕ(A2)​ которая будет обновляться​ числовых форматов.​​ 59​​ от 1 до​Образец​ в ячейку значение​в группе​​=ТДАТА()​​ считается статические.​​ позволяют извлекать требуемые​​ заданной дате. День​ можете найти в​22.10.2016​ пожалуйста как именно.{/post}{/quote}​​ она это значение​​ знач(),​​формат ячейки дата​​ до текущей даты​К началу страницы​мм​​ 12​​, что позволяет просматривать​

1. ​9:30 a​​Числовые форматы​​Текущие дата и время​​Выделите на листе ячейку,​​ нам параметры из​ возвращается как целое​ категории​​Nic70y​​Guest​

   

   ​ из другой ячейки​​файл во вложении​​первый раз поспешил​ при каждом открытии​​Чтобы быстро применить формат​​Секунд в виде чисел​

2. ​м​​ выбранные параметры форматирования​​или​​, нажмите​​ (значения могут быть​​ в которую требуется​​ дат и времени.​

   

3. ​ число в диапазоне​​Дата и время​​: одна?​: Маленько не то,​

   ​ другого листа.​​jakim​Юлия​ листа или перерасчете​ даты или времени​ от 0 до​Месяцев в виде чисел​ чисел.​9:30 p​

4. ​Дата​ разными)​ вставить текущую дату​ В следующем уроке​ от 1 до​​группы команд​​формула массива:​

   

   ​ да ну да​ТЗ: Имеется 3​: Дата =LEFT(A2;10)время =RIGHT(A2;8)​​: Не поняла..)​​ формулы, введите в​ по умолчанию, щелкните​ 59​

   

​ от 01 до​

Создание пользовательского формата даты или времени

1. ​К началу страницы​​, Excel автоматически интерпретирует​​или​Выделите текст в таблице,​ или время.​​ мы научимся отображать​​ 31.​

   

   ​Библиотека функций​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>ЕСЛИОШИБКА(ЕСЛИ(ОСТАТ(СТРОКА(A1);3);—ПСТР(A$8;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(—ПСТР(ПОДСТАВИТЬ(A$8;» «;»_»)&ПОВТОР(«_»;999);СТРОКА($1:$999);10));СТРОКА($1:$999));СТРОКА(A1)-ОКРУГЛВВЕРХ(СТРОКА(A1)/3;)+1);10);»»);ЕСЛИ(F8<>»»;СЕГОДНЯ();»»))​​ ладно, допетрю как​ ячейки, нужно разбить​​wasya​​Юлия​

2. ​ пустую ячейку =СЕГОДНЯ()​​ ячейку с датой​​с​​ 12​​На вкладке​​ его как время​​Время​ приведенной выше, и​Выполните одно из следующих​ даты и время​Данная функция возвращает значение​. В рамках данного​Владимир098​ нить​ даты на день,​: Имеем ячейку с​

3. ​: Я выделяю ячейки,​​ и нажмите клавишу​​ или временем и​​Секунд в виде чисел​​мм​​Главная​​ и отображает в​и в списке​ нажмите клавиши CTRL+C.​ действий.​ в ячейках Excel​ часа, соответствующее заданному​ урока мы рассмотрим​: Добрый день! Вновь​Юрий М​

4. ​ месяц, год, все​ датой и временем​​ выбираю формат ячейки​​ ВВОД.​ нажмите клавиши CTRL+SHIFT+#​ от 00 до​Месяцев в виде «янв»​

   ​нажмите кнопку вызова​​ ячейке время​

​Тип​	​На пустом листе щелкните​
​Чтобы вставить текущую дату,​ с помощью функций.​ времени. Час возвращается​	​ всего лишь 7​
​ обращаюсь к Вам​: В ячейке В3​ в числовых выражениях.​	​ (в одной ячейке).​
​ «дата», отображается только​Kolomoets​	​ или [email protected]​
​ 59​ — «дек»​	​ диалогового окна рядом​
​9:30 AM​выберите тип, после​	​ ячейку A1 и​
​ нажмите клавиши CTRL+;​ Надеюсь, что этот​ как целое число​	​ функций данной категории,​
​ в надежде на​ поменяйте формат и​Буду благодарен.​	​ Нужно сделать две​
​ дата, но в​: Здравствуйте,​	​Если после применения к​
​сс​ммм​	​ с именем группы​
​или​ чего нажмите​ нажмите клавиши CTRL+V.​	​ (точка с запятой).​
​ урок Вам пригодился!​ в диапазоне от​	​ которые позволяю извлекать​

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

​Часов в формате «4​

​Месяцев в виде «январь»​	​Число​
​9:30 PM​ОК​ При работе в​	​(Windows) Чтобы вставить текущее​
​ Всего Вам доброго​ 0 до 23.​ различные параметры из​	​Имеется столбец А,​
​MCH​: Для этого существуют​ одной дата, в​	​ равно остается время.​
​ суть вопроса собственно​ или времени в​ AM»​	​ — «декабрь»​
​.​. Формат отображения времени​.​	​ Excel Online повторите​
​ время, нажмите клавиши​ и успехов в​Возвращает количество минут, соответствующее​	​ значений дат и​
​ в который внесены​: >​	​ одноименные функции.​
​ другой время.​китин​	​ влезла в тему.​
​ ней отображаются символы​Ч AM/PM​	​мммм​
​Чтобы открыть диалоговое окно​ также можно настроить​	​При вводе в ячейку​
​ копирование и вставку​ CTRL+SHIFT+; (точка с​	​ изучении Excel.​
​ заданному времени. Минуты​	​ времени, например, номер​
​ данные: дата и​	​на Вашем примере​

​MCH​​Возможно ли это?​: что?​В ячейке дата​;##​Времени в формате «4:36​Месяцев в виде первой​Формат ячеек​ в диалоговом окне​ даты или времени​ для каждой ячейки​ запятой).​Автор: Антон Андронов​ возвращаются как целое​ месяца, год, количество​ время, необходимо избавиться​kim​: 24 августа 2010г.​SAS888​Юлия​ и время. Как​, вероятно, ширина ячейки​ PM»​

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

​: Вот, симпатично сделал…​

Советы по отображению даты и времени

• ​ это не дата​: В ячейке «A3″​: Добрый день. Ну​ убрать дату, а​ недостаточна для отображения​Ч:мм AM/PM​ммммм​

• ​ клавиши​.​ формат даты и​Важно:​​ время, нажмите клавиши​​ Мы стараемся как можно​ от 0 до​Если Вы только начали​ только дату.​Guest​ а текст, дата​ — дата и​ не получается у​ время оставить?​ данных. Чтобы увеличить​Времени в формате «4:36:03​Дней в виде чисел​CTRL+1​

• ​На вкладке​ времени по умолчанию.​ Чтобы пример заработал должным​+ ; (точка​​ оперативнее обеспечивать вас​​ 59.​​ работать с датами​​Голову поломал, пытаясь​: 5 часов форматировал​ в формате ДД​ время (в формате​ меня)) Имеются ячейки​Сразу во всех​ ширину столбца, содержащего​ P»​ от 1 до​.​​Главная​​ Этот формат определяется​ образом, его необходимо​ с запятой).​​ актуальными справочными материалами​​Возвращает количество секунд, соответствующее​​ и временем в​​ что то придумать,​

• ​ все, подставлял формы,​ ММММ ГГГ будет​ дата/время).​ D4,D5 и т.д.,​ строках.​ данные ячейки, дважды​Ч:мм:сс A/P​ 31​В списке​в группе​ региональными параметрами даты​ вставить в ячейку​(Windows) Чтобы вставить текущие​ на вашем языке.​ заданному времени. Секунды​ Excel, советуем сначала​ а массив большой,​

support.office.com

В ячейке дата и время. Как убрать дату, в время оставить? (Формулы/Formulas)

​ делал связи, потом​​ 24 Август 2010​
​В ячейки «D3″​ в них даты​Спасибо заранее.​
​ щелкните его правую​Истекшего времени в часах;​д​Числовые форматы​
​Число​ и времени, заданными​
​ A1 листа.​

​ дату и время,​​ Эта страница переведена​ возвращаются как целое​
​ обратиться к урокам​
​ вручную редактировать невариант!​ взял и удалил​Guest​ и «E3» Вашего​ и время 28.02.2014​Gustav​ границу. Ширина столбца​ например 25:02​Дней в виде чисел​выберите пункт​нажмите кнопку вызова​

​ в панели управления​​Чтобы переключиться между просмотром​
​ нажмите клавиши CTRL+;​
​ автоматически, поэтому ее​ число в диапазоне​ по основным понятия​Данный столбец во​ нечаянно мимо корзины.​: :)​ файла помещаем «=A3».​ 22:33:00, 22.04.2014 11:22:00​: В ячейку B1​ будет автоматически изменена​
​[ч]:мм​ от 01 до​Дата​

​ диалогового окна рядом​​ Windows, и меняется​ результатов и просмотром​
​ (точка с запятой),​

​ текст может содержать​​ от 0 до​ и форматированию временных​ вложенном файле, в​

​ ЛЕПОТА!​​Михаил С.​​ Форматы ячеек у​​ и т.д. Как​
​ формулу:​

​ таким образом, чтобы​​Истекшего времени в минутах;​

​ 31​​или​ с именем группы​ при изменении этих​ формул, возвращающих эти​ затем ПРОБЕЛ, а​ неточности и грамматические​

​ 59.​​ данных.​

​ колонке B -​​doberman​: Что за ники​ Вас уже установлены.​ мне убрать время​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=A1-ЦЕЛОЕ(A1)​ отобразить содержимое ячеек.​ например 63:46​дд​Время​Число​ параметров. Для отображения​ результаты, нажмите клавиши​ после этого — CTRL+SHIFT+;​ ошибки. Для нас​Данная функция возвращает номер​Возвращает значение года, соответствующее​ ожидаемый результат.​: Здравствуйте! Помогите пожалуйста​ вы себе выбираете?​

​kaa​​ во всех ячейках?​​Смысл в том,​​ Можно также перетащить​
​[мм]:сс​Дней в виде «Пн»​, а затем выберите​.​

​ даты и времени​​ Ctrl+` (знак ударения)​ (точка с запятой).​ важно, чтобы эта​
​ дня недели, соответствующий​ заданной дате. Год​Заранее благодарю!​ решить задачу. (VBA,​ как обратиться?​

excelworld.ru

Как разделить дату и время по отдельным ячейкам?

​: Целая часть ячейки​​ Если я выделю​ что полная дата​ правую границу столбца​Истекшего времени в секундах​
​ — «Вс»​

​ формат, наиболее близкий​​Чтобы открыть диалоговое окно​
​ можно использовать другие​

​ или на вкладке​​(Mac) Чтобы вставить текущие​ статья была вам​
​ заданной дате. Номер​

​ может быть целым​​Czeslav​

planetaexcel.ru

Разбить данные из одной ячейки в две разные

​ формулы не важно).​​Короче, давайте конкретный​ отвечает за дата,​ ячейки и выберу​ с временем -​ до необходимой ширины.​[сс]​ддд​
​ по стилю к​

​Формат ячеек​​ форматы, на большинство​Формулы​ дату и время,​ полезна. Просим вас​
​ дня недели по​ числом в диапазоне​: Формулой =DATE(YEAR(A1);MONTH(A1)AY(A1)) преобразовать​ Имеется ячейка с​ пример, тогда можно​

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

​в группе​​ нажмите клавиши CTRL+;​
​ уделить пару секунд​

​ умолчанию возвращается как​​ от 1900 до​ и потом со​ набором дат (количество​ подсказать решение.​ время. Для даты​

planetaexcel.ru

Разбить дату

​Зы. Пост выше​​ воспоьзуйтесь =ЦЕЛОЕ(), для​ но в строке​ часть — это​ времени путем выбора​AM и PM​дддд​ создании пользовательских числовых​
​CTRL+1​ влияют.​нажмите кнопку​ затем ПРОБЕЛ, а​ ли она вам,​
​ диапазоне от 1​

​В ячейке A1 представлена​​ в столбец А.​ от 1).​

​ — тоже мой​​ времени — разница​ формулы всё равно​ дата, а дробная​ варианта​   .  Если формат содержит обозначение​Лет в виде чисел​

​ форматов проще видоизменить​​.​

​Отображение чисел в виде​​Показывать формулы​ после этого —​ с помощью кнопок​
​ (воскресенье) до 7​ дата в формате​DV68​
​02.12.2009 г. по​Guest​

​ между искомой ячейкой​​ будет время. Вот​

​: Вариант формулы​​ 02.12.2010 г. и​:​ и датой :)​ как от него​ течение суток. При​в списке​ применяется 12-часовая система,​ 99​

​ создавать формат с​​Числовые форматы​Создание пользовательского формата даты​Скопировав пример на пустой​ запятой).​ удобства также приводим​Функция имеет два аргумента,​. Это пользовательский (нестандартный)​Код =ОТБР(A1)​

​ с 24.10.2011​​MCH​слэн​ избавиться, скажите пож-та?​ соответствующем формате ячейки,​

​Числовые форматы​​ где «AM» или​гг​ нуля).​

​Дата или время обновляемые​​ ссылку на оригинал​

​ второй из них​​ числовой формат представления​Выделяем нужный диапазон​Нужно получить каждую​: Для даты в​: =—ТЕКСТ(A3;»дд.ММ.гггг»)​Pelena​

planetaexcel.ru

Разделение дат из ячейки (вытаскивание, транспонирование да (Формулы/Formulas)

​ конечно.​​, в Excel отобразится​ «A» указывает на​Лет в виде чисел​В списке​Дата​Советы по отображению даты​ настроить в соответствии​
​ при пересчете листа​ (на английском языке).​ не обязательный. Им​
​ даты и времени​ и жмем Ctrl+H​ дату отдельно в​ виде даты подойдет​=—ТЕКСТ(A3;»чч:мм»)​: Чем формула от​_Boroda_​ числовой код. Если​ время суток от​
​ 1900 от 9999​
​Числовые форматы​
​или​
​ и времени​
​ со своими потребностями.​
​ или открытии книги,​Предположим, что требуется легко​ является число, которое​ в Excel.​Найти: * (пробел​ каждой ячейки в​ ГОД() МЕСЯЦ() ДЕНЬ()​
​wasya​
​китина​
​: Или​
​ снова ввести дату​
​ полуночи до полудня,​
​гггг​
​выберите пункт​
​Время​

​Можно форматировать значения даты​​Примечание:​
​ считаются «динамическими», а​
​ указывать текущие дату​

excelworld.ru

Как убрать время из ячейки (время + дата)

​ определяет тип отсчета​​В качестве примера использования​ и звездочка)​ столбец по порядку,​а для текста​
​: Вариант от SAS888​не подошла?​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОСТАТ(A1;1)​ или время, будет​ а «PM» или​Если «м» стоит сразу​
​(все форматы)​.​ и времени при​ Функции​
​ не статическими. Наиболее​ и время при​ недели. Если второй​ функции​
​Заменить все​

​ а последняя должна​​ в виде 24​ подходит на все​Форматом Вы изменяете​Или, если нужно​

​ использован формат даты​​ «P» — на время​
​ после кода «ч»​

​. В поле​В списке​
​ вводе. Например, если​СЕГОДНЯ​
​ распространенным способом получения​

CyberForum.ru

​ записи действий в​

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Добрый день,  Есть дата 31.01.2014 18:00:00, нужно 31.01.2014 занести в ячейку А1 а 18:00:00 в ячейку А2. Приложил свои потуги. Прикрепленные файлы Пример.xlsm (19.69 КБ)

wowick Пользователь Сообщений: 972 Регистрация: 14.01.2014	Округл(ДатаВремя;0) — будет дата Остат(ДатаВремя;1) — будет время. Если автоматизировать бардак, то получится автоматизированный бардак.

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Спасибо конечно, но хотелось бы средствами VBA

wowick Пользователь Сообщений: 972 Регистрация: 14.01.2014	Так замените названия на английские и пропишите в макросе… Если автоматизировать бардак, то получится автоматизированный бардак.

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Формулы приму к сведению, на крайняк их вставлю. Разве нету никакой функции в VBA?

lexey_fan Пользователь Сообщений: 436 Регистрация: 01.01.1970	#6 15.02.2014 13:33:08 Код xltime = Right(Now, 8) xlDat = Left(Now, 10) для вашего примера Код Sub Date_Time() Dim d As Date Dim q As Date d = Cells(2, 1).Value Cells(4, 1) = Right(d, 8) Cells(5, 1) = Left(d, 10) End Sub Изменено: lexey_fan — 15.02.2014 13:36:32 Если очень захотеть — можно в космос полететь

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Спасибо ,так понимаю ,то что получилось будет в текстовом формате ?

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Именно для даты нету никакой функции ?

lexey_fan Пользователь Сообщений: 436 Регистрация: 01.01.1970	#9 15.02.2014 13:43:15 вот так будет формат даты Код Sub Date_Time() Dim d As Date Dim q As Date d = Cells(2, 1).Value q = Right(d, 8) Cells(4, 1) = q q = Left(d, 10) Cells(5, 1) = q End Sub Изменено: lexey_fan — 15.02.2014 13:43:30 Если очень захотеть — можно в космос полететь

gling Пользователь Сообщений: 4024 Регистрация: 01.01.1970	А формат ячейки не устраивает? Потом будете решать как суммировать время при переходе суток. Изменено: gling — 15.02.2014 13:56:57

Sanja Пользователь Сообщений: 14838 Регистрация: 10.01.2013	#11 15.02.2014 13:51:40   Код tm = TimeValue(Now)    'системное время dt = DateValue(Now)      'системная дата Согласие есть продукт при полном непротивлении сторон.

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Есть какая либо функция по типу A = 31.01.2014 18:00:00 b = Функция(A, hh mm ss) c = Функция(A. yyyy mm dd) чтобы: b= 18:00:00 c= 18.01.2014 Изменено: luppi — 15.02.2014 13:54:21

Sanja Пользователь Сообщений: 14838 Регистрация: 10.01.2013	См пост #11. Замените Now на Ваше значение. Согласие есть продукт при полном непротивлении сторон.

luppi Пользователь Сообщений: 44 Регистрация: 11.12.2013	Ок, это то что нужно, хотя все способы в разных ситуациях подойдут. Sanja, ты в точку попал, спасибо. Остальным тоже спасибо.

Михаил С. Пользователь Сообщений: 10514 Регистрация: 21.12.2012	#15 15.02.2014 14:11:09 Цитата Sanja пишет: tm = TimeValue(Now) ‘системное время dt = DateValue(Now) ‘системная дата Код tm = Time 'системное время dt = Date 'системная дата

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	#16 09.08.2021 16:13:10 dovos, создайте отдельную тему с названием, отражающим суть задачи

Может ли кто дать совет каким образом можно отделить дату от времени в таблице EXL
— «16.12.2009  10:00:00» таким образом чтобы получилось два отдельных значения «16.12.2009» и  «10:00:00» в разных ячейках. Формулы типа «=правсимв( )» и «=левсимв( )» не решают задачу. Пример массива:
16.12.2009 9:00   1,4519
16.12.2009 10:00   1,4548
16.12.2009 11:00   1,4555
16.12.2009 12:00   1,4552
16.12.2009 13:00   1,4569
16.12.2009 14:00   1,4563
16.12.2009 15:00   1,4546
16.12.2009 16:00   1,4576
16.12.2009 17:00   1,4579
16.12.2009 18:00   1,4573
16.12.2009 19:00   1,4538
16.12.2009 20:00   1,454
16.12.2009 21:00   1,4513
16.12.2009 22:00   1,453
16.12.2009 23:00   1,4531
……..

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

---

Разделить дату и время с помощью формул

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

1. Выберите диапазон столбцов, в который вы хотите поместить только результаты даты, и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек в контекстном меню. См. Снимок экрана:

2. В диалоговом окне Формат ячеек на вкладке “Число” нажмите Дата в разделе Категория и перейдите к Тип , чтобы выбрать нужный тип даты. См. Снимок экрана:

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

4. Нажмите OK , чтобы закрыть диалоговое окно Форматирование ячеек . Затем в первой ячейке столбца Date (кроме заголовка) введите эту формулу = INT (A2) (A2 – это ячейка, которую нужно разделить), затем перетащите маркер заполнения в диапазон, необходимый для применения этой формулы. См. Скриншоты:

5. Перейдите в первую ячейку столбца «Время» (кроме заголовка) и введите эту формулу = A2-C2 (A2 – это ячейка, по которой вы разделены, а C2 – ячейка даты), и перетащите дескриптор заполнения в нужный диапазон. См. Скриншоты:

Затем дата и время было разделено на две ячейки.

---

Разделение даты и времени с помощью Kutools for Excel (3 шага с щелчками)

Самый простой и Удобный метод разделения даты и времени, без сомнения, использует утилиту Split Cells из Kutools for Excel !

Kutools for Excel , с более чем 300 удобными функциями, упрощает вашу работу.

Бесплатная загрузка Полная будущая 30-дневная бесплатная пробная версия

После установки Kutools for Excel, пожалуйста, сделайте следующее: （Бесплатная загрузка Kutools for Excel сейчас!)

1. Выберите ячейки даты и времени и нажмите Kutools > Объединить и разделить > Разделить ячейки . См. Снимок экрана:

2. В диалоговом окне Разделить ячейки установите флажки Разделить на столбцы и Пробел . См. Снимок экрана:

3. Нажмите ОК , выберите ячейку для вывода разделенных значений и нажмите ОК . См. Снимок экрана:

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

Совет. С помощью утилиты “Разделить ячейки” , вы можете разделить одну ячейку на строки или столбцы по разделителю или фиксированной ширине. Если вы хотите получить бесплатную пробную версию функции Split Cells, сначала перейдите на бесплатную пробную версию Kutools for Excel, а затем примените операцию в соответствии с вышеуказанными шагами.

Split Ячейки

---

Удалить время из DateTime

В Excel , чтобы удалить 12:11:31 из 21.01.2017 12:11:31 и сделать это точно 21.01.2017, вам может потребоваться некоторое время, чтобы создать формулу для выполнения этой задачи. Однако утилита «Удалить время из даты» в Kutools for Excel может быстро удалить временную метку навсегда из форматирования даты и времени в Excel. Нажмите, чтобы загрузить 30-дневную бесплатную пробную версию .

---

Разделить дату и время с помощью извлечения текста

С помощью удобной утилиты – Извлечь текст из Kutools for Excel , вы также можете быстро извлечь дату и время из одного столбца в два столбца.

После установка Kutools for Excel, пожалуйста, сделайте следующее: （Бесплатная загрузка Kutools for Excel сейчас!)

1. Во-первых, вам нужно отформатировать ячейки как дату и время. Выберите один диапазон столбцов и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню.. Затем в диалоговом окне Формат ячеек нажмите Дата в разделе Категории и выберите один тип даты. См. Скриншоты:

2. Нажмите ОК . И перейдите к другому диапазону столбцов и отформатируйте его как время. см. снимок экрана:

3. Выделите ячейки даты и времени (кроме заголовка) и нажмите Kutools > Текст > Извлечь текст . См. Снимок экрана:

4. Затем в диалоговом окне Извлечь текст введите * и пробел в поле Текст , затем нажмите Добавить , чтобы добавить его. в список Извлечь . См. Скриншоты:

5. Нажмите ОК и выберите ячейку для размещения дат. См. Снимок экрана:

6. Нажмите ОК . Вы можете видеть, что данные были извлечены.

7. Снова выберите дату и время и нажмите Kutools > Текст > Извлечь текст . Удалите все критерии из списка Извлечь, введите пробел и * в поле Текст и добавьте его в список Извлечь . См. Скриншоты:

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

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

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

Разделить дату и время

---

Разделить дату и время с текстом на Столбец

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

1. Выберите диапазон данных и нажмите Данные > Текст в столбцы , затем в открывшемся диалоговом окне установите флажок С разделителями .. См. Снимок экрана:

2. Затем нажмите Далее , чтобы открыть диалоговое окно Convert Text to Column Wizard Step 2 of 3 , установите флажок Пробел (вы также можете при необходимости выберите другие разделители) в разделе Разделители . См. Снимок экрана:

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

4. Затем в диалоговом окне Формат ячеек нажмите Дата в разделе Категории и выберите нужный тип даты в разделе Тип . См. Снимок экрана:

5. Нажмите ОК . Теперь результат показан ниже:

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

---

Как разделить текст по столбцам, как разделить ячейки в Excel

Как в excel 2007 разделить текст по столбцам на две, три и более ячеек. Обычно для примера используют разбивку ячеек с ФИО, чтоб данные были раздельно.

Как разделить текст по столбцам в экселе

Мне по работе пришлось разделять время в ячейках таблицы excel , пример: 0:00-15:00. А между значениями разделитель точка с запятой 0:00-15:00;16:00-17:00;22:00-24:00. Эти данные в таблице по дням, в ячейках разное количество значений между разделителем. Необходимо было разделить время по каждому дню, в столбик под датой проставить время, разделить время на начало и конец, посчитать разницу и все сложить вместе.

Этап первый. Разделить время в ячейках

Выделяем диапазон ячеек, на ленте нажимаем Данные-Текст по столбцам, появится Мастер текстов.

Ничего не меняем, если у вас переключатель стоит в положении: с разделителем, жмем далее.

Снимаем галку «знак табуляция», ставим галку «другой»

и в окошко вносим точку с запятой [ ; ]. Жмем либо далее, хотя там особо смотреть не чего, верней нажать готово. Будет задан вопрос: заменить содержимое ячеек? Ответ да!

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

Две ячейки-две строки. Выделяем ячейки, копируем,

и под первой ячейкой жмем правой кнопкой «Специальная вставка»- «Транспонировать».

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

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

Этап второй. Как в экселе разделить ячейку на время начала и конца

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

Вынесем столбец за нашу таблицу, скопировав и вставив ячейки рядом. Работаем с мастером: Данные-Текст по столбцам , шаг первый ничего не меняем, на втором шаге в окошке другой [ — ] ставим минус, нажимаем готово.

Этап третий

Ставим формулу: время конца минус время начала,

формат ячеек Все форматы [ч]:мм,

протянем формулу вниз до конца. Мы получили время в часах. В соседней ячейке напишем: =К4*60 Enter. Получим время в минутах.

Правда с секундами. Не нашел способа лучше, чем скопировать столбец, вставить его в блокнот, нажать CTRL+H (Найти и заменить), в найти написал :00

а заменить оставил пустым.

Нажал заменить все. Текст без :00 скопировал из блокнота и вставил в таблицу.

С количеством часов поступил проще, скопировал в блокнот и вставил в таблицу. Одна проблема: 24 часа стоят как 24:00:00.

Не проблема, раз мы знаем, как в экселе разделить ячейку, то тут можно и Copy&Paste применить, но с хитростью — в ячейке перед 24:00 поставить апостроф ‘ -одиночную кавычку ’24:00

и вставить всюду. Складывать автосуммой можно только минуты, сумму часов получим, разделив сумму минут на 60, а количество дней разделим полученное число часов на 24.

В моем примере нечто из 31 дня проработало 28 дней,из 744 часов -673 часа и из 44640 минут -40411 минут. Не важно что это, главное что на этом примере вы узнали как как в экселе разделить текст по столбцам в ячейке, как можно не стандартно использовать блокнот и менять формат ячеек, добавляя к ним апостроф.

В этом видео подробно показано, как разделить текст по столбцам:

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

Функция ВРЕМЯ() в MS EXCEL

Рассмотрим функцию ВРЕМЯ() , у которой 3 аргумента: часы, минуты, секунды. Записав формулу =ВРЕМЯ(10;30;0) , получим в ячейке значение 10:30:00 в формате Время. Покажем, что число 0,4375 соответствует 10:30 утра.

Функция ВРЕМЯ() , английский вариант TIME(), переводит значения часов, минут и секунд в формат времени.

В статье Как EXCEL хранит дату и время показано, что любому значению времени в MS EXCEL сопоставлено число. Например, если формат ячейки, содержащей число 0,5 перевести в формат Время, то 0,5 будет преобразовано в 12:00:00 утра. Числа, соответствующие времени, принадлежат интервалу от 0 (0:00:00) до 0,99999 (23:59:59).

Синтаксис функции

ВРЕМЯ(часы;минуты;секунды)

Часы — число от 0 до 32767, задающее часы. Если значение больше 23, оно делится на 24; остаток от деления будет соответствовать значению часов. Например, ВРЕМЯ(27;0;0) = ВРЕМЯ(3;0;0) = 0,125, то есть 03:00 утра.

Минуты — число от 0 до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. Например, ВРЕМЯ(0;750;0) = ВРЕМЯ(12;30;0) = 0,520833, то есть 12:30.

Секунды — число от 0 до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды. Например, ВРЕМЯ(0;0;2000) = ВРЕМЯ(0;33;22) = 0,023148, то есть 12:33:20.

Все аргументы функции — обязательные. Если секунды не важны, то можно использовать такую запись =ВРЕМЯ(10;30;)

Пример1 — используем ссылки

Пусть в столбце А содержатся часы, в столбце B содержатся минуты.

Формула =ВРЕМЯ(A2;B2;) в ячейке С2 вернет значение времени в числовом формате (0,88 для 21:00). Чтобы отобразить значение в стандартном формате времени чч:мм, вызовите Диалоговое окно Формат ячеек, нажав CTRL+1, выберите числовой формат Время, затем выберите в поле Тип — подходящий формат.

Пример2 — прибавляем минуты

Прибавим к 13:23 (значение находится в ячейке A1) 75 минут. Для этого в ячейке B1 запишем формулу =A1+ВРЕМЯ(;75;)

Формула вернет значение 14:38

Пример3 — вычисляем количество секунд

Пусть в ячейке содержится значение времени 0:20:35, Т.е. 20 минут и 35 секунд. Вычислим сколько это значение содержит секунд.

С помощью формулы =ВРЕМЯ(0;20;35) введем в ячейку А1 указанное значение времени. Этому значению времени соответствует число 0,014294.

Вспомним, что 1 секуннда в числовом формате — это 1/24/60/60.

Чтобы вычислить количество секунд, разделим значение в ячейке А1 на 1/24/60/60. Для этого запишем формулу =A1/(1/24/60/60) .

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

Вставка текущих даты и времени в ячейку

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

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

Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Поскольку значение ячейки не меняется, оно считается статичным.

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

Выполните одно из действий, указанных ниже.

Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).

Чтобы вставить текущее время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).

Чтобы вставить текущие дату и время, нажмите клавиши CTRL+; (точка с запятой), затем клавишу ПРОБЕЛ, а после этого — CTRL+SHIFT+; (точка с запятой).

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

Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите команду Формат ячеек. Затем в диалоговом окне Формат ячеек на вкладке число в разделе Категориявыберите пункт Дата или время , а затем в списке тип выберите тип и нажмите кнопку ОК.

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

Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Поскольку значение ячейки не меняется, оно считается статичным.

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

Выполните одно из действий, указанных ниже.

Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).

Чтобы вставить текущее время, нажмите клавиши +; (точка с запятой).

Чтобы вставить текущие дату и время, нажмите клавиши CTRL +; (точка с запятой), нажмите клавишу пробел, а затем — клавишу +; (точка с запятой).

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

Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите команду Формат ячеек. Затем в диалоговом окне Формат ячеек на вкладке число в разделе Категориявыберите пункт Дата или время , а затем в списке тип выберите тип и нажмите кнопку ОК.

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

Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Поскольку значение ячейки не меняется, оно считается статичным.

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

Выполните одно из действий, указанных ниже.

Чтобы вставить дату, введите дату (например, 2/2) и нажмите кнопку раскрывающегося списка Формат номерадома > (на вкладке число ) >Краткая дата или длинный день.

Чтобы вставить время, введите время и нажмите кнопку раскрывающегося списка Формат номерадома > (на вкладке число ) >time (время).

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

Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите пункт Числовой Формат. Затем в диалоговом окне Формат номера в разделе Категориявыберите значение Дата или время , а затем в списке тип выберите тип и нажмите кнопку ОК.

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

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

Для вставки обновляемой текущей даты или времени используются функции СЕГОДНЯ и ТДАТА, как показано в приведенном ниже примере. Дополнительные сведения об использовании этих функций см. в статьях Функция СЕГОДНЯ и Функция ТДАТА

Текущая дата (значения могут быть разными)

Текущие дата и время (значения могут быть разными)

Выделите текст в таблице, приведенной выше, и нажмите клавиши CTRL+C.

На пустом листе выберите один раз в ячейке a1, а затем нажмите клавиши CTRL + V. Если вы работаете в Excel Online, повторите копирование и вставку для каждой ячейки в примере.

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

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши Ctrl+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, можно настроить его в соответствии со своими потребностями.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Как в экселе дату разбить на 3 столбца?

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

Первый шаг. Сначала отделим от общей даты только число, т.е. нужно взять от неё первые два символа, обычно такую задачу решает функция ЛЕВСИМВ. Поэтому в ячейке «В2» пишем формулу =ЛЕВСИМВ(A2;2).

Второй шаг. Получаемый результат нас не устраивает, так как функция отделила не дату, а символы от числа. В программе эксель, любая дата, на самом деле представляется числом, которое просто отображается в виде даты. Поэтому нужно воспользоваться функцией ТЕКСТ, которое преобразует число в текст, при этом мы зададим следующий формат данного текста: ДД.ММ.ГГГГ. В итоге в ячейке «В2» пишем новую формулу =ЛЕВСИМВ(ТЕКСТ(A2;»ДД.ММ.ГГГГ»);2). И результат нас уже устроит.

Третий шаг. Теперь отделим от общего числа год, т.е. последние четыре символа. Воспользуемся функцией ПРАВСИМВ и ТЕКСТ. Пишем в ячейке «D2» формулу: =ПРАВСИМВ(ТЕКСТ(A2;»ДД.ММ.ГГГГ»);4).

Четвертый шаг. Чтобы отделить месяц, придется использовать три функции из второго и третьего шага. В ячейке «С2» пишем формулу =ПРАВСИМВ(ЛЕВСИМВ(ТЕКСТ(A2;»ДД.ММ.ГГГГ»);5);2).

Пятый шаг. Выделяем диапазон ячеек с «В2» по «D2», нажимаем клавиши «Ctrl+C», после выделяем диапазон с «В3» по «D4» и нажимаем «Ctrl+V». В итоге мы все даты разбили на три столбца.

Функции для извлечения различных параметров из дат и времени в Excel

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

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

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

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

В ячейке A1 представлена дата в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это пользовательский (нестандартный) числовой формат представления даты и времени в Excel.

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

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

Возвращает номер месяца, соответствующий заданной дате. Месяц возвращается как целое число в диапазоне от 1 до 12.

Возвращает значение дня, соответствующее заданной дате. День возвращается как целое число в диапазоне от 1 до 31.

Данная функция возвращает значение часа, соответствующее заданному времени. Час возвращается как целое число в диапазоне от 0 до 23.

Возвращает количество минут, соответствующее заданному времени. Минуты возвращаются как целое число в диапазоне от 0 до 59.

Возвращает количество секунд, соответствующее заданному времени. Секунды возвращаются как целое число в диапазоне от 0 до 59.

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

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

Если в качестве второго аргумента функции ДЕНЬНЕД подставить 2, то дням недели будут соответствовать номера от 1 (понедельник) до 7 (воскресенье). В итоге функция вернет нам значение 6:

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

Содержание

• 1 ГОД()
• 2 МЕСЯЦ()
• 3 ДЕНЬ()
• 4 ЧАС()
• 5 МИНУТЫ()
• 6 СЕКУНДЫ()
• 7 ДЕНЬНЕД()
• 8 Работа с функциями даты и времени
  • 8.1 ДАТА
  • 8.2 РАЗНДАТ
  • 8.3 ТДАТА
  • 8.4 СЕГОДНЯ
  • 8.5 ВРЕМЯ
  • 8.6 ДАТАЗНАЧ
  • 8.7 ДЕНЬНЕД
  • 8.8 НОМНЕДЕЛИ
  • 8.9 ДОЛЯГОДА
  • 8.10 Помогла ли вам эта статья?
• 9 Как поставить текущую дату в Excel
• 10 Как установить текущую дату в Excel на колонтитулах
  • 10.1 Видео
  • 10.2 Как вводить даты и время в Excel
  • 10.3 Быстрый ввод дат и времени
  • 10.4 Как Excel на самом деле хранит и обрабатывает даты и время
  • 10.5 Количество дней между двумя датами
  • 10.6 Количество рабочих дней между двумя датами
  • 10.7 Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.
  • 10.8 Сдвиг даты на заданное количество дней
  • 10.9 Сдвиг даты на заданное количество рабочих дней
  • 10.10 Вычисление дня недели
  • 10.11 Вычисление временных интервалов
  • 10.12 Ссылки по теме

Может ли кто дать совет каким образом можно отделить дату от времени в таблице EXL
— «16.12.2009  10:00:00» таким образом чтобы получилось два отдельных значения «16.12.2009» и  «10:00:00» в разных ячейках. Формулы типа «=правсимв( )» и «=левсимв( )» не решают задачу. Пример массива:
16.12.2009 9:00   1,4519
16.12.2009 10:00   1,4548
16.12.2009 11:00   1,4555
16.12.2009 12:00   1,4552
16.12.2009 13:00   1,4569
16.12.2009 14:00   1,4563
16.12.2009 15:00   1,4546
16.12.2009 16:00   1,4576
16.12.2009 17:00   1,4579
16.12.2009 18:00   1,4573
16.12.2009 19:00   1,4538
16.12.2009 20:00   1,454
16.12.2009 21:00   1,4513
16.12.2009 22:00   1,453
16.12.2009 23:00   1,4531
……..

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

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

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

ГОД()

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

В ячейке A1 представлена дата в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это пользовательский (нестандартный) числовой формат представления даты и времени в Excel.

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

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

МЕСЯЦ()

Возвращает номер месяца, соответствующий заданной дате. Месяц возвращается как целое число в диапазоне от 1 до 12.

ДЕНЬ()

Возвращает значение дня, соответствующее заданной дате. День возвращается как целое число в диапазоне от 1 до 31.

ЧАС()

Данная функция возвращает значение часа, соответствующее заданному времени. Час возвращается как целое число в диапазоне от 0 до 23.

МИНУТЫ()

Возвращает количество минут, соответствующее заданному времени. Минуты возвращаются как целое число в диапазоне от 0 до 59.

СЕКУНДЫ()

Возвращает количество секунд, соответствующее заданному времени. Секунды возвращаются как целое число в диапазоне от 0 до 59.

ДЕНЬНЕД()

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

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

Если в качестве второго аргумента функции ДЕНЬНЕД подставить , то дням недели будут соответствовать номера от 1 (понедельник) до 7 (воскресенье). В итоге функция вернет нам значение :

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

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

1. Для введения формулы через Мастер функций выделите ячейку, где будет выводиться результат, а затем сделайте щелчок по кнопке «Вставить функцию». Расположена она слева от строки формул.
2. После этого происходит активация Мастера функций. Делаем клик по полю «Категория».
3. Из открывшегося списка выбираем пункт «Дата и время».
4. После этого открывается перечень операторов данной группы. Чтобы перейти к конкретному из них, выделяем нужную функцию в списке и жмем на кнопку «OK». После выполнения перечисленных действий будет запущено окно аргументов.

Кроме того, Мастер функций можно активировать, выделив ячейку на листе и нажав комбинацию клавиш Shift+F3. Существует ещё возможность перехода во вкладку «Формулы», где на ленте в группе настроек инструментов «Библиотека функций» следует щелкнуть по кнопке «Вставить функцию».

Имеется возможность перемещения к окну аргументов конкретной формулы из группы «Дата и время» без активации главного окна Мастера функций. Для этого выполняем перемещение во вкладку «Формулы». Щёлкаем по кнопке «Дата и время». Она размещена на ленте в группе инструментов «Библиотека функций». Активируется список доступных операторов в данной категории. Выбираем тот, который нужен для выполнения поставленной задачи. После этого происходит перемещение в окно аргументов.

Урок: Мастер функций в Excel

ДАТА

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

Его аргументами являются «Год», «Месяц» и «День». Особенностью обработки данных является то, что функция работает только с временным отрезком не ранее 1900 года. Поэтому, если в качестве аргумента в поле «Год» задать, например, 1898 год, то оператор выведет в ячейку некорректное значение. Естественно, что в качестве аргументов «Месяц» и «День» выступают числа соответственно от 1 до 12 и от 1 до 31. В качестве аргументов могут выступать и ссылки на ячейки, где содержатся соответствующие данные.

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

=ДАТА(Год;Месяц;День)

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

РАЗНДАТ

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

=РАЗНДАТ(нач_дата;кон_дата;единица)

Из контекста понятно, что в качестве аргументов «Начальная дата» и «Конечная дата» выступают даты, разницу между которыми нужно вычислить. А вот в качестве аргумента «Единица» выступает конкретная единица измерения этой разности:

• Год (y);
• Месяц (m);
• День (d);
• Разница в месяцах (YM);
• Разница в днях без учета годов (YD);
• Разница в днях без учета месяцев и годов (MD).

Урок: Количество дней между датами в Excel

ЧИСТРАБДНИ

В отличии от предыдущего оператора, формула ЧИСТРАБДНИ представлена в списке Мастера функций. Её задачей является подсчет количества рабочих дней между двумя датами, которые заданы как аргументы. Кроме того, имеется ещё один аргумент – «Праздники». Этот аргумент является необязательным. Он указывает количество праздничных дней за исследуемый период. Эти дни также вычитаются из общего расчета. Формула рассчитывает количество всех дней между двумя датами, кроме субботы, воскресенья и тех дней, которые указаны пользователем как праздничные. В качестве аргументов могут выступать, как непосредственно даты, так и ссылки на ячейки, в которых они содержатся.

Синтаксис выглядит таким образом:

=ЧИСТРАБДНИ(нач_дата;кон_дата;)

ТДАТА

Оператор ТДАТА интересен тем, что не имеет аргументов. Он в ячейку выводит текущую дату и время, установленные на компьютере. Нужно отметить, что это значение не будет обновляться автоматически. Оно останется фиксированным на момент создания функции до момента её перерасчета. Для перерасчета достаточно выделить ячейку, содержащую функцию, установить курсор в строке формул и кликнуть по кнопке Enter на клавиатуре. Кроме того, периодический пересчет документа можно включить в его настройках. Синтаксис ТДАТА такой:

=ТДАТА()

СЕГОДНЯ

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

=СЕГОДНЯ()

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

ВРЕМЯ

Основной задачей функции ВРЕМЯ является вывод в заданную ячейку указанного посредством аргументов времени. Аргументами этой функции являются часы, минуты и секунды. Они могут быть заданы, как в виде числовых значений, так и в виде ссылок, указывающих на ячейки, в которых хранятся эти значения. Эта функция очень похожа на оператор ДАТА, только в отличии от него выводит заданные показатели времени. Величина аргумента «Часы» может задаваться в диапазоне от 0 до 23, а аргументов минуты и секунды – от 0 до 59. Синтаксис такой:

=ВРЕМЯ(Часы;Минуты;Секунды)

Кроме того, близкими к этому оператору можно назвать отдельные функции ЧАС, МИНУТЫ и СЕКУНДЫ. Они выводят на экран величину соответствующего названию показателя времени, который задается единственным одноименным аргументом.

ДАТАЗНАЧ

Функция ДАТАЗНАЧ очень специфическая. Она предназначена не для людей, а для программы. Её задачей является преобразование записи даты в обычном виде в единое числовое выражение, доступное для вычислений в Excel. Единственным аргументом данной функции выступает дата как текст. Причем, как и в случае с аргументом ДАТА, корректно обрабатываются только значения после 1900 года. Синтаксис имеет такой вид:

=ДАТАЗНАЧ (дата_как_текст)

ДЕНЬНЕД

Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:

=ДЕНЬНЕД(Дата_в_числовом_формате;)

НОМНЕДЕЛИ

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

=НОМНЕДЕЛИ(дата;)

ДОЛЯГОДА

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

=ДОЛЯГОДА(нач_дата;кон_дата;)

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Да Нет

Самый простой и быстрый способ ввести в ячейку текущую дату или время – это нажать комбинацию горячих клавиш CTRL+«;» (текущая дата) и CTRL+SHIFT+«;» (текущее время).

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

Чтобы вставить текущую дату в Excel воспользуйтесь функцией СЕГОДНЯ(). Для этого выберите инструмент «Формулы»-«Дата и время»-«СЕГОДНЯ». Данная функция не имеет аргументов, поэтому вы можете просто ввести в ячейку: «=СЕГОДНЯ()» и нажать ВВОД.

Текущая дата в ячейке:

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

Текущая дата и время в ячейке.

Как установить текущую дату в Excel на колонтитулах

Вставка текущей даты в Excel реализуется несколькими способами:

1. Задав параметры колонтитулов. Преимущество данного способа в том, что текущая дата и время проставляются сразу на все страницы одновременно.
2. Используя функцию СЕГОДНЯ().
3. Используя комбинацию горячих клавиш CTRL+; – для установки текущей даты и CTRL+SHIFT+; – для установки текущего времени. Недостаток – в данном способе не будет автоматически обновляться значение ячейки на текущие показатели, при открытии документа. Но в некоторых случаях данных недостаток является преимуществом.
4. С помощью VBA макросов используя в коде программы функции: Date();Time();Now().

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

Чтобы сделать текущую дату в Excel и нумерацию страниц с помощью колонтитулов сделайте так:

1. Откройте окно «Параметры страницы» и выберите закладку «Колонтитулы».
2. Нажмите на кнопку создать нижний колонтитул.
3. В появившемся окне щелкните по полю «В центре:». На панели выберите вторую кнопку ««Вставить номер страницы»». Потом выберите первую кнопку «Формат текста» и задайте формат для отображения номеров страниц (например, полужирный шрифт, а размер шрифта 14 пунктов).
4. Для установки текущей даты и времени щелкните по полю «Справа:», а затем щелкните по кнопке «Вставить дату» (при необходимости щелкните на кнопку «Вставить время»). И нажмите ОК на обоих диалоговых окнах. В данных полях можно вводить свой текст.
5. Нажмите на кнопку ОК и обратите на предварительный результат отображения колонтитула. Ниже выпадающего списка «Нижний колонтитул».
6. Для предварительного просмотра колонтитулов перейдите в меню «Вид»-«Разметка страницы». Там же можно их редактировать.

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

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

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

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

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

«Классическая» форма	3.10.2006
Сокращенная форма	3.10.06
С использованием дефисов	3-10-6
С использованием дроби	3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

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

16:45

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

16:45:30

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

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

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

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!  😉
• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

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

Количество рабочих дней между двумя датами

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

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

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

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

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

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

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

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

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

В ячейке дата и время. Как убрать дату, в время оставить?

Kolomoets Дата: Пятница, 02.05.2014, 13:58 | Сообщение № 1 Группа: Пользователи Ранг: Новичок Сообщений: 15 Репутация: 0 ± Замечаний: 0% ± Здравствуйте, Пример документа приложил, суть вопроса собственно влезла в тему. В ячейке дата и время. Как убрать дату, а время оставить? Сразу во всех строках. Спасибо заранее. К сообщению приложен файл: Vremya.xlsx (16.5 Kb) Сообщение отредактировал Kolomoets — Пятница, 02.05.2014, 13:58   Ответить

Gustav Дата: Пятница, 02.05.2014, 14:02 | Сообщение № 2 Группа: Друзья Ранг: Старожил Сообщений: 2398 Репутация: 986 ± Замечаний: 0% ± начинал с Excel 4.0, видел 2.1 В ячейку B1 формулу: Смысл в том, что полная дата с временем — это вещественное число, в котором целая часть — это дата, а дробная — время в течение суток. При соответствующем формате ячейки, конечно. МОИ: Ник, Tip box: 41001663842605 Сообщение отредактировал Gustav — Пятница, 02.05.2014, 14:05   Ответить

_Boroda_ Дата: Пятница, 02.05.2014, 14:28 | Сообщение № 3 Группа: Модераторы Ранг: Местный житель Сообщений: 16618 Репутация: 6465 ± Замечаний: 0% ± 2003; 2007; 2010; 2013 RUS Или Или, если нужно прямо в ячейке, то жмите Контрл h, в «Найти» пишите /* / без слешей (звездочка и 2 пробела), в «Заменить на» ничего не пишите, «Заменить все» почему-то тут 2 пробела подряд преобразуются в один Скажи мне, кудесник, любимец ба’гов… Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995   Ответить

Kolomoets Дата: Пятница, 02.05.2014, 14:59 | Сообщение № 4 Группа: Пользователи Ранг: Новичок Сообщений: 15 Репутация: 0 ± Замечаний: 0% ± Спасибо огромное, то что нужно. Вопрос решен.   Ответить

Юлия Дата: Пятница, 16.05.2014, 10:07 | Сообщение № 5 А как сделать наоборот? Убрать время,а дату оставить.   Ответить

китин Дата: Пятница, 16.05.2014, 10:16 | Сообщение № 6 Группа: Модераторы Ранг: Экселист Сообщений: 6973 Репутация: 1063 ± Замечаний: 0% ± Excel 2007;2010;2016 формат ячейки дата [p.s.]первый раз поспешил Не судите очень строго:я пытаюсь научиться ЯД 41001877306852 Сообщение отредактировал китин — Пятница, 16.05.2014, 10:18   Ответить

Юлия Дата: Пятница, 16.05.2014, 10:21 | Сообщение № 7 Не поняла..)   Ответить

Юлия Дата: Пятница, 16.05.2014, 10:23 | Сообщение № 8 Я выделяю ячейки, выбираю формат ячейки «дата», отображается только дата, но в строке формулы всё равно остается время.   Ответить

китин Дата: Пятница, 16.05.2014, 10:24 | Сообщение № 9 Группа: Модераторы Ранг: Экселист Сообщений: 6973 Репутация: 1063 ± Замечаний: 0% ± Excel 2007;2010;2016 К сообщению приложен файл: 7777777.xlsx (34.4 Kb) Не судите очень строго:я пытаюсь научиться ЯД 41001877306852   Ответить

Юлия Дата: Вторник, 20.05.2014, 15:46 | Сообщение № 10 Добрый день. Ну не получается у меня)) Имеются ячейки D4,D5 и т.д., в них даты и время 28.02.2014 22:33:00, 22.04.2014 11:22:00 и т.д. Как мне убрать время во всех ячейках? Если я выделю ячейки и выберу формат «Дата», то будет отображаться дата, но в строке формулы всё равно будет время. Вот как от него избавиться, скажите пож-та?   Ответить

Pelena Дата: Вторник, 20.05.2014, 15:52 | Сообщение № 11 Группа: Админы Ранг: Местный житель Сообщений: 18797 Репутация: 4284 ± Замечаний: ± Excel 2016 & Mac Excel Чем формула от китина не подошла? Форматом Вы изменяете только внешнее представление данных, а истинное значение остаётся прежним «Черт возьми, Холмс! Но как??!!» Ю-money 41001765434816   Ответить

Юлия Дата: Вторник, 20.05.2014, 15:54 | Сообщение № 12 Я в ячейку ввожу формулу: =целое(D4) правильно? [moder]Нет, не правильно. Сначала прочитайте Правила форума. Приложите свой пример. А потом разговаривать будем.   Ответить

Получить день

Получить дату с номера дня

= ДАТА(год; 1; день)

Чтобы получить реальную дату из числа дня или «n-го числа года», вы можете использовать функцию ДАТА.

В показанном примере формула в C5:

= ДАТА(2015;1; B5)

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

Например, ДАТА возвращает 9 апреля 2016 года со следующими аргументами:

= ДАТА(2016;1;100)

В январе нет 100-го дня, поэтому ДАТА просто продвигается на 100 дней вперед с 1 января, а цифры возвращают правильную дату.

Формула на этой странице использует эту механику. В этом случае предполагается, что в этом году 2015 год, поэтому 2015 год жестко закодирован в течение года, а 1 используется в течение месяца. Значение дня выводится из столбца B, а функция ДАТА рассчитывает дату, как объяснено выше.

Если у вас есть дата в формате Юлиан, например 10015, где используется формат «дддгг», вы можете изменить формулу следующим образом:

= ДАТА(ПРАВСИМВ(A1;2); 1; ЛЕВСИМВ(A1;3))

Здесь мы используем ПРАВСИМВ, чтобы извлечь 2 символа справа за год, и ЛЕВСИМВ, чтобы извлечь 3 символа слева в течение дня. Месяц представлен как 1, как в первом примере.

Получить день с даты

= ДЕНЬ(дата)

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

Функция ДЕНЬ принимает только один аргумент — дату, из которой вы хотите извлечь день. В этом примере формула:

= ДЕНЬ (B3)

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 5, представляющее компонент дня даты.

Обратите внимание: вы можете использовать ДЕНЬ для извлечения дня из дня, введенного как текст:

= ДЕНЬ(«1/5/2016»)

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

Получить имя дня с даты

ТЕКСТ (B4; «дддд»)

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

Если вы хотите отображать имя дня, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату, затем перейдите к Форматирование ячеек (Ctrl + 1 или Cmd + 1)> Custom и введите один из этих настраиваемых форматов:

«ддд» // «сред»

«дддд» // «среда»

Excel отобразит только имя дня, но оно оставит неизменным значение даты.

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

= ТЕКСТ (B4; «ддд»)

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

Если вы не хотите использовать вспомогательную таблицу, вы также можете отобразить номер дня недели в название месяца непосредственно в функции ВЫБОР следующим образом:

= ВЫБОР (ДЕНЬНЕД(B4); «Вс»; «Пн»; «Вт»; «Ср»; «Чт»; «Пт»; «Сб»)

В этом случае введите имена дней недели, которые вы хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента. ДЕНЬНЕД будет извлекать номер буднего дня, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что ДЕНЬНЕД возвращает число 1-7, соответствующее заданному дню недели. С настройками по умолчанию, воскресенье = 1 и суббота = 7. Смотрите ДЕНЬНЕД для получения дополнительной информации.

ВЫБОР — это больше работы по настройке, но она также более гибкая, так как позволяет привязать дату к любым требуемым значениям (т. е. Вы можете использовать значения, которые являются пользовательскими, сокращенными, не сокращенными, разными языками и т. д.).

Получить дни до даты

= дата-СЕГОДНЯ ()

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

В примере D5 содержит эту формулу:

= B4-СЕГОДНЯ()

В Excel даты — это просто серийные номера. В стандартной системе дат для окон, основанной на 1900 году, где 1 января 1900 года является номером 1. Даты действительны до 9999, что является серийным номером 2 958 465. Это означает, что 1 января 2050 года серийный номер 54789.

В примере датой является 9 марта 2016 года, который является серийным номером 42 438. Так:

= B4-СЕГОДНЯ()

= 1 января 2050 — 27 апреля 2014 г.

= 54;789 — 42;438

= 12;351

Это означает, что до 1 января 2050 года, когда отсчитывается с 9 марта 2016 года, существует 13 033 дня.

Примечание: вам не нужно использовать функцию СЕГОДНЯ. Во втором примере формула в D6:

= B6-C6

В третьем примере одна и та же базовая формула используется вместе с оператором конкатенации (&) для встраивания вычисленных дней в простое текстовое сообщение:

= «Осталось всего» & B6-C6 & «дней!»

Поскольку между 10 декабря 2014 года и 25 декабря 2014 года существует 15 дней, результатом является следующее сообщение:

Осталось всего 15 дней!

Получить дни между датами

= Поздняя дата-Ранняя дата

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

В примере формула в ячейке D6 возвращает 365, поскольку между 1/1/1999 и 1/1/2000 есть 365 дней.

Даты в Excel — это просто серийные номера, которые начинаются 1 января 1900 года.

В примере формула в ячейке D6 просто вычитает числовое значение 1/1/1999 (36161) из числового значения 1/1/2000 (36526), чтобы получить результат 365. Шаги выглядят так:

= C5-B5

= 1/1 / 1999-1 / 1/2000

= 36161-36526

= 365

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

= СЕГОДНЯ() – ранняя дата

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

= Поздняя дата-СЕГОДНЯ()

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

Функция ДНИ, введенная в Excel 2013, также вычисляет дни между двумя датами.

Получайте дни между датами, игнорируя годы

= РАЗНДАТ (нач_дата; кон_дата; «гд»)

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

= РАЗНДАТ(B5; C5; «yd»)

Функция РАЗНДАТ может обрабатывать различные расчеты «разницы дат» для вычисления разницы между двумя датами в годах, месяцах и днях. РАЗНДАТ принимает 3 аргумента: начальную дату, конечную дату и единицу измерения, которая контролирует, какой результат будет возвращен.

В этом случае мы хотим, чтобы дни игнорировались годами, поэтому мы поставляем «yd» для единицы.

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

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

= Дата-ДЕНЬ(дата) +1

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

В показанном примере формула в ячейке B5:

= B5-ДЕНЬ(B5) +1

Функция ДЕНЬ возвращает значение дня для даты. В примере показано, что значение дня для даты в B5 (5 июня 2016 года) равно 5. Поскольку даты — это только порядковые номера в Excel, мы можем вычесть 5, а затем добавить 1, чтобы получить значение даты на 1 июня 2016 года.

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

= КОНМЕСЯЦА(дата; -2) +1

Чтобы получить первый день предыдущего месяца для данной даты, вы можете использовать простую формулу, основанную на функции КОНМЕСЯЦА.

В показанном примере формула в ячейке B5:

= КОНМЕСЯЦА(B5; -2) +1

Функция КОНМЕСЯЦА возвращает последний день месяца, основанный на заданной дате. Второй аргумент — это месяцы, в которых указывается, сколько месяцев в будущем или в прошлом нужно переместить перед возвратом последнего дня. Возвращаясь на 2 месяца, затем добавляя один день, мы можем рассчитать первый день предыдущего месяца с любой заданной даты.

В показанном примере месяцев указываются как -2, что заставляет КОНМЕСЯЦА возвращаться 4/30/2015. Затем добавляется 1 день для получения 5/1/2015.

Получить последний день месяца

= КОНМЕСЯЦА(дата; 0)

Самый простой способ подсчитать последний день месяца — использовать функцию КОНМЕСЯЦА, которая предназначена для этой цели.

В показанном примере формула в ячейке B5:

= КОНМЕСЯЦА(B5;0)

Второй аргумент (месяцы) функции КОНМЕСЯЦА позволяет вам получить последний день месяца в будущем или в прошлом месяце. Когда вы используете ноль в течение месяцев, КОНМЕСЯЦА вернет последний день месяца в том же месяце.

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

= КОНМЕСЯЦА(дата; -1)

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

= КОНМЕСЯЦА(дата; 1)

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

= ДАТА(ГОД (дата); МЕСЯЦ(дата) +1;0)

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

Получить последний рабочий день в месяц

=РАБДЕНЬ (КОНМЕСЯЦА(дата) + 1; -1)

Чтобы получить последний рабочий день в месяце, вы можете использовать функцию РАБДЕНЬ вместе с функцией КОНМЕСЯЦА.

В примере формула в C5:

= РАБДЕНЬ (КОНМЕСЯЦА (B5;0) + 1; -1)

Работая изнутри, функция КОНМЕСЯЦА получает последний день месяца любой даты. К этому результату мы добавляем 1, что приводит к первому дню следующего месяца.

Эта дата переходит в функцию РАБДЕНЬ как «дата начала», а также -1 для «дней». Функция РАБДЕНЬ автоматически отступает на 1 день, принимая во внимание любые выходные. В результате будет последний рабочий день месяца.

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

= РАБДЕНЬ(КОНМЕСЯЦА(B5;0) + 1; -1; праздничные дни)

Функция РАБДЕНЬ предполагает, что выходные — это суббота и воскресенье. Если вам необходимо настроить выходные дни, вы можете использовать функцию РАБДЕНЬ.МЕЖД.

Получить n-ый день года

= Дата-ДАТА (ГОД (дата); 1;0)

Если вы хотите получить день года с даты (например, n-го числа года), вы можете использовать формулу, которая использует функции ДАТА и ГОД вместе.

Например, с датой «1 июня 2016 года» в ячейке B4 следующая формула вернет 153:

= B4-ДАТА(ГОД (B4); 1;0)

То есть 1 июня 2016 года — 153-й день этого года.

Эта формула использует тот факт, что даты — это просто порядковые номера в Excel. Он определяет последний день предыдущего года и вычитает его, начиная с исходной даты B4. Результат — n-й день года.

Обратите внимание, что аргумент день в ДАТА предоставляется как ноль. Хорошей особенностью функции ДАТА является то, что она может обрабатывать значения ДЕНЬ, которые находятся «вне диапазона», и соответствующим образом корректировать результат. Когда мы даем ДАТА год, 1 месяц и 0 дней, функция ДАТА возвращает последний день предыдущего года.

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

= СЕГОДНЯ () — ДАТА (ГОД (СЕГОДНЯ ()); 1;0)

Получить день недели

Получить последний день недели

= Дата-ОСТАТ(дата-день; 7)

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

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

В примере формула B6 является датой 1/16/2015, а формула в C6:

= B6-ОСТАТ(B6-7;7)

Число 7 (числовой аргумент в функции ОСТАТ) представляет день недели (день), который вы хотите. Только помните, что 1 — это воскресенье, а 7 — суббота. Так принято в США.

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

= B6-ОСТАТ(B6-7;7)

= B6-ОСТАТ(42013;7)

= B6-6

= 42014

= 1/10 / 2015

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

= СЕГОДНЯ () — ОСТАТ(СЕГОДНЯ () — день; 7)

Примечание. Если дата имеет тот же день недели, дата будет возвращена.

Получить следующий день недели

= Дата + 7-ДЕНЬНЕД(дата + 7-день)

Если вы хотите найти следующий конкретный день недели (т.е. следующую среду, пятницу или понедельник) с определенной датой начала, вы можете использовать общую формулу, которая использует функцию ДЕНЬНЕД с чем-то, называемым «день недели» (день). С помощью этой формулы вы можете делать такие вещи:

• Получить следующую пятницу
• Получить следующую субботу
• Получить следующий вторник

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

В примере формула B6 является датой 1/16/2015, а формула в C6:

= B6 + 7-ДЕНЬНЕД(B6 + 7-2)

Число 2 представляет день недели (день), который вы хотите, в данном случае, понедельник.

Чтобы решить эту формулу, Excel сначала добавляет 7 к начальной дате в C6, а затем уменьшает B6 + 7-2 до 42025 (порядковый номер в системе даты Excel, который представляет 1/21/2015). Затем Excel вычисляет день недели для этой даты (4) и вычитает, что из исходной даты начала + 7 (42027 = 1/23/2015), чтобы получить окончательное значение даты 42013 = 1/9/2015

= B6 + 7-ДЕНЬНЕД(B6 + 7-2)

= 42027-ДЕНЬНЕД(42025)

= 42027-4

= 42023

= 1/9/2015

Если вы хотите получить следующий день недели с текущей даты, вы можете использовать функцию СЕГОДНЯ, например:

= СЕГОДНЯ () + 7-НЕДЕЛЯ (СЕГОДНЯ () + 7-день)

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

Получить n-й день недели в месяце

= Дата-ДЕНЬ(дата) + 1 + n * 7-ДЕНЬНЕД(дата-ДЕНЬ (дата) + 8-день)

Чтобы получить n-й день недели в месяце (т. е. первый вторник, третий вторник, четвертый четверг и т. д.), вы можете использовать формулу, основанную на функциях ДЕНЬ и НЕДЕЛЯ.

В показанном примере формула в E5:

= B5-ДЕНЬ(B5) + 1 + D5 * 7-ДЕНЬНЕД(B5-ДЕНЬ(B5) + 8-C5)

Во-первых, эта формула определяет «дату начала». «Первое число месяца» рассчитывается с помощью функции ДЕНЬ:

= B5-ДЕНЬ(B5) +1

Затем n * 7 дней до первого числа месяца, чтобы получить дату начала n недель с первого числа месяца. В общей форме формулы n представляет «n-ое».

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

ДЕНЬНЕД(B5-ДЕНЬ(B5) + 8-C5)

В ДЕНЬНЕД снова вычисляется первое число месяца, затем добавляются 8 дней и вычитается значение для день (день недели).

День недели сокращенно «день» в общей форме формулы. Это число от 1 (воскресенье) до 7 (суббота), которое может быть изменено по мере необходимости.

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

Получить последний день недели за месяц

= КОНМЕСЯЦА(дата; 0) + 1-ДЕНЬНЕД (КОНМЕСЯЦА(дата; 0) + 1-день)

Чтобы получить последний день недели за месяц (то есть в прошлую субботу, последнюю пятницу, последний понедельник и т. Д.), Вы можете использовать формулу, основанную на функциях КОНМЕСЯЦА и ДЕНЬНЕД.

В показанном примере формула в D5:

= КОНМЕСЯЦА(B5;0) + 1-ДЕНЬНЕД(КОНМЕСЯЦА(B5;0) + 1-C5)

Во-первых, эта формула определяет первый день следующего месяца * после * данной даты. Он делает это, используя КОНМЕСЯЦА, чтобы получить последний день месяца, а затем добавить один день:

= КОНМЕСЯЦА(B5;0) +1

Затем формула вычисляет количество дней, требуемых для «возврата назад» к последнему запрошенному буднему дню в предшествующем месяце (т. е. Месяце исходной даты):

ДЕНЬНЕД(КОНМЕСЯЦА(B5;0) + 1-C5)

В ДЕНЬНЕД КОНМЕСЯЦА снова используется для получения первого дня следующего месяца. С этой даты вычитается значение для дня недели, и результат вводится в ДЕНЬНЕД, который возвращает количество дней для отката.

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

В общем виде формулы вверху страницы день недели сокращенно «день». Это число от 1 (воскресенье) до 7 (суббота), которое можно изменить, чтобы получить другой день недели. Например, чтобы получить последний четверг месяца, установите значение день равным 5.

Получить первый понедельник до любой даты

= Дата-ДЕНЬНЕД (дата-2)

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

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

Понедельник 7

Вторник 1

Среда 2

Четверг 3

Пятница 4

Суббота 5

Воскресенье 6

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

= A1-ДЕНЬНЕД (A1-2)

= 4;2015 июня — ДЕНЬНЕД (4 июня015-2)

= 4;2015 июня — ДЕНЬНЕД(2 июня 2015 г.)

= 4 июня015-3

= Июнь 1;2015

Если вы считаете, что настройка дня недели запутанна и не интуитивна, вы используете альтернативную формулу, которая использует функцию ВЫБОР с жестко заданными значениями регулировки:

= A1-ВЫБОР(ДЕНЬНЕД(A1;2); 7;1;2;3;4;5;6)

В этой версии используется ДЕНЬНЕД, чтобы получить индекс для дня недели (с понедельника как 1), а затем ВЫБОР возвращает правильное количество дней для возврата.

Если вам нужно найти понедельник до сегодняшнего дня, просто используйте функцию СЕГОДНЯ на дату:

= СЕГОДНЯ () — ДЕНЬНЕД(СЕГОДНЯ () — 2)

Получить месяц

Получить месяц с даты

= МЕСЯЦ (дата)

Если вам необходимо извлечь месяц из даты, вы можете использовать функцию МЕСЯЦ. В общей форме приведенной выше формулы дата должна быть в форме, которую Excel распознает как действительную дату.

Функция МЕСЯЦ принимает только один аргумент — дату, из которой вы хотите извлечь месяц. В этом примере формула:

= МЕСЯЦ (B3)

B4 содержит значение даты на 5 января 2016 года. Функция МЕСЯЦ возвращает число 1, соответствующее месяцу (январю) даты.

Обратите внимание, что вы можете использовать МЕСЯЦ для извлечения месяца со дня, введенного как текст:

= МЕСЯЦ(«1/5/2016»)

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

Получить название месяца с даты

= ТЕКСТ (дата; «мммм»)

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

Если вы хотите отображать название месяца, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату и перейдите к Форматирование ячеек (Ctrl + 1 или Cmd +1), затем выберите Custom и введите один из этих настраиваемых форматов:

«Ммм» // «Янв»

«Мммм» // «Январь»

Excel отобразит только название месяца, но оно оставит неизменным значение даты.

Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например «ммм».

= ТЕКСТ (B4; «мммм»)

Функция ТЕКСТ преобразует значения в текст с использованием формата чисел, который вы предоставляете. Обратите внимание, что дата теряется при преобразовании: остается только текст для названия месяца.

Если вы хотите максимальной гибкости, вы можете «сопоставить» название месяца с именем месяца непосредственно в функции ВЫБОР следующим образом:

= ВЫБОР (МЕСЯЦ (B4); «Янв»; «Фев»; «Мар»; «Апр»; «Май»; «Июн»; «Июл»; «Авг»; «Сен»; «Окт»; «Ноя» «;» Дек»)

Введите названия месяца, которые хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента, который вводится как МЕСЯЦ (дата). МЕСЯЦ выведет номер месяца, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что МЕСЯЦ возвращает число 1-12, соответствующее имени месяца.

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

Получать месяцы между датами

= РАЗНДАТ (нач_дата; кон_дата; «м»)

Чтобы рассчитать месяцы между двумя датами как целые числа, вы можете использовать функцию РАЗНДАТ.

В показанном примере формула в D5:

= РАЗНДАТ(B5; C5; «м»)

Обратите внимание, что РАЗНДАТ автоматически округляется вниз.

Функция РАЗНДАТ — это функция «совместимости», которая приходит из Лотос 1-2-3. По неизвестным причинам это документировано в Excel 2000, но вы можете использовать его в своих формулах во всех версиях Excel с того времени.

РАЗНДАТ принимает 3 аргумента: дата начала, дата окончания и единица измерения. В этом случае мы хотим месяцы, поэтому мы поставляем «м» для единицы.

РАЗНДАТ автоматически вычисляет и возвращает число в течение месяцев, округленное вниз.

РАЗНДАТ округляется по умолчанию. Если вы хотите рассчитать месяцы до ближайшего целого месяца, вы можете сделать простую корректировку формулы:

= РАЗНДАТ(нач_дата; кон_дата + 15; «м»)

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

Получить год с даты

= ГОД(дата)

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

Функция ГОД принимает только один аргумент — дату, из которой вы хотите извлечь год. В этом примере формула:

= ГОД(B3)

B4 содержит значение даты для 5 января 2016 года. Функция ГОД возвращает число 2016, представляющее год даты.

Обратите внимание, что вы можете использовать ГОД для извлечения года из дня, введенного как текст:

= ГОД(«1/5/2016»)

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

Извлечение даты с даты и времени

= ЦЕЛОЕ(дата)

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а 0,5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть даты, вы можете использовать формулу, которая использует функцию ЦЕЛОЕ. Итак, если A1 содержит дату и время, 1 июня 2000 г. 12:00, формула, приведенная ниже, возвращает только часть даты (36678):

= ЦЕЛОЕ(A1)

Временная часть значения (дробная часть) отбрасывается. Вы также можете использовать функцию ОТБР с тем же результатом:

= ОТБР (A1)

Заметки:

1. При использовании любого из приведенных выше методов убедитесь, что вы используете формат даты для результата, который не включает время. В противном случае вы увидите время, отображаемое как 12:00 AM.
2. Для дат и времени (которые должны быть положительными в Excel) нет разницы в использовании ЦЕЛОЕ и ОТБР для извлечения целого числа. Но ЦЕЛОЕ фактически округляет числа до ближайшего целого, что делает разницу, когда значения отрицательные.

Извлечение времени с даты и времени

= ОСТАТ(дата; 1)

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а .5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть времени (дробную часть), вы можете использовать формулу, которая использует функцию ОСТАТ. Если предположить, что A1 содержит дату и время 1 июня 2000 г. 12:00, формула, приведенная ниже, вернет только часть времени (0,5):

= ОСТАТ(A1;1)

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

= ОСТАТ(5;2) // возвращает 1

= ОСТАТ(7;5) // возвращает 2

Таким образом, если вы используете ОСТАТ с делителем 1, результат будет дробной частью числа, если таковой имеется, потому что каждое целое число может быть равномерно разделено само по себе. Например:

= ОСТАТ(3.125;1) // возвращает 0.125

Короче говоря, = ОСТАТ(число, 1) возвращает только дробную часть числа, отбрасывая целую часть, поэтому это удобный способ извлечь время из даты и времени.

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

Получить возраст от дня рождения

= ЦЕЛОЕ(ДОЛЯГОДА (дата рождения; СЕГОДНЯ ()))

Если вам необходимо рассчитать возраст человека с даты его рождения, вы можете сделать это с помощью функций ДОЛЯГОДА, ЦЕЛОЕ и СЕГОДНЯ. В общей версии приведенной выше формулы, дата рождения — день рождения человека с года, а СЕГОДНЯ — дата, на которую рассчитывается возраст. Поскольку эта формула использует функцию СЕГОДНЯ, она будет продолжать рассчитывать и правильный возраст в будущем.

В этом примере активная ячейка содержит эту формулу:

= ЦЕЛОЕ(ДОЛЯГОДА(D3; СЕГОДНЯ()))

ДОЛЯГОДА вычисляет десятичное число, представляющее долю года между двумя датами. Чтобы определить долю года в десятичном значении, Excel использует целые дни между двумя датами. Это просто в Excel, потому что все даты — это просто серийные номера.

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

Результатом ДОЛЯГОДА для Майкла Чанга, здесь в феврале 2016 года, является что-то вроде этого:

14.7333333333333

Затем функция ЦЕЛОЕ берет на себя и округляет это число до целочисленного значения, которое является числом 14. Хотя Майклу почти 15 лет, ему все еще только 14 по годам, так что это правильный результат.

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

Чтобы проверить день рождения и вернуть «Несовершеннолетний» или «Взрослый», вы можете обернуть формулу возраста в функции ЕСЛИ следующим образом:

= ЕСЛИ(ЦЕЛОЕ(ДОЛЯГОДА(A1; СЕГОДНЯ())) <18; «Несовершеннолетний»; «Взрослый»)

Вы можете заменить 18 любым возрастом, имеющим смысл для вашей ситуации.

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

= ЦЕЛОЕ(ДОЛЯГОДА(A1; ДАТА (2016;1;1)))

Где A1 содержит дату рождения.

Получайте дни, часы и минуты между датами

= ЦЕЛОЕ(конец-начало) & «дни» & ТЕКСТ(конец-начало; «ч» «часы» «м» «мин-ы» «»)

Чтобы вычислить и отобразить дни, часы и минуты между двумя датами, вы можете использовать функцию ТЕКСТ с небольшой помощью функции ЦЕЛОЕ.

В показанном примере формула в D5:

= ЦЕЛОЕ(C5-B5) &»дни»&ТЕКСТ(C5-B5; «ч» «часы» «м» «мин-ы» «»)

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

ТЕКСТ (C5-B5; «ч» «часы» «м» «мин-ы» «»)

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

Значение для дней вычисляется с помощью функции ЦЕЛОЕ, которая просто удаляет целую часть конечной даты минус дата начала:

ЦЕЛОЕ(C5-B5)

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

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

= ЦЕЛОЕ(C5-B5) & «дни» & ТЕКСТ(C5-B5; «ч» «часы» «м» «мин-ы» «с» «сек-ы» «»))

Получить финансовый квартал с даты

= ВЫБОР(МЕСЯЦ (дата); 1;1;1;2;2;2;3;3;3;4;4;4)

Если вы хотите рассчитать финансовый квартал от даты, а финансовый квартал начинается в месяце, отличном от января, вы можете использовать формулу, основанную на функции ВЫБОР.

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

В показанном примере формула в ячейке D5:

= ВЫБОР(МЕСЯЦ (B5); 4;4;4;1;1;1;2;2;2;3;3;3)

Эта формула возвращает число 1-4, которое соответствует четвертичной системе, которая начинается в апреле и заканчивается в марте.

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

= ВЫБОР (2; «маленький»; «средний»; «большой»)

В случае фискальных кварталов мы можем использовать эту же идею для сопоставления любого входящего месяца (1-12) с одним из 4 квартальных значений. Нам просто нужно использовать функцию МЕСЯЦ, чтобы получить номер месяца в качестве первого аргумента, а затем предоставить 12 номеров (по одному на каждый месяц года), которые тщательно упорядочены, чтобы отразить желаемый финансовый год:

= ВЫБОР(МЕСЯЦ (B5); 1;1;1;2;2;2;3;3;3;4;4;4) // Начало январь

= ВЫБОР(МЕСЯЦ (B5); 4;4;4;1;1;1;2;2;2;3;3;3) // Начало апрель

= ВЫБОР (МЕСЯЦ (B5); 3;3;3;4;4;4;1;1;1;2;2;2) // Начало июль

Если вы хотите, чтобы номер квартала включал «К» со значением года, вы можете объединить его:

= «К» & ВЫБОР(МЕСЯЦ (дата); 1;1;1;2;2;2;3;3;3;4;4;4) & «-» & ГОД(дата)

Возвращает значения типа: «К1 — 2016», «К2 — 2016» и т. д.

Получить процент завершения года

= ДОЛЯГОДА(ДАТА (ГОД (дата); 1;1); дата)

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

В примере формула в C5, которая вычисляет процент оставшегося года, выглядит так:

= ДОЛЯГОДА(ДАТА(ГОД(B5); 1;1); B5)

Функция ДОЛЯГОДА принимает две даты, дату начала и дату окончания и вычисляет время между ними в дробных годах. В этом случае мы используем конечную дату как есть, но генерируем начальную дату, равную первому дню того же года, с этим битом кода:

ДАТА (ГОД (B5); 1;1)

Это извлекает значение года из даты в B5 и вставляет его в функцию ДАТА, а также 1 для месяца и дня.

В результате функция ДОЛЯГОДА возвращает десятичное значение, соответствующее доле года между двумя датами.

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

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

= 1-ДОЛЯГОДА(ДАТА(ГОД(B5); 1;1); B5)

Получить квартал от даты

= ОКРУГЛВВЕРХ (МЕСЯЦ (дата) / 3;0)

Если вы хотите рассчитать квартал (то есть 1,2,3,4) от даты, вы можете использовать функцию ОКРУГЛВВЕРХ вместе с МЕСЯЦ.

В показанном примере формула в ячейке C5:

= ОКРУГЛВВЕРХ(МЕСЯЦ (B5) / 3;0)

В этом случае формула сначала извлекает месяц как число от 1 до 12, а затем делит это число на 3. Затем результат округляется до ближайшего целого числа с помощью функции ОКРУГЛВВЕРХ.

Функция ОКРУГЛВВЕРХ работает так же, как функция ОКРУГЛ, за исключением того, что ОКРУГЛВВЕРХ всегда округляет цифры от 1 до 9 до заданного количества цифр.

Если вы хотите, чтобы номер четверти включал «Ч», вы можете конкатенировать. Формула в D5:

= «Ч» & ОКРУГЛВВЕРХ(МЕСЯЦ (B5) / 3;0)

Результатом является буква «Ч», добавленная к номеру квартала.

Получить ту же дату в следующем месяце

= ДАТАМЕС(дата; 1)

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

В показанном примере формула в ячейке B5:

=ДАТАМЕС (B5;1)

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 1 дается месяцами, ДАТАМЕС получает ту же дату в следующем месяце.

Чтобы получить ту же дату за предыдущий месяц, используйте -1:

= ДАТАМЕС(дата; -1) // предыдущий месяц

Получить ту же дату в следующем году

=ДАТАМЕС (дата; 12)

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

В показанном примере формула в ячейке B5:

= ДАТАМЕС(B5;12)

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 12 дается на месяцы, ДАТАМЕС получает ту же дату в следующем году.

Чтобы получить ту же дату за предыдущий месяц, используйте -12:

= ДАТАМЕС(дата; -12) // предыдущий год

Получить дату начала в соответствии с установленной датой

= РАБДЕНЬ(цель; дни; праздники)

Чтобы рассчитать требуемую дату начала в соответствии с установленной датой запуска / запуска / завершения, за исключением нерабочих дней, таких как выходные и праздничные дни, вы можете использовать функцию РАБДЕНЬ.

В показанном примере формула в D6:

= РАБДЕНЬ(B6; C6; B9: B11)

Начиная с даты окончания Пт, 06-Янв-2015, функция РАБДЕНЬ отсчитывает назад 10 рабочих дней (исключая выходные и праздничные дни) и возвращает Вт, 18-дек-2014. Это необходимая дата начала активности, для которой требуется 10 рабочих дней.

Функция РАБДЕНЬ принимает три аргумента: дата начала, дни и (необязательно) праздники. Затем он добавляет дни к дате, автоматически исключая выходные и праздничные дни (если они предусмотрены).

В этом случае мы хотим считать назад от даты в будущем (дату запуска) на определенное количество рабочих дней. Решением является использование отрицательного значения для дней.

При отрицательном значении дня РАБДЕНЬ вычитает дни с даты начала, принимая во внимание выходные и праздничные дни.

Обратите внимание, что праздники не являются обязательными. Результат в D5 показывает разницу, когда отпуска не предусмотрены.

Если в вашем расписании пользовательские выходные дни (то есть что-то отличается от субботы и воскресенья), используйте функцию РАБДЕНЬ.МЕЖД вместо РАБДЕНЬ.

Получить номер недели с даты

= НОМНЕДЕЛИ (дата)

Если вам необходимо получить номер недели со дня с даты, вы можете использовать функцию НОМНЕДЕЛИ. Дата должна быть в формате, который Excel распознает как действительную дату.

Функция НОМНЕДЕЛИ принимает два аргумента, дату и, необязательно, аргумент с именем Тип возвращаемого значения, который управляет схемой, используемой для вычисления номера недели.

В этом примере формула:

= НОМНЕДЕЛИ (B5)

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 2, представляющее число вычисляемой недели.

Обратите внимание, что по умолчанию функция НОМНЕДЕЛИ использует схему, где неделя 1 начинается 1 января, а неделя 2 начинается в следующее воскресенье (когда аргумент Тип возвращаемого значения опущен или поставлен как 1).

При значении Тип возвращаемого значения 2 неделя 1 начинается 1 января, а неделя 2 начинается в следующий понедельник.

С номерами недель в неделю неделя 1 начинается в понедельник первой недели в году с четверга. Это означает, что первый день года для недель ISO — это всегда понедельник в период с 29 января по 4 января.

Начиная с Excel 2010 для Windows и Excel 2011 для Mac, вы можете сгенерировать номер недели ISO, используя 21 как Тип возвращаемого значения:

= НОМНЕДЕЛИ(дата; 21)

В Excel 2013 появилась новая функция НОМНЕДЕЛИ.ISO.

Помогаю со студенческими работами здесь

Время и дата. Время жизни программы
Сделал вывод даты в Edit, но столкнулся с проблемой, время не &quot;идёт&quot;.

AnsiString Days =…

Имя книги как дата и время
Возникла необходимость сохранять файл с именем типа &quot;09.02.2015 11:35:12.xls&quot;. С датой все просто,…

Как найти элемент дата и время в C++ Builder?
В какой вкладке Borland C++ 6.0 , найти DateTimePicker? Не могу найти

Как можеш определить время и дата компютера
Как можеш определить время и дата компютера. Спасибо зф ответ.

Текущая дата и время, как значение по умолчанию
Здравствуйте, нужно установить текущую дату и время как значение по умолчанию для столбца DATEIME…

Как создать скриншот с названием в формате Дата-время
Привет всем!
Перейду сразу к делу. Мне нужно что бы создавался скриншот с названием
Пример…

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму: