Как сделать чтобы в excel число на дату не менялось?

Меняет число на дату эксель

Отмена автоматической замены чисел датами

​Смотрите также​ один​ по продажам за​Пример работы c функциями​ аргументом:​ — саму ячейку.​Klinskyi​ отформатировать.​ в особом формате,​ противном случае используется​ непосредственно перед кодом​Тип​В списке​

​ и времени при​ значения все равно​ в левом верхнем​ функции поиска для​Чтобы даты было проще​Kolomoets​ длительные, средние и​ ДЕНЬ и ДЕНЬНЕД.​=ГОД(адрес ячейки с датой​ Всё.​

​: Импорта данных я​Фгуп скб титан нио​

​ например​​ 24-часовая система. Код​​ «сс», Excel отображает​​будет отображен код​​Тип​​ вводе. Например, если​​ будут преобразованы, но​

​ углу каждой ячейки​ этих данных, мы​ вводить, Excel Online​: Спасибо огромное, то​ короткие периоды времени.​ Функция ДЕНЬ получает​ в числовом формате)​

​Klinskyi​ не делал ,Файл​ пас​январь 2010​ «м» или «мм»​

​ не месяцы, а​ выбранного в действии​выберите нужный формат​ ввести в ячейку​ будут выровнены по​ зеленый треугольник. При​ рекомендуем использовать апостроф.​ автоматически преобразует 2.12​ что нужно.​Стоит сразу отметить что​ вычислить из даты​Аргумент функции является обязательным​

​: Для особо «одареных»напишите​​ достался мне в​

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

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

​ таком виде. Что​ вместо «общий» в​ как текст, выбрав​ после кода «ч»​Часы, минуты и секунды​ или времени. Встроенный​Примечание:​2/2​

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в MS EXCEL)

​ текст). Изменить формат​ от нее появится​ ПОИСКПОЗ и ВПР,​ Но это может​Юлия​ получить разницу между​Функция ДЕНЬНЕД возвращает номер​ может быть заменен​ бы я мог​ то не получается​ «текстовый».​ пункт​ или «чч» или​Для отображения​ формат даты или​ Форматы даты и времени,​, Excel автоматически интерпретирует​ ячеек на числовой​ желтенький значок, нажав​

​ не учитывают апострофы​ сильно раздражать, если​: А как сделать​ двумя датами нам​ дня недели (1-понедельник,​ на «дата_в_числовом_формате». В​ применить для всех​ предложенный вами вариант.​Тамара золотарёва​Текстовый​ непосредственно перед кодом​

​Используйте код​ времени невозможно изменить​ отмеченные звездочкой (*),​ его как дату​ можно стандартным способом​ который можно преобразовать​ при вычислении результатов.​ вы хотите ввести​ наоборот? Убрать время,а​

​ не поможет ни​ 2-второник… и т.д.)​ примерах ниже, вы​ ячеек​JayBhagavan​: тут можно только​в списке​ «сс», в противном​Часов в виде чисел​

​ или удалить, поэтому​ зависят от параметров​ и отображает в​ через меню Формат​ значения в числа.​Если число в ячейке​ числа, которое не​

​ дату оставить.​ одна из выше​ для любой даты:​ сможете наглядно увидеть​JayBhagavan​: Требуйте исходный файл.ЦитатаKlinskyi​ заранее выделить нужные​

​Числовые форматы​ случае Excel отображает​ от 0 до​ можно безопасно перезаписать​

• ​ даты и времени​ ячейке дату​
• ​ ячеек (нажав​После преобразования числа будут​ выровнено по левому​ нужно превращать в​​китин​​ описанных функций. Для​
• ​Во втором опциональном аргументе​ это. Важно помнить,​: Klinskyi, формула есть​
• ​ написал: Что то​ ячейки (удерживай ctrl​.​ месяц, а не​ 23​ его.​ в панели управления​02.фев​
• ​CTRL+1​
• ​ выравнены по правому​

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

​Чтобы быстро ввести текущую​ минуты.​ч​Внесите необходимые изменения в​ Windows. На форматы​. Если такой формат​).​ краю ячейки (если,​ означает, что оно​ невозможно отключить его,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЦЕЛОЕ(A2)​ воспользоваться специально предназначенной​ указать число 2​ даты в качестве​​В таблицах Excel предусмотрена​​ вами вариант.Вариант был​

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

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

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

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

В этой статье

​Если вам нужно сложить​Юлия​

​ «дата» требует особого​ (с понедельника-1 по​

​ ячейки), функция ГОД​ и числовой информации.​

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

​ не для применения​Amir mazitov​ клавиши CTRL+; (точка​ не делали этого​чч​​ использовать любые из​​ времени в формате​2 февраля 2009 г.​ формат ДАТ (Если,​ по значению).​​ числа с буквой​​ много значений, которые​: Не поняла..)​ подхода при обработке​ восркесенье-7):​​ не будет выполнена.​​ Доступна и обработка​​ к порченому экселевскому​​: левый верхний квадрат​ с запятой), а​ раньше. Дополнительные сведения​​Минут в виде чисел​​ кодов, указанных в​ другого языка выберите​или​ конечно, EXCEL поймет​​Чтобы преобразовать сразу несколько​​ «е», например 1e9,​​ похожи на даты​​Юлия​ данных. Поэтому следует​Если пропустить второй необязательный​ Ее результатом станет​​ дат. При этом​​ файлу.​​ щелк левой кнопкой​​ затем, если необходимо,​ о создании пользовательских​ от 0 до​​ приведенных ниже таблицах.​​ нужный вариант в​

​02.02.09​​ ту форму, в​​ значений — выделите​​ оно автоматически преобразуется​​ (например, чисел с​: Я выделяю ячейки,​ использовать соответствующие данному​​ для заполнения аргумент,​​ отображение #ЗНАЧ. Поэтому​

​ может возникнуть потребность​​На тот случай,​​ мыши (выделение всей​ нажмите клавишу ВВОД.​​ числовых форматов см.​​ 59​

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

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

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

​ Правда, после преобразования​ ячейки появится желтый​ 1,00E+09. Чтобы избежать​ отформатировать ячейки как​ «дата», отображается только​​ Excel.​​ формат по умолчанию​

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

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

​.​​ диалоговом окне​​ в ячейках будет​ значок — с​ этого, введите перед​​ текст. После этого​​ дата, но в​

​Kolomoets​​ (английский с воскресенья-1​​ числовом варианте. Дни,​ например, года. Для​​ файл.​​ числа текстовый​

​ до текущей даты​​ числовых форматов.​​ от 00 до​​Используйте код​​Число в активной ячейке​​Формат ячеек​​ содержаться число, соответствующее​ помощью него преобразуйте​ числом апостроф: ‘1e9​ Excel Online не​ строке формулы всё​: Здравствуйте,​ по суботу-7).​ месяцы и год​ этого существует отдельные​Klinskyi​

​Aleksei dongvani​​ при каждом открытии​​К началу страницы​​ 59​​Месяцев в виде чисел​​ выделения на листе​​, как описано в​ датам. Изменить формат​ сразу все значения.​При копировании ЧИСЛОвых данных​ будет пытаться преобразовать​ равно остается время.​Пример документа приложил,​Создадим формулу из комбинаций​ могут быть разделены​

​ функции: ГОД, МЕСЯЦ,​: Извините, я ничего​​: viberi ia4eiku,pravoi knopkoi​​ листа или перерасчете​Чтобы быстро применить формат​мм​ от 1 до​

​ отображается в поле​​ приведенной ниже процедуре.​

​ ячеек на формат​

​Другой способ группового преобразования​

Проблема отображения числа в формате даты в Excel

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

Решение проблемы отображения числа как даты

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

Способ 1: контекстное меню

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

1. Кликаем правой кнопкой мыши по диапазону, в котором нужно сменить формат. В контекстном меню, которое появится после этих действий, выбираем пункт «Формат ячеек…».

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

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

Способ 2: изменение форматирования на ленте

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

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

Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.

Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Еще статьи по данной теме:

Почему САМОПРОИЗВОЛЬНО меняется формат на ДАТА, не в момент ввода, а когда в следующий раз открываешь документ.

Здравствуйте, Александр. А вы сохраняете документ после того, как снова меняете формат на нужный вам или просто его закрываете? Если все-таки сохраняете, то проверьте, не включены ли у вас случайно макросы в этом документе.

Спасибо. Страдаю от этого не первый год. Лицензия.

Спасибо! Очень помогли.

Здравствуйте. Вопрос заключается в том, что при выделении всей таблицы, где находятся даты вместо чисел, и форматируя ячейки по другим форматам, приводит к тому, что вместо дат появляются непонятные числа (не те, что в исходном документе). А изменяя таблицу повторно по форматам, изменяются уже эти неправильные числа в ячейках. Также в данные ячейки не получается записать исходник после всех манипуляций (преобразует снова в неправильное новое число).
Что делать, я уже не знаю??…Эксель 14 версии, офис 10

Спасибо! А то блин сам не разобрался бы.
Давно хотел Excel изучить всё времени нет

Здравствуйте! Почему в Excel вместо числа появляется текст проблему выложил на ютуб youtu.be/xh9_8icxj0c

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

Мне нужно проставить страницы в содержании(таблица), я ставлю 4-5, перехожу к другой ячейке, у меня в первой дата ставиться. Я работаю не в вордовском документе, а в OpenOffice. Таблицу я не делала, нам скинули отчет по практике, который мы должны заполнить, и таблица там уже была

Уже не надо, спасибо. Сделала по-другому, получилось

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

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

Попробую объяснить. Эксель 2007, есть таблица с данными на 1000 человек, в столбце дата стоит 28.11.1974, при просмотре ячейки через «формат ячейки» данные в ней «дата» при загрузке этого файла в программу которая проверяет правильность внесенных данных в Экселевский файл значение указанной ячейки исчезает, т.е. там пусто. Мне удалось установить, что если ячейку в данными 28.11.1974 войти в «формат ячейки» и по менять с «дата» поменять на «общий» или «числовой» появиться цифра 27361. Но в моем случае остается значение 28.11.1974. Если бы это был единичный случай проблем бы не было. Но так как файлы обрабатываются ежемесячно и в них 1000 человек, то приходиться в ручную проверять все 1000 дат на предмет такого казуса. При том что доходит до абсурда. Шаблон Экселевского файла один и тот же изменятся информация в других ячейках, а в ячейках столбца с датами уже другие даты ведут себя подобным образом.

У нас следующая проблема: из подпрограммы AutoCad выводим данные в Exel. Значений как правило много, больше 1000 и половина переводиться автоматически в даты.
В AutoCad загружается данный фал, но программа даты не воспринимает!
Однако на соседней машине, с такой же версией Exel, значения не преобразуются в даты.
Как отключить автоматическое преобразование?

такая же проблема с переводом из Autocad

я сделала как вы сказали в двух способах,но всё возвращается опять на дату

все сделал,но дата не исчезла

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

почему excel воспринимает число как дату в html

если лист защищен ты ничего не можешь сделать, лучше б это описали

Добрый день. Этому вопросу у нас посвящена отдельная статья:
Снятие защиты с файла Excel

Приветствую Максим. Хелп, не могу пофиксить даты, ну никак. Пробовал разные форматы, как дат(в том числе пробовал иностранные форматы), так и числовые. Не получается у меня исходное число получить. Вот пример, есть ряд чисел, 88.28,11.25,0.48, указаны через запятую. Первое и третье числа переносятся как есть, второе число становится 25 ноября. При его формате я получаю разные числа, вплоть до 45962 (общий формат), но исходное число получить не могу.

И да, эксель 2016.

Если ставить нормальную запятую то в формате ячейки числовой или текстовый, а так же общий — цифры остаются прежними.
А вот если Вы ставите точку то формат ячейки изначально должен быть текстовый! И только потом туда вбивать число.

Сергей, спасибо за ваш ответ. Немного детализирую проблему. Это импортированный документ, его необходимо упорядочить.Вся информация в нем до дефолту собрана в одном столбце. Я применяю функцию «Текст по столбцам» и далее указываю использовать запятые как разделитель, никаким другим образом я не получаю соответствие информации в столбцах и строках, то есть замена точек на запятые не подходит, от этого пострадает логика разбивки. Принудительное форматирование ячеек как текст так же не дает результатов. Единственный способ, которые сохраняет логику, но изначально понятен только мне, это замена на отличный от запятой символ, например знак доллара. Тогда, зная что доллар=запятая/точка, данные приобретают смысл и сходятся.

Если я правильно Вас понял Алексей, Вам не важно стоит запятая или точка в ячейке после применения функции «Текст по столбцам». Если честно с таким еще не сталкивался. Но заметил такую вещь. При применении функции «Текст по столбцам» в «Мастере текстов (разбор) — шаг 3 из 3» в «Образец разбора данных» можно выбирать столбец и к каждому столбцу можно задавать «Формат данных столбца» если поменять с «Общего» на «Текстовый» данные не поменяют свои значения. Попробуйте может это Вам поможет.

Как заменить дату на число?

Формат ячейки. Или у Вас другой случай? Тогда более подробнее.

изменяю формат с даты на числовой, но всё равно пишет, что это видоизменённая дата, как это изменить?

Не совсем понял. Можно подробнее сам процесс. И желательно с примером.

в ячейке с форматом общий(по умолчанию) ввожу 8-12, оно преобразуется в дату, я меняю формат на «числовой», заново ввожу 8-12, навожу на ячейку курсор, выпадает меня с надписью, что это видоизменённая дата, скрин прилагаю. Вот как сделать что бы число стало чистом а не видоизменённой датой?

Простите, но числа 8-12 в математике не существует! Если Вам нужно чтобы в ячейке было написано 8-12 сначала измените формат ячейки не «Текстовый» а затем введите 8-12.

Согласен, такого числа нет))), но есть числа написанные через тире. И если я Вас правильно понял, то надо сначала задавать формат ячейки «Текстовым», а затем вводить числа через тире или запятую? Если я сначала их ввожу в ячейку числа с тире или через запятую, точку, то программа понимает это как календарная дата и уже поменять формат на «Текстовой» не получится? Что собственно у меня и происходит. В любом случае спасибо за ответ!

Вместо даты во всём столбце стали отображаться пятизначные числа. Ни один из перечисленных в статье способов не помогает

Току нет. от статьи вопрос был не как поменять формат данных а как вставить изначально не как дату Я вставляю 5,03 он пишет 5 марта вот в этом проблема.

У Вас не запятая после 5, а точка. Можете указать конкретно проблема? Но при вставлении значения в ячейку с форматом «текстовый» значение не меняется.

например было число 7.55, эксель преобразовал в 01.07.1955, после изменения формата в ячейке 20271 что в принципе не верно

Сначала меняете формат ячейки на текстовый затем вставляете значение.

Нужно скопировать большой объем данных в эксель. Задаешь всем ячейкам формат — число. После копирования формат поменялся на дату. И часть ячеек с числами, часть с датами. Вместо 4.47 — стоит апр.47. Если поменять формат на числовой, будет 17258,00

А что не судьба задать формат ячейки — текстовый. Или это сверх сложная задача.

Здравствуйте! Попробовал все выше перечисленные варианты, но всё равно при в воде 5-30, почему то выходит дата 01.05.1930 и формат ячейки меняется. Помогите пожалуйста разобрать с этим вопросом.

Можете привести конкретный пример и последовательность действий? Вдруг удастся найти решение!)

Такая же песня. Уже час бьюсь. Меняю формат, нажимаю Ок, ввожу 3/30, пишет мар.30 и формат опять «дата».

Выделяю ячейку, правой клавишей вызываю контекстное меню, выбираю формат ячейки ставлю текстовый, после забиваю 3/30 или 3-30 и значение не меняется.

Ввожу вот так 5-30 http://prntscr.com/nqfhb0 , получается вот так http://prntscr.com/nqfhg1 , менял формат ячейки на общий до ввода. Мне нужен диапазон от 5 до 30 для формулы массива. от 30-50 нормально получается.

У меня получилось если заранее в ячейке поменять формат на текстовый, а затем вставить диапазон от 5 до 30

Нужно чтобы он раз и навсегда перестал так делать. Каждый раз изменять формат ячеек (особенно учитывая, что экзель забывает о том, какой формат мы ему задали, когда туда забивается число через CTR+V) неудобно.

Есть такие советы:
Приложение Microsoft Excel программируется так, чтобы облегчить ввод дат. Например, 12/2 меняется на 2-дек. Это очень плохо, когда вы вводите что-то, которое вы не хотите менять на дату. К сожалению, отключить эту функцию невозможно. Но есть и другие способы обойти.
Предформатируйте ячейки, в которых нужно вводить числа в виде текста. В этом случае Excel не будет пытаться изменить данные, вводимые в даты.
Если вы хотите ввести несколько чисел, вы можете запретить Excel изменить их на даты, введя:
Пробел перед вводом числа. После нажатия клавиши Ввод пространство останется в ячейке. (См. заметки)
Апостроф (‘) перед вводом числа, например, ‘ 11-53 или ‘ 1/47. После нажатия клавиши Ввод этот апостроф не отображается в ячейке.
Ноль и пробел перед вводом дробной части, например 1/2 или 3/4, чтобы они не переменялись на 2 января или 4 марта, например. Введите 0 1/2 или 0 3/4. После нажатия клавиши Ввод ноль не сохраняется в ячейке, а ячейка становится десятичным числом
Подойдут?

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

Основные принципы работы с датами и временем в Excel

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

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

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

С использованием дефисов

С использованием дроби

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

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

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

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

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:

Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в Excel

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

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

Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

Функция ГОД используется аналогично смежным:

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

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

=ГОД(адрес ячейки с датой в числовом формате)

Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.

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

Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

Введем исходные данные в Excel.

Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:

=ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

В результате мы извлекаем года из каждой даты.

Аналогичный пример работы функции МЕСЯЦ в Excel:

Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

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

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

Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

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

Примеры практического применения функций для работы с датами

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

Допустим у нас имеется простой отчет по продажам:

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

Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:

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

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

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

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

Как: остановить Excel от изменения чисел на даты

Microsoft Excel предварительно запрограммирован для облегчения ввод дат, Например, 12 / 2 становится 2-Dec, что очень расстраивает, когда вы вводите что-то, что не хотите менять для даты. К сожалению, нет возможности отключить его. Но есть способы обойти это.

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

Как предотвратить изменение чисел на даты в Excel:

Трюк Excel

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

• Добавьте апостроф (‘) перед значением. По сути, это имеет тот же эффект, что и установка числового формата в текст. Хорошо, что вы не увидите апостроф в камере.
• Другой способ — добавить пробел. Тем не менее, мы должны отметить, что это не лучший метод, потому что иногда пространство может влиять на значения или формулы ячеек.
• Еще одна хитрость, особенно для дробей без целых чисел, заключается в добавлении 0 и пробела. Эта комбинация может действовать как целочисленная часть и предотвращать превращение значения в число. Excel также удаляет нули и пробелы автоматически.

Исправление обновления января 2020 года:

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

• Шаг 1: Скачать PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista — Microsoft Gold Certified).
• Шаг 2: Нажмите «Начать сканирование”, Чтобы найти проблемы реестра Windows, которые могут вызывать проблемы с ПК.
• Шаг 3: Нажмите «Починить все», Чтобы исправить все проблемы.

(дополнительное предложение для Advanced System Repair Pro -> Cайт | Лицензионное соглашение | Политика Kонфиденциальности | Удалить)

Метод апострофа

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

1. Введите апостроф в клетку.
2. Введите номер так, как вы хотите, чтобы он отображался, и нажмите Enter.
3. Числа теперь должны правильно отображаться и импортироваться.
4. Это позволяет вам использовать функции MATCH и VLOOKUP в Excel (апостроф игнорируется).

Использование кода

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

Выберите следующий код, щелкните его правой кнопкой мыши и выберите «Копировать».

1. Откройте книгу, в которую вы хотите вставить код.
2. Нажмите одновременно клавиши «Alt» + «F1».
3. Нажмите «Вставить» и выберите «Модуль».
4. Выберите «Редактировать», где курсор мигает, и выберите «Вставить».
5. Нажмите «ОК», чтобы добавить его.
6. Нажмите на вкладку «Вид» и выберите «Макросы».
7. Выберите добавленный код для его выполнения.

CCNA, веб-разработчик, ПК для устранения неполадок

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