Как вытащить дату из текста excel

2

Как в excel преобразовать текст в дату

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

​Смотрите также​​KuklP​ он их не​ надо вводить как​ вниз ))​—— сделал 61​ текста? чтобы не​ справиться с маем.​ ? или Александр​ которая преобразует Это​: _Boroda_, Спасибо более​ датами а эти​ в число, в​.​Нажмите клавишу ВВОД, а​ листе.​ введите даты с​Примечание:​: acornwalle, не мне​ поддерживает. Я уже​ формулы массива с​MCH​

​ (без =), действительно,​ гадать, правильно или​ Елена, спасибо за​ Родионович Бородач? Подскажите​ Вы хвастаетесь или​ удобная формула так​ даты вытащены с​ соседней ячейке пишем​В диалоговом окне​ функция ДАТАЗНАЧ возвращает​Совет:​ двузначным по ячейкам,​Мы стараемся как​ Вас учить, как​

​ пошел качать виртуализацию​ помощью Ctrl+Shift+Enter, формулы​: Должно быть $B$4:$B$23​ получилась почти формула​ нет решена задача,​ науку!​ . я приму​ так завуалированно пытаетесь​ как где нет​ какой то программы​ такую формулу.​​Специальная вставка​

​ порядковый номер даты,​ Для отмены выделения ячеек​ которые были ранее​ можно оперативнее обеспечивать​ общаться в форумах.​ и ставить виндовс,​ должны отображаться в​ — не досмотрел​ Михаила​ т.к. в исходных​Nic70y​ к сведению!​

Преобразование текстовых дат с двузначным годом с помощью проверки ошибок

​ что-то спросить?​ даты она оставляет​ так что эти​=—(ТЕКСТ(A1;»ГГГГММДД»))​в разделе​ которая была представлена​ щелкните любую ячейку​ отформатированные как текст,​ вас актуальными справочными​ Но от Вас​ решил спросить на​ фигурных скобках в​GIG_ant​Serge_007​ данных может быть​:​

​Pelena​Цитата​ пустую ячейку, а​ даты в экселе​

​Копируем формулу по​​Вставить​ в текстовом формате.​ в таблице.​ может появиться маленький​

​ материалами на вашем​​ зависит в большой​​ всякий случай, вдруг​​ строке формул.​​: А вообще то​​: Миш, не смеши,​​ и ММ/ДД/ГГГГ и​

​=ДАТА(ПРАВБ(A2;4);ПОИСК(ПСТР(A2;5;2);»ёнвеварпраяюнюлвгенктояек»)/2;ЛЕВБ(A2;2))​​: Ближайшие двое суток​ ​akademik90, 05.01.2017 в​ Nic70y, формула пишет​​ стоят как текст​​ столбцу. Получилось так.​​выберите параметр​​Что такое числовом Excel?​

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

​ кому нужно решение​​ ДД.ММ.ГГГГ​​для подпольных версий​​ никак. Вам замечание​ 16:26, в сообщении​​ #ЗНАЧ! там где​

​ а мне нужно​Если нужно убрать из​Значения​Excel даты хранятся как​

​ появится рядом с​ левом верхнем углу​ переведена автоматически, поэтому​ возможно получение ответа.​Михаил С.​: Спасибо, если не​ целей можно использовать​ этой задачи в​DV​DV​

​ за неуважительное отношение​​ № 8 ()​ нет даты )​ чтоб они были​

​ таблицы столбец А​и нажмите кнопку​ целые числа, чтобы​

​ выбранной ячейки.​​ ячейки. Индикатор этой​​ ее текст может​​ Рад буду, если​​: Ну если мой​ сложно, подскажите пожалуйста,​ сводную таблицу со​1​​: Да, конечно.​​: По мотивам решения​

​ к участникам форума​ 200?’200px’:»+(this.scrollHeight+5)+’px’);»>_Boroda_, В твоем​ Но все же​ как полноценная дата​ с датами, то​

​ОК​ их можно использовать​В меню выберите команду​ ошибки вы узнаете,​ содержать неточности и​

Преобразование дат в текстовом формате с помощью функции ДАТАЗНАЧ

​ Вы извлечете из​ пример в макинтоше​ где почитать как​ всеми вытекающими от​56 знаков? Какой это​Вот новый файл:​ с Планеты​ и бан.​ файле который ты​​ спасибо и за​​ типо 05.01.2017 !​

​ можно скрыть этот​

​.​ в вычислениях. По​Преобразовать XX в 20XX​
​ что даты хранятся​​ грамматические ошибки. Для​​ нашей переписки что-нибудь​​ не работает, тогда​​ работать с формулами​​ сюда удобствами !​ мозговой штурм?​​у меня 76​​http://www.planetaexcel.ru/forum. D=48824​​А пока почитайте​​ прислал твоя формулаЯ​

​ ту и за​ Как это сделать​

​ столбец.​​На вкладке​

​ умолчанию 1 января​или​ как текст, как​

​ нас важно, чтобы​​ полезное.​

​ не знаю. ​ массива.​ см. файл​Цитата​MCH​

​Есть данные, выгруженные​

​Форма обращения на​ Вам во вчерашней​ эту формулу!​ ? И еще​Или, скопировать столбец​Главная​ 1900 г —​Преобразовать XX в 19XX​ показано в следующем​ эта статья была​1​Guest​ikki​oolechka​(AndreTM)200?’200px’:»+(this.scrollHeight+5)+’px’);»> (без =)​: Чуть поправил свою​ в формате ММ/ДД/ГГГГ.​ «ты» и на​ личной переписке, когда​Я знаю формулу​​ есть даты такие​ В и вставить​щелкните средство запуска​ порядковый номер 1,​. Если вы хотите​

​ примере.​ вам полезна. Просим​: acornwalle, я не​: acornwalle, вставьте эту​:​: это будет следующий​

​ — писать не​ формулу, получилось 69​ При выгрузке некоторые​ «Вы»​​ Вы про свою​​ которая преобразует допустим​​ как допустим 05.01.2017​​ в новый столбец​​ всплывающего окна рядом​​ а 1 января​

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

​ знаю, как там​ формулу в ячейку​acornwalle​ шаг. просто когда​ нужно. По умолчанию​​DV​​ данные преобразовались в​​Serge_007​​ поправленную карму мне​ дата у нас​​ А — я​​ значения ячеек. А​​ с надписью​​ 2008 г., —​

​ без преобразования число,​​ в формат дат​​ секунд и сообщить,​​ в Макинтоше, но​​ В1​​: Скажите как сделать​​ возникает проблема, хочется​​ считаем без знака​​: На основе формулы​

​ даты формата ДД.ММ.ГГГГ,​​: На​​ писали, уже задавал​ 20190105 нам надо​ не пойму что​​ столбцs с датами​​число​

​ номер 39448, так​​ нажмите кнопку​​ можно использовать индикатор​​ помогла ли она​​ в Анг. версии​=ДАТАЗНАЧ(«1 «&ПОДСТАВИТЬ(ПСТР(A1;3;3);»ая»;»ай»)&» «&ЛЕВСИМВ(ПРАВСИМВ(A1;6);4))​ так, чтобы такие​​ найти для нее​​ » Напомните мне​ 05.01.2017 перевести​ значит эта буква​

Преобразовать число в дату Excel.

​ и формулами удалить.​​.​ как интервал в​ ​Пропустить ошибку​​ ошибок.​​ вам, с помощью​​ Excel формула Михаила​P.S. это формула​ данные​ решение.​oolechka​MCH​ и дни поменялись​
​akademik90​ пожалуйста, когда это​
​С использованием функции​ А ? Она​ Подробнее о способах​
​В поле​ днях после 1​.​Примечания:​ кнопок внизу страницы.​
​ будет таккая​ Михаила​
​с декабря 2009г.​
​oolechka​: Добрый всем день.​
​: есть решение в​ местами. Остальные данные​
​: Все Понял)) Наказание​ мы с Вами​ ДАТА() :​ мне не нужны​
​ копирования, смотрите в​
​Числовые форматы​ января 1900.To скопировать​
​Преобразование дат в текстовом​ Во-первых убедитесь, что ошибок​ Для удобства также​=DATEVALUE(«1″&SUBSTITUTE(MID(A1,3,3),»ая»,»ай»)&LEFT(RIGHT(A1,6),4))​Михаил С.​
​с октября 2009г.​: МСН, СПАСИБО!​ Подскажите, пожалуйста, как​ 56​ остались текстом. Необходимо​ принял) Бан на​ на брудершафт пили?».​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2))​ при копировании у​ статье «Копирование в​
​выберите пункт​ формулу преобразования в​ формате с двузначным​ включен в Microsoft​ приводим ссылку на​
​Владимир​: У вас локализация​с октября 2009г.​Как и думала:​ можно при преобразовании​DV​ все данные привести​ 2 дня) А​ Вы его проигнорировали.​С использованием пользовательского​ меня он уже​ Excel».​
​Дата​ диапазон смежных ячеек,​ номером года в​ Excel. Это можно​ оригинал (на английском​: =1&ПСТР(A1;НАЙТИ(» «;A1);НАЙТИ(«г»;A1)-2)&» г.»​ какая? русская, английская​с октября 2009г.​ все гениальное просто.​ даты в текст​: Если еще желающие​ к нормальном датам.​ как можно убирать​
​ А зря, я​ формата : Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(A1;»0000″»/»»00″»/»»00″)+0​ не читает как​Если по какой-то​, после чего укажите​ выделите ячейку, содержащую​ стандартные даты с​

Преобразовать текст в дату ? (Формулы/Formulas)

​ сделать:​​ языке) .​1​ или. ​с апреля 2010г.​Yulia​ (уже сделала), чтобы​ подумать есть, то​ В файле есть​ Замечания ? Чтоб​ ведь могу подумать,​Или : Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ТЕКСТ(A1;»0000\/00\/00″)​ дату а как​ причине не считаются​ необходимый формат даты​ формулу, которая была​ четырьмя цифрами.​Выберите пункты​В некоторых случаях даты​: Владимир, у ТС​Михаил С.​с июня 2010г.​: У меня в​ в ячейках отражающаяся​ можно подождать, иначе​ формула на 119​ было меньше Процент?​ что Вы меня​akademik90​ текст и УФ​ даты, то, возможно,​ в списке​ введена , а​После преобразования ячеек с​Файл​ могут форматироваться и​ английская локализация )​: без пробелов тоже​с февраля 2009г.​ Ехсеl 2010 при​ дата была только​ можно вскрываться, думаю​ знаков. Можно сократить​drugojandrew​ совсем не уважаете​: _Boroda_, В твоем​

​ не может работать​​ в ячейках стоит​ ​Тип​

​ затем перетащите маркер​​ текстовыми значениями можно​ >​​ храниться в ячейках​​Так же что-то​

​ работает.. это я​​с октября 2009г.​ слиянии нескольких ячеек​ текстом: иначе не​

​ меньше уже нельзя​​ почти в 2​: Не получается преобразовать​akademik90​ файле который ты​ с этими ячейками!​

​ не тот формат.​​.​
​ заполнения​

​ изменить внешний вид​​Параметры​ в виде текста.​ не так преобразует​ просто по привычке​с ноября 2009г.​ дата так и​ работает формула:​MCH​ раза.​ запись даты текстом​: А в чем​ прислал твоя формула​
​ У меня УФ​Напоминаем, что в​Чтобы удалить порядковые номера​по диапазону пустых​ дат путем применения​
​ >​ Например, даты могут​ ​ в строке «с​
​ к виду даты..​с июня 2009г.​
​ отображается в тексте,​

​=СУММ((B4:B23=»Кокорин»)*(C4:C23=»апрель»)*E4:E23), т.к. воспринимается​​: Думаю, что больше​MCH​ в дату. Даты​ проблема я не​ работает нормально ,то​ стоит так чтоб​ Excel есть два​ после успешного преобразования​ ячеек, который по​ формата даты.​Формулы​ быть введены в​ августа 2009г.» по​=ДАТАЗНАЧ(«1″&ПОДСТАВИТЬ(ПСТР(A1;3;3);»ая»;»ай»)&ЛЕВСИМВ(ПРАВСИМВ(A1;6);4))​с июля 2009г.​ как ДД.ММ.ГГГГ, хотя​ «апрель» по ссылке​ нет, к понедельнику​: 55 подойдет?​ записаны в таком​ пойму, что тебя​ есть если ячейка​ автоматом по датам​ формата – формат​ всех дат, выделите​ размерам совпадает диапазон​Чтобы преобразовать дату в​.​ ячейки, которые имеют​

​ вашей формуле получается​​KuklP​с августа 2009г.​ формула прописана правильно.​ на ячейку: 12.04.1999.​
​ можно вскрываться​​Вариант 2 -​ формате текстом: 07​ так смущает ?​ пуста и формула​ прокрашивались ячейки входящие​ ячейки и формат​ содержащие их ячейки​ ячеек, содержащих даты​
​ текстовом формате в​​В Excel 2007, нажмите​ текстовый формат, кроме​ «1 ав г.»​: А может сразу​с сентября 2009г.​ Кто-то может мне​ По форуму смотрела,​Serge_007​ 54 52​ февраля 1965​Цитата​ ссылается на нее​ в диапазон дат​ значений. Подробнее об​ и нажмите клавишу​ в текстовом формате.​ ячейке в числовом​кнопку Microsoft Office​ того, даты могут​Михаил С.​

​ родить надо было?​​с октября 2009г.​ в этом помочь?​ не нашла ответа.​: А вы со​
​Вариант 3 -​​Подскажите, плиз, как​_Boroda_, 05.01.2017 в​ в твоем файле​ с . числа​ этих форматах, чем​ DELETE.​В результате получится диапазон​
​ формате, с помощью​, а затем​ быть импортированы или​

​: Дла английской должно​​ На форуме есть​с декабря 2009г.​ Пример прикреплен ниже.​ Заранее спасибо.​ стороны посмотрите -​ 51 50​ это сделать.​ 16:43, в сообщении​ он остается пустым​ по . автоматом​ они отличаются, как​Рассмотрим,​ ячеек с последовательными​ функции ДАТАЗНАЧ. Скопируйте​​ выберите пункт​​ вставлены в ячейки​

​ так сработать​​ Макинтошевцы. Не отнимали​с января 2010г.​madguck​Guest​ даже 56 -​DV​_Igor_61​ № 9 ()​ ! А потом​

​ прокрашивалось . а​​ настроить нужный формат,​как преобразовать число в​ номерами, который соответствует​ формулу и выделение​Параметры Excel​
​ в текстовом формате​
​=ДАТА(—ЛЕВСИМВ(ПРАВСИМВ(A1;6);4);ПОИСКПОЗ(ПРАВСИМВ(ЛЕВСИМВ(A1;ДЛСТР(A1)-7);ДЛСТР(A1)-9);<"января";"февраля";"марта";"апреля";"мая";"июня";"июля";"августа";"сентября";"октября";"ноября";"декабря">;0);1)​ бы время. Хотя. ​с февраля 2010г.​

​: А у меня​​: =ТЕКСТ(ссылка_на_дату;»ММММ»)​​ это не реально​

​: Можно 50​​: Здравствуйте! Попробуйте заменить​ 200?’200px’:»+(this.scrollHeight+5)+’px’);»>»Напомните мне пожалуйста,​ твою формулу перенес​ у меня они​ т.д., читайте в​ дату​

Преобразовать текст в дату (Формулы/Formulas)

​ диапазону ячеек с​​ ячеек, содержащих даты​>​ из внешних источников​Михаил С.​ Если мозг не​с марта 2010г.​
​ все работает) Перебейте​преобразует 12.04.1999 в​

​MCH​​MCH​ «февраля» на «февраль»​

​ когда это мы​​ в другой файл​
​ не красятся так​
​ статье «Преобразовать дату​

​Excel​​ датами в текстовом​​ в текстовом формате​​формулы​ данных.​

​: чуть упростил​​ ампутирован, легко сможете​с апреля 2010г.​ дату ручками и​ Апрель​ ​: Почему не реально,​ ​: Тоже сделал 50,​Pelena​

​ с Вами на​​ они там не​ как у меня​ в текст Excel»​и​
​ формате.​ с помощью​.​Даты, отформатированные как текст​acornwalle​ подстроить под свои​с мая 2010г.​ да будет Вам​GIG_ant​

​ если есть?​​ ЕСЛИ не использовал​ ​: Вариант​ ​ брудершафт пили?»​

Преобразовать текст в дату (Преобразовать текст и неправильную дату в правильную дату)

​ пустые а пишет​​ написано 05-янв-2017 как​ здесь.​
​дату в число.​
​Выберите ячейку или диапазон​Специальной вставки​Проверка ошибок​ выравниваются по левому​: Дла английской должно​ — надо.​с декабря 2008г.​ счастье)​: скиньте примерчик, и​Кроме того, Сергей,​DV​=—(ЛЕВСИМВ(ПОДСТАВИТЬ(A2;»ая»;»ай»);6)&ПРАВСИМВ(A2;5))​Оказца мы не​ либо ЗНАЧ либо​ текст а не​Как работать с​

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

​С ув. Сергей.​​преобразовывались в ячейки​

​Микки​​ решим . ​ у тебя есть​

​: Вынужден констатировать, что​​и формат даты​ пили его )​ ставит дату 00.01.1900​ как дата !​ датами в Excel,​ Excel дата написана​ номерами и на​Выполните следующие действия.​Включить фоновый поиск ошибок​ не по правому​=ДАТА(—ЛЕВСИМВ(ПРАВСИМВ(A1;6);4);ПОИСКПОЗ(ПРАВСИМВ(ЛЕВСИМВ(A1;ДЛСТР(A1)-7);ДЛСТР(A1)-9);<"января";"февраля";"марта";"апреля";"мая";"июня";"июля";"августа";"сентября";"октября";"ноября";"декабря">;0);1)​Guest​ с датой​
​: У меня почему-то​

​oolechka​​ фора в 1​ я случайно ввел​

​drugojandrew​​ И что?​
​ г. как это​
​ что делать люди?​

​ менять формат, считать,​​ одним числом 20160112.​ вкладке​Выделите пустую ячейку и​. Любая ошибка, обнаружении,​ краю). Если включена​благодарю, всё работает.​: KuklP)) как ты​Михаил С.​ только так получилось..​: вот пример листа,​

​ символ, т.к. я​​ в заблуждение. Подвел​
​:​
​Wasilich​

​ изменить чтоб было​​Nic70y​ прибавлять дни к​

​ Нам нужно разделить​​Главная​ убедитесь, что его​

​ будут помечены треугольник​​ Проверка ошибок ,​ только пока не​

​ жесток​​: один из вариантов​.=D2&»: «&»серія «&E2&»​ в котором сбоит​ считаю с учетом​ формат ячейки в​

​Pelena​​: А то, что​ пусто чтоб не​:​

​ дате, вычитать, т.д.,​​ точками число на​в группе​ числовой формат Общие.​ в левом верхнем​

​ дат в текстовом​​ скинули файл -​acornwalle​
​KuklP​ «&»номер «&» «&F2&»​ формула​ знака » >=ДАТА(ПСТР(D7;8;2)+2000;ПОИСК(ПСТР(D7;5;2);»ёanebarprayunulugepctovec»)/2;ЛЕВБ(D7;2))​ смотрите в статье​ год, месяц и​Буфер обмена​

​Проверка числового формата:​​ углу ячейки.​ формате с двузначным​ оказывается у экселя​: англ локализация​: Класс! Я только​ виданий»&» «&G2&» «&ТЕКСТ(ДАТА(ГОД(H2);МЕСЯЦ(H2);ДЕНЬ(H2));»ДД.ММ.ГГГГ»)​oolechka​

​ ты без​​ том, что в​ надо.​ себя так не​ и не удалять!​Udik​
​ «Работа с датами​ день.​нажмите кнопку​на вкладке​

​В разделе​​ может быть также​ маковского синтаксис другой​KuklP​​ собрался макрос. ​​madguck​: я дату уже​
​Serge_007​​ датах тоже надо​Che79​ ведут, во первых,​ Даже в твоем​: А чем штатное​

преобразование даты в текст

​ в Excel» и​​Как преобразовать число в​Копировать​Главная​Правила контроля ошибок​ помечены как с​Юрий М​: Да не так​acornwalle​: Там проблема с​
​ преобразовала. но дальше​: В итоге считается​ было менять месяцы​: Я не виноват,​ он вам совсем​ файле рядом в​

​ средство​​ в конце этой​
​ дату Excel.​.​

​в группе​​установите флажок​ индикатором ошибки:​

​: Вариант:​​ уж, что бы.​: а без макроса​ самой датой, формула​

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

​ Прсто иногда сутками​​ можно? с помощью​

​ не причем.​ эту ячейку формула​ — для меня​ Так что, учитывая​ длинная в голову​

​ вторых на много​​ сделать тоже самое​не устраивает?​

​ перечень статей в​​ таблица с датами,​ Можно также нажать клавиши​щелкните стрелку рядом​ представленные 2 цифрами​Поскольку ошибок в Excel​ CDate(Format(Replace(«1 » &​ приходится подгонять решение​

​ формулы?​​Yulia​ не работает. вот​ — нереал​ новые обстоятельства думаю​ прилетела​

​ старше по возрасту​​ как ты сделал​
​akademik90​ разделе «Другие статьи​

​ написанными одним числом.​​ CTRL + C.​ с полем​.​ можно определить текст,​ Split(Cells(1, 1))(1) &​ из-за неграмотного вопроса:-(​R Dmitry​: Получилось только после​ проблема. не вводить​MCH​

​ можно показать неправильную​​=ДАТАМЕС(ЛЕВБ(A2;ПОИСК(» «;A2)-1)&ПСТР(ЗАМЕНИТЬ(A2;ПОИСК(«$»;ПОДСТАВИТЬ(A2;» «;»$»;2))-1;1;ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК(<"мар":"уст">;A2)));»»;ЕСЛИ(ЕЧИСЛО(ПОИСК(«мая»;A2));»й»;»ь»)));ПОИСК(» «;A2);99);0)​ и вы всё​ что у тебя​: Nic70y, Огромедное спасибо​ по этой теме».​

​Нам нужно сделать так,​​Выберите ячейку или диапазон​Числовой формат​
​Выполните эту процедуру, чтобы​ отформатированный дат с​ » » &​

​ А так я​​: а ответ Михаила​ того, как поменяла​ же в формулу​

​: Все равно, это​​ формулу:​Вместе с форматом​ ТЫКАЕТЕ как пацану​ ячейка в нужном​

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

​ Split(Cells(1, 1))(2), «г.»,​​ добрый, всем хочу​
​ Вам не по​ в формуле вместо​ массива еще и​ не означает, что​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ПСТР(A1&»/»&A1;1+ПОИСК(«/»;A1&»/»;4);ДЛСТР(A1))​ даты​ на улице, не​

​ столбце пустая?​​ нужно!​ несколькими способами посчитать​ были написаны в​ даты в текстовом​Общие​

​ формате текста в​​ использовать параметры автоматического​

преобразование текста в дату

​ «»), «dd.mm.yyyy»))​​ помочь. А почему​ душе?​ «ДД.ММ.ГГГГ» на «dd.mm.yyyy»​
​ эту формулу. будет​
​ нужно повторить данный​
​Serge_007​
​_Igor_61​
​ взирая на замечания,​
​_Boroda_​
​akademik90​
​ стаж, возраст. Про​
​ таком формате –​
​ формате, и на​
​.​
​ обычном даты.​
​ исправления преобразуемый Дата​
​Юрий М​
​ аноним? Авторизоваться слабо?​
​KuklP​
​Андрей​
​ слишком кудряво. а​
​ результат или улучшить​
​: А в правильной​
​: Не, ребята, мой​

​ а еще НИК​: Нет, она там​

​: Udik, Пробовал Он​​ эти способы читайте​

​ 26.01.2016.​​ вкладке​В пустой ячейке сделайте​

​Выберите на листе любую​​ в формате даты.​: А макросы и​acornwalle​

​: Дим, а вопрос​​: Добрый день! Подскажите​ все гениальное просто.​ его, нужно предложить​

​ формуле сколько знаков?​​ вариант лучше -​ взял​ оставляет значение 0,5,​

​ не понимает его​​ статью «Как быстро​В ячейке В1​Главная​ следующее.​ ячейку или диапазон​ Функция ДАТАЗНАЧ преобразования​ не нужны, оказывается. ​: KuklP, вы правы,​ ТС Вам по​ пжста, почему у​MCH​ свой с указанием​

​MCH​​ вообще формул не​akademik​ которое скрывается форматом​ как дату он​

​ посчитать стаж в​​ пишем такую формулу.​в группе​Введите​

​ смежных ячеек с​

​ большинство других типов​KuklP​

​ не подумал, что​​ душе?:-)​ меня в 2003​: .​

​ количества знаков​​: неправильная формула:​ надо​, ителегент . ​ ячейки ДД.ММ.ГГГГ;;​
​ остается как текст​

​ Excel».​​=ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2))​Буфер обмена​= ДАТАЗНАЧ (​ индикатором ошибки в​ дат в текстовом​: Юр, а ответ​ это так скажется​acornwalle​ Экселе формулы из​
​GIG_ant​

​AndreTM​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(A1>»»;—ЛЕВБ(ПРАВБ(A1;4)&»/»&A1;ДЛСТР(A1));A1)​А если по​

​akademik90​​Цитата​

​ !​​akademik90​Копируем формулу по​нажмите кнопку со​Щелкните ячейку, содержащую дату​ верхнем левом углу.​ формате в формат​ автра от 29.08.2010,​ на результате решения.​: Да у меня​

​ файла, присланного пользователем​​: пожалуйста (файл)​: Я сегодня добрался​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЛЕВБ(ПРАВБ(Т(A1);4)&ТЕКСТ(A1;»0;[email protected]»);ДЛСТР(A1))​ теме — думал​: Wasilich, Мне тогда​akademik90, 05.01.2017 в​_Boroda_​: Опять проблемка с​

​ столбцу. Получилось так.​​ стрелкой​ в текстовом формате,​ Дополнительные сведения читайте​ даты.​ 20:47? Тут хоть​ В следующий раз​ просто Макинтош, а​ MCH, не работают?​MCH, проверьте ваши​ до этой темы,​MCH​

​ о трех буквах,​​ как к нему​ 15:47, в сообщении​: Еще вариант​ тем же Файлом​Преобразовать дату в число​Вставить​

​ которую следует преобразовать.​

​ в статье Выбор​​При импорте данных в​

​ какая локализация. ​​ не буду так​ там в 2008​
​Казанский​ формулы, по моему​ подумал — получил​: Дим, можешь выложить​ как в варианте​ обращаться Дядюшка Борода​

​ № 7 ()​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЗАМЕНИТЬ(ЛЕВБ(D7;9);4;3;ПОИСК(ПСТР(D7;5;2);»ёanebarprayunulugepctovec»)/2)​ ! Заказчик дал​
​ Excel.​

​и выберите команду​​Введите​

​ ячеек, диапазонов, строк​​ Microsoft Excel из​KuklP​
​ делать. Приношу свои​

​ Excel нету макросов​: [телепат моде он]​ у вас диапазон​ пока только 73​ файл с корректным​

​ Елены, но не​​ ? Или Мистер​
​ 200?’200px’:»+(this.scrollHeight+5)+’px’);»>Я знаю формулу​akademik90​ Эксел файл с​Чтобы преобразовать дату​Специальная вставка​)​

​ или столбцов на​​ другого источника или​: Дуплетом:-)​

​ извинения.​​ на Визуал Бейсике,​Потому что их​ с фамилиями слезает​ знака (без =). ​

Извлечение из даты в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поскольку между 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. Шаги выглядят так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заметки:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При значении Тип возвращаемого значения 2 неделя 1 начинается 1 января, а неделя 2 начинается в следующий понедельник.

С номерами недель в неделю неделя 1 начинается в понедельник первой недели в году с четверга. Это означает, что первый день года для недель ISO — это всегда понедельник в период с 29 января по 4 января.

Начиная с Excel 2010 для Windows и Excel 2011 для Mac, вы можете сгенерировать номер недели ISO, используя 21 как Тип возвращаемого значения:

Как вытащить дату из текста excel

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

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

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

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

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

• Заметки:
• В приведенной выше формуле A2 это ячейка, содержащая дату, которую вы хотите извлечь;
• Если в ячейке есть другие числа, эта формула не будет работать правильно ；
• Формула не может правильно извлечь дату, если в текстовой строке содержится более одной даты.

Преобразование различных нестандартных дат в нормальные реальные даты в Excel

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без каких-либо ограничений в 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

• Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
• Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
• Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
• Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . —>
• Более 300 мощных функций . Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Может кто-нибудь помочь мне найти строку для извлечения даты в этом формате, пожалуйста, гггг-мм-дд
«2018-03-24T01:42:26-07:00»

Я использую Excel 2003, поэтому я считаю, что IFERROR не существует, и я нашел, что это цитируется в Интернете как эквивалент:

Итак, я пытаюсь это:

— Я нажал CTRL+SHIFT+ENTER, фактическая формула в excel показывает ее в <>

Используя эту формулу, я просто получаю пустой результат

Можете проверить и дайте мне знать, где я ошибся. Или это невозможно в Excel 2003?

На самом деле мои данные для извлечения имеют формат:

Данные действительны на 14 декабря 2018 г.

Поэтому мне нужно извлечь «14 декабря 2018 года», а не обычный
хх/хх/хх

Кажется, это для меня (Excel 2003)

: A2 содержит данные для извлечения

: он находит первое числовое значение, затем переводит оставшуюся дату

Работает с данными в этом формате: Данные действительны на 14 декабря 2018 г.

Глядя на «среднюю» функцию «это = количество символов.

Таким образом, самым длинным будет = «дд сентября гггг» = 17 символов

2 = дата
9 = месяц
4 = год
2 = пробелы

Возможно, мне следовало сказать, что данные: «25 сентября 2018 года».

Так что 17 должно охватывать все месяцы года 🙂

Привет, Адам,
Формула была исправлена, как показано ниже:

Пожалуйста, попробуйте, надеюсь, это поможет вам!

У меня возникла проблема с тем, что эта формула не отображает все значение даты.
Как и в случае с Адамом Табором, формула отображала значение даты, как и ожидалось, до 01. С тех пор в значении даты отсутствует последняя цифра

Пример:
Ячейка A1 содержит строку «Monthly-Returned-Ticket-Report-01-29-2020».

Ячейка A2 содержит следующую формулу:

Ожидаемый результат: в ячейке A2 отображается значение «01-29-2020».

Фактический результат: в ячейке A2 отображается значение «01-29-202».

Надеетесь, что у кого-то есть идеи о том, что нужно изменить, чтобы справиться с этим новым поведением с нового года?

Привет, Нил,
Формула в этой статье была обновлена, пожалуйста, примените следующую формулу:

Пожалуйста, попробуйте, надеюсь, это поможет вам!

Я знаю, что формула не будет работать, если в ячейке есть другие числа, однако есть ли способ извлечь только числа в формате даты?
Пример: люди 5/2/12/20
Игнорируйте 5 и выводите только 2

Привет, Админ.
я не могу извлечь дату из текста ниже.
RETURNED_INCOMPLETE -> INCOMPLETE Назначение JSV подтверждено 15, PM. Без сабвуфера WFM после 10:2020
при вводе заданной формулы значения приходят. 15, 10:2020. Без саба WFM через 5

«RETURNED_INCOMPLETE -> INCOMPLETE JSV-Назначение подтверждено 21/10/2020 PM mhumza требуется с 06 до 07 часов, потому что работает» после ввода данных по формуле значения приходят. 21 PM Мумза разыскивается с 10 по 2020

«RETURNED_INCOMPLETE -> INCOMPLETE JSV-Назначение подтверждено 18/10/2020 PM mhumza требуется с 16:30 до 18:00» после ввода данных по формуле приходят значения. 18 мхумза разыскивается с 10:2020 до 16:30

RETURNED_INCOMPLETE -> INCOMPLETE Назначение JSV подтверждено 15, AM. Без WFM саб доступен только до 10 утра
при вводе заданной формулы значения приходят. 15, 10:2020. Без WFM sub доступен только до 9

Как в Excel из даты вытянуть день, месяц и год отдельно.

Функция МЕСЯЦ() — извлекает номер месяца из даты, в числовом формате. Месяц возвращается как число от 1 до 12.

Функция ГОД() — извлекает значение года из даты. Год может быть целым числом от 1900 до 9999

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

• Значение это ссылка на ячейку с датой;
• Формат для отображения обязательно ставить в «кавычки» , и в зависимости от количества букв «М» отображаться будет по разному: