Формат даты Excel без года

Формат даты Excel без года

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

Есть ли способ сохранить формат даты, но не включать год автоматически?

6 ответов

Короткий ответ :
Невозможно иметь дату без года, это просто обязательная часть даты. Точно так же, как вторая часть является обязательной частью времени суток — если вы входите, скажем, «1:15», фактическое значение будет «01:15:00» (или что-то в этом роде, в зависимости от ваших региональных настроек) , Единственное, что вы можете сделать, это форматировать столбец как текст, использовать формат даты, который скрывает год, или трюки, подобные использованию формул, чтобы извлечь день и месяц в другой столбец и скрыть столбец с фактической датой.

Объяснение :
Excel хранит даты как количество дней с 0 (да, ноль) января 1900 года. Так что сегодня, 23 августа 2012 года, будет храниться как 41144, а 23 августа 2008 года будет сохранено как 39683. Из-за этого Excel требуется , чтобы узнать год, чтобы рассчитать правильное количество дней. 23 августа само по себе является неоднозначным, поскольку он может решить более 100 различных значений (по одному на каждый год с 1900 года), поэтому Excel добавляет сам год, если вы его опустите.

В течение дня Excel добавляет дробную часть к числу дней, которое представляет собой долю 24-часового дня, прошедшего. Например, 06:00 (или 6 AM) сохраняется как 0,25, потому что в этот момент прошло 25% дня.

Вы можете увидеть это в действии, указав дату и /или время (или просто =NOW() для текущей даты /времени) в ячейку в Excel и изменение формата на Number. Вам также нужно будет увеличить число десятичных знаков, чтобы увидеть полную точность.

Например, текущее время (20:19 в моем часовом поясе, начиная с написания этого) сохраняется как 41144.846945255.

Насколько я знаю, это невозможно. Вы можете отформатировать ячейку как mm /dd, но если это дата, она состоит из трех частей: месяца, дня и года. Excel добавит вам год, когда вы введете только месяц и день. Вы увидите его как mm /dd, но поскольку дата по определению должна иметь год в нем для выполнения арифметики даты, у вас нет выбора действительно. Единственный способ, которым я могу это сделать, — форматировать его как текстовое поле. Но вы потеряете преимущества формата данных. Что вам нужно, позвольте спросить? В большинстве случаев вы можете избежать использования года в своей арифметике, если это вас беспокоит.

Создайте два столбца. В первом просто положить месяц. Во втором поместите день, разделенный пробелом, используя 0 для однозначных дней. Итак, 2 апреля будет читать First Column: April, Second Column 0 2. Затем Excel не читает его как дату и не форматирует. 🙂

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

Вам нужно перейти к значку «Текст в столбцы». Нажмите на это.

Оставьте флажок «Разграничено», нажмите «Далее».

Нажмите «Другое». (в этот момент мои даты — xx /xx /xxxx с косой чертой). В поле рядом с «другим» я помещаю в /, нажмите «Далее»

На следующем экране — выберите «Общие» и нажмите «Готово».

Теперь ваши даты будут в 3 столбцах. Столбец месяца будет выглядеть неактуальным, следующий столбец будет днем ​​и последним в году. Не беспокойтесь.

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

Удалить столбец в течение года, так как вы этого не хотели.

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

Хотите, чтобы они вместе? Скажем, месяц находится в B1, а день — в столбцах B2. Справа или используя пустой столбец, примерная формула: =Concatenate(b1,»/»,b2) и Enter .

Это даст вам что-то похожее на 12/31. Теперь скопируйте это 12/31 и вставьте весь путь вниз по длине вашей таблицы /дат и там вы идете!

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

Функция РАЗНДАТ — вычисляет количество дней, месяцев или лет между двумя датами. Начальная дата должна быть раньше или совпадать с конечной, иначе функция вернет -1.

Формат

РАЗНДАТ(начальная_дата; конечная_дата; «единица»)

DATEDIF(начальная_дата; конечная_дата; «единица»)

• дата пишется строго в порядке день-месяц-год;
• день и месяц можно писать как с ведущим нулем, так и без него, например, 01.01.2000, 15.10.2000, 1.1.2000;
• год можно писать как в 4 цифры (01.01.2015), так и в 2 (01.01.15) (год из двух цифр интерпретируется как год текущего века — 08.12.93 = 08.12.2093, 08.12.00 = 08.12.2000);
• разделителем в дате может быть любой символ (кроме цифры). Если разделитель — точка (15.10.2020), дату не нужно брать в кавычки, а если / или — (15/10/2020 или 15-10-2020) — то только в кавычках.

• d — разница в днях.

Пример 1: начальная_дата 25.02.2007, конечная_дата 26.02.2007 Результат: 1 (день).

Пример 2: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Результат: 28 (дней)

Пример 3: начальная_дата 28.02.2008, конечная_дата 01.03.2008 Результат: 2 (дня), т.к. 2008 год — високосный

• m — разница в полных месяцах.

Пример 1: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Результат: 1 (месяц)

Пример 2: начальная_дата 01.03.2007, конечная_дата 31.03.2007 Результат: 0

Пример 3: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 25 месяцев

Пример 4: начальная_дата 31.03.2007, конечная_дата 01.05.2007 Результат: 1 (месяц)

Пример 5: начальная_дата 01.04.2007, конечная_дата 01.05.2007 Результат: 1 (месяц)

Пример 6: начальная_дата 31.03.2007, конечная_дата 30.04.2007 Результат: 0

• y — разница в полных годах.

Пример 1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 2 (года)

Пример 2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 1 (год)

• ym — разница в полных месяцах без учета лет.

Пример 1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 1 (месяц) — сравниваются конечная дата 01.03.2009 и модифицированная начальная дата 01.02. 2009 (год начальной даты заменяется годом конечной даты, т.к. 01.02 меньше, чем 01.03).

Пример 2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 11 (месяцев) — сравниваются конечная дата 01.03.2009 и модифицированная начальная дата 01.04. 2008 (год начальной даты заменяется годом конечной даты за вычетом 1 года , т.к. 01.04 больше чем 01.03).

• md — разница в днях без учета месяцев и лет.

Начальная дата выравнивается под конечную дату для сравнения с ней.

Пример 1: начальная_дата 31.08.2007, конечная_дата 01.05.2008 — начальная дата преобразуется в 31.04.2008. Но в апреле 30 дней, поэтому 31-ое апреля подравняется и станет 30-м апреля. Так же работает и с февралем, включая високосные годы (т.е. не будет несуществующих чисел февраля, вместо этого они заменятся последним числом февраля для года конечной даты).

• yd — разница в днях без учета лет.

Пример 1: начальная_дата 01.01.2007, конечная_дата 31.12.2007 Результат: 364 (дня)

Пример 2: начальная_дата 01.01.2008, конечная_дата 31.12.2008 Результат: 365 (дней), т.к. 2008 год — високосный

Дата плюс дни excel

Как определить сколько календарных дней прошло с начала месяца до текущей даты и сколько дней осталось до окончания месяца?

Для решения этой задачи понадобится знание 3-х функций:

• СЕГОДНЯ() – обновляемая дата.
  TODAY()
• КОНМЕСЯЦА(Нач_дата;Число_месяцев) – определяет дату последнего дня месяца, заданную смещением по количеству месяцев до или после начальной даты
  EOMONTH(start_date;months)
• ДЕНЬ(Дата_в_числовом_формате) – определяет число месяца: число от 1 до 31.
  DAY(Serial_number)

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

Дальнейший расчет совсем прост – разница между 2-мя известными датами, совмещая в одну формулу, получаем:

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

Определить дату 1-го дня месяца можно по формуле: =ДАТА(ГОД(СЕГОДНЯ();МЕСЯЦ(СЕГОДНЯ());1)

Оставьте комментарий!

На сообщение “Расчет количества дней от даты начала текущего месяца до текущей даты и от текущей даты до даты окончания текущего месяца” комментариев 15

1. DenN :
   01.07.2014 (12:25)

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

Конечно просто! ведь Excel прекрасно умеет работать с датами, правда отчет ведется с 01.01.1900

Простота решений не может не радовать!

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

Можно еще начальную дату месяца определить тоже через функцию =КОНМЕСЯЦА(сегодня();-1)+1

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

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

Полезное дополнение в моим знаниям

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

эти формулы гораздо проще, чем когда-то пытался написать я сам

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

Вазген, посмотрите здесь http://mirexcel.ru/?p=276
второй способ Вам в помощь!

Отлично,а то с датами мало работала

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

Как посчитать возраст в excel

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

Видео: Дата в excel. Как вычислить с помощью формул?

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

Первый вариант.

Как посчитать в Excel количество лет, месяцев и дней возраста. В ячейке А1 стоит дата рождения. В ячейке В2 стоит текущая дата, если нам нужно посчитать возраст на сегодняшний день.

В ячейке С1 пишем формулу:

РАЗНДАТ – это название функции.
y – считает года
ym – считает месяца
md – считает дни
«г.», «мес.», «дн.» написанные между знаками & — так будут подписаны года, месяца и дни в итоговой дате.

Если нужно посчитать возраст человека на определенную дату (не текущую), то в ячейку В2 пишем эту дату.

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

Если достаточно посчитать количество полных лет, то делаем так. В ячейке А1 стоит дата рождения.

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

В ячейке D1 пишем формулу. Ставим «Равно», затем находим функцию «ДОЛЯГОДА». Она находится на закладке «Формулы» -> «Дата и время». В появившемся диалоговом окне указываем адреса ячеек с датами.

В строке «Нач_дата» ставим адрес ячейки с датой рождения.
В строке «Кон_дата» ставим адрес ячейки с сегодняшней (текущей) датой.

В этом окне уже виден результат подсчета (54,85). Нажимаем «ОК». Получилась такая формула:

Получилось 54 года. А цифры после запятой (85) – это не месяцы. Их нужно убрать, округлить, но по специальной формуле. Это легко.

В ячейке Е1 установим функцию «ОКРВНИЗ».

Внимание! Есть ещё функция «ОКРУГЛВНИЗ» — она нам сейчас не подходит. Итак, заходим на закладку «Формулы» -> «Библиотека функций» -> «Вставить функцию».

Выйдет окно «Мастер функций». В разделе «Категория» выбираем «Математические». В окне «Выберите функцию» выбираем «ОКРВНИЗ».Нажимаем «ОК».

В диалоговом окне в строке «Число» указываем адрес ячейки с числом, которое хотим округлить. В строке «Точность» ставим единицу.

Нажимаем «ОК». Результат виден уже в этом окне (54). Получилась такая формула:

Можно скрыть столбец D и столбец B (например, в анкете). Нажимаем правой мышкой на букву D (адрес столбца), выбираем «Скрыть».

Третий вариант.

Как посчитать в Excel количество полных лет можно посчитать и так.

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

В ячейке С1 пишем формулу:

Формат ячеек с датами должен быть «Дата».

Где найти эту функцию и как установить её, смотрите в статье «Число Excel. Формат» здесь. Смотрите следующую статью по расчетам дат «Прибавить в Excel к дате дни».

Глава 12. Выборка из диапазона дат с помощью критерия в ином формате

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Давайте подробнее изучим работу этих пяти формул.

• Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
• Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
• Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
• Формулы с вспомогательными столбцами как правило работают быстрее.

• Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
• Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
• Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: » >=1 » &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
• Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
• Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.

• Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
• В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
• Эта формула работает быстрее, чем формулы [4] и [5].

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

• Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.

Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)