Excel датамес не работает
Функция ТДАТА в Excel предназначена для определения текущих даты и времени (на момент последнего выполнения формулы) и возвращает соответствующее числовое значение.
Как получить и вставить текущую дату и время в ячейку Excel
Если ячейка, в которой была введена функция ТДАТА, имела формат Общий, он будет автоматически изменен в формат даты, и итоговое значение отобразится в виде записи даты и времени, например, 09.06.2019 19:14. Для получения числового значения как результата выполнения функции, необходимо установить числовой формат данных для требуемой ячейки. Например, в результате будет получено число 43625,80.
Функция ТДАТА удобна для вычислений разницы дат и времени, когда требуется динамически обновляемый результат. Например, некоторые расчеты были проведены в среду, книга Excel была сохранена и закрыта. При открытии книги в пятницу, результаты вычислений с использованием данной функции будут другими (актуальными для пятницы).
Если требуется зафиксировать значения, полученные с использованием функции ТДАТА, можно внести соответствующие параметры для пересчета книги либо скопировать полученное значение (не формулу) и вставить в другую ячейку.
Пример 1. В таблице указаны даты некоторых событий. Рассчитать число дней, прошедших с момента события до сейчас.
Для расчета используем следующую формулу массива:
Поскольку ТДАТА возвращает дробное число (значение даты и времени), отбрасываем дробную часть с помощью функции ОТБР. Результат вычислений:
Для корректного отображения полученных данных необходимо выделить ячейки B2:B6 и установить Общий формат данных.
Для получения такого же результата можно использовать функцию для вставки только даты СЕГОДНЯ:
Вообще функция =ТДАТА() чаще всего применяется в Excel для получения текущего времени:
Или используйте альтернативную формулу вставки текущего времени, не привязанную к формату ячеек:
Так как рассматриваемая функция возвращает дату и время ее можно встретить в разных формулах для обработки дат и времени.
Расчет рабочего года для предоставления отпуска в Excel
Пример 2. В таблице имеются данные о новых сотрудниках и дате их принятия на работу. Добавить столбец, в котором будут отображаться сведения о том, может ли сотрудник получить оплачиваемый отпуск. Согласно политике компании, такой отпуск предоставляется по истечению 120 чистых рабочих дней. Праздниками можно пренебречь.
Вид таблицы данных:
Для расчетов используем следующую формулу:
Функция ЕСЛИ проверяет условие «больше или равно ли число рабочих дней между датой приема сотрудника на работу и текущей датой, рассчитанной функцией ТДАТА?». Если условие выполнится, будет выведено значение «да», иначе – «нет».
Вычислим значение для первого сотрудника и «растянем» формулу вниз по столбцу для определения значений для остальных сотрудников. Полученные результаты:
Для проверки полученных значений проведем расчеты в соседнем столбце с помощью формулы:
Поскольку результаты, полученные с использованием ТДАТА, пересчитываются, со временем в столбце C будут содержаться только строки «да».
Если Вам часто приходится вводить даты и время в ячейки, то Вам должна понравиться идея писать их сокращенно, без точек-дробей-двоеточий - просто как число. Чтобы в заданном диапазоне ячеек листа, например, число 250699 автоматически превращалось в 25.06.1999, а 1125 в 11:25.
Для этого щелкните по ярлычку листа, куда будут вводиться даты и время и выберите команду Исходный текст (Source Code). В открывшееся окно редактора Visual Basic скопируйте и вставьте следующий код:
Диапазоны A2:A10 и B2:B10 замените на свои области листа, куда будут подобным образом вводиться даты и время, соответственно.
Ссылки по теме
Макрос вообще не нужен если формат ячейки поставить "Дата", а значения вводить: 25-06-1999 или 25/06/1999 или 25,06,1999. Если набрать 25/6 или 25-06 . то год подставится текущий.
Не ставьте заранее для ячейки ввода формат даты. Иначе все введенное Excel автоматически преобразует в дату, а точнее - в свой специфический код даты. И макрос работает дальше уже с ним, а не с введенными вами цифрами.
у меня не работает даже в скачанном примере. Если не менять формат ячейки, и ввести, к примеру, 110887, то ничего не меняется, цифры в таком формате и остаются. Если формат ячейки поменять на формат даты, то выдает 06.08.2203
Защита от макросов выключена? Файл - Параметры - Центр Управления Безопасностью - Параметры Центра Управления - Макросы - Разрешить все макросы. И перезагрузиться.
Подскажите, пожалуйста, почему не работает на всю книгу? Если создаю для одного листа - все в порядке. Статью по созданию макросов прочитала, но все равно не получается.
Потому, что этот макрос написан для листа и вставлять его надо в модули листов, а не в модуль ЭтаКнига.
здравствуйте. макрос отличный на много сокращает количество нажатий кнопок на клавиатуре и соответственно времени рутины. при его использовании столкнулся с тем что при внесении даты(например 310113) если ошибочно внес не 6 знаков(например 3113), а другое количество выдает ошибку необходимости отладки кода макроса по 12 строке и макрос перестает работать.подскажите, что можно сделать ?
В случае занесения не 6 символов ошибку выдавать не должно - в 10-й строке идет проверка на наличие именно 6 символов, и если количество введенных цифр не равно 6 - макрос заканчивает работу и ничего не происходит.
Добрый день!
На работе этот макрос используем на нескольких компьтерах ошибка «runtimeerror ‘13’ typemismatch» появляется при ошибочном внесении например «1»или «131312».
После этого макрос перестает работать. Макрос включается только после перезапускаExcel.
Я не могу разобраться почему так происходит.
Подскажите,что нужно сделать что бы:
1. -макрос снова начинал работать без перезапуска Excel
2. - не появлялась на экране ошибка «runtimeerror ‘13’ typemismatch» которая приводит в панику некоторых пользователей.
3. - и посмотрите пожалуйста макрос предложенный Вами чуть чуть мной переделанный. Какие в нем недостатки. В VBA профан за ранее извеняюсь за назойливость .
(файл в котором у меня ошибка прикрепить не могу не нахожу кнопки "прикрепить файл"
А ее тут и нет - это комментарии к приему, а не форум. Лучше создайте тему на форуме и прикрепите ваш файл с макросом - а мы его покритикуем
А зависание макроса происходит, скорее всего, из за того, что вы в коде отключаете обработку событий Application.EnableEvents=False, а после возникновения ошибки она у вас обратно не включается.
Сразу Спасибо за сайт. оч помогает.
а как переделать макрос для времени, если вводится формат 21 . 15 (ну или любой другой знак, всякое бывает), ну а нужен обычный 21:15
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
12 строка подсвечивается для отладки, когда макрос перестаёт работать.
Это происходит, как я заметил, в случае:
1. Сортировки таблицы, несколько столбцов которой заполнены при помощи данного макроса датой и временем.
2. После ошибочного ввода в рабочем диапазоне макроса даты с разделителями, удаления ошибчной записи и последующей попытки заполнить ячейку не применяя разделители, при помощи макроса.
Добрый день! не в коем случаи не хочу обидеть автора статьи, но у меня есть один трюк именно для того, чтобы лишний раз не нажимать shift и какой либо знак. он делается очень легко и для даты и для времени, без всякого там кода VB, сам я ламер в VB поэтому вечно ищу более легкие пути, так вот я делаю так:
для даты:
в формате ячейки -> все форматы 00\.00\.0000
пример:
12122012 -> 12.12.2012
для времени -> все форматы 00\:00
пример:
1212 -> 12:12
после слеша влево можно поставить любой знак, который предпочтителен для Вас.
Надеюсь Вам понравится такая идея.
если у кого будут вопросы или захотите сказать спасибо, я в facebook Parviz Ruziev
Добрый день, описанный вами прием всего лишь придаст форму но не изменит сути, Excel не будет воспринимать введенное значение как дату.
К сожалению, с отофрматированными вашим способом датами нельзя работать. Попробуйте вычесть из одной даты другую, чтобы посчитать количество дней - и получите ерунду. И ни одна функция из категории "Даты и время" с вашими "датами" работать не будет. Так что не советовал бы идти таким путем, при всем уважении.
Посмею предложить свой вариант. )))
В формате ячейки (у меня это D2) выставляем формат типа:
00"."00"."0000
На проверку вводимых данных задаем условия проверки:
тип данных – другой; и на соответствие формуле в соответствующей ячейке (у меня это R2).
Формула такого вида:
=ЕСЛИ(ИЛИ(И(ДЛСТР(D2)=7;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;2;2);"00";))<=12);И(ДЛСТР(D2)=8;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))<=12;ИЛИ(ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=1;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=3;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=5;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=7;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=8;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=10;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=12;);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=31);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=4;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=6;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=9;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=11);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=30);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=2;ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=29);ИСТИНА;ЛОЖЬ)));ИСТИНА;ЛОЖЬ)
Не пойму почему так отображает, в выше приведенной формуле смайлики заменить на ")" без кавычек естественно.
Соответственно дата вводится без каких-либо посторонних знаков, только цифры. Формула проверяет что бы введенный диапазон был между 01.01.1970 и 31.12.2020, при вводе большей или меньшей даты – выдает ошибку, при попытке ввести 13 и т.д. месяц – выдает ошибку, при попытке ввести более 31 дня (в январе, марте, мае, июле, августе, октябре, декабре) – выдает ошибку, при попытке ввести более 30 дней (в апреле, июне, сентябре, ноябре) – выдает ошибку, при попытке ввести более 29 дней в феврале – выдает ошибку. Осталось добить проверку високосных лет. )))))
Ну и перевести это все безобразие в дату тоже не проблема, у меня в ячейке I2 идет подсчет даты от введенной в ячейку D2 плюс 2 месяца вот такой формулой:
=ЕСЛИ(ЕПУСТО(D2);""; ЕСЛИ(ДЛСТР(D2)=7; ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);(СЦЕПИТЬ("0";ЛЕВСИМВ(D2))));2); ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));2)))
Сразу отвечу на вопрос зачем так все усложнять – просто задача стояла сделать без макросов.
а можно чтоб при вводе числа например 5 появлялась дата не текущего а конкретного месяца или хотябы прошлого месяца текущего года, я например делаю отчёт за прошлый месяц и чтоб не вводить значения года и месяца а только день
Принес с работы файл созданный в офисе 2007 который работает хорошо (именно с этим макросом), но дома на офисе 2013 не хочет пишет Could not load an object because it is not available on this machine.(Не удалось загрузить объект, потому что он не доступен на этой машине.) В чем проблема?
Николай здравствуйте! Случайно наткнулся на ваш сайт и . был в восторге! Спасибо огромне за Вашу проделанную работу. Но у меня вопрос к вам, а как макрос будет выглядеть если надо ввести время так: 12:15-13:45 (это в одной ячейке). Заранее спасибо. =Будущий гуру Exel=
P.S. и год как убрать?
Подскажите пожалуйста, если мне нужно даты во многих столбцах (B; N;O; S) начиная с 3 строки указывать, как это прописать в макросе?
Доброго времени суток!
Прошу прощения за вопрос, в VBA новичок. К сожалению, нет времени чтобы изучить возникшую проблему, необходимо срочно сделать форму отчета кассира КМ-6 (форма типовая, скачана из альбома унифицированных форм в Консультанте). Проблема в том, что в этой форме графы "дата составления" и "время работы" представляют собой объединение нескольких ячеек и изменение диапазона указывает на неправильное событие, если я правильно поняла. Какой должен быть макрос, чтобы эта проблема была устранена?
К сожалению, не удается вставить скрин-шот для наглядности.
Заранее благодарна.
Добрый день. Скажите пожалуйста, как можно установить автоматический ввод даты и времени в нескольких столбцах сразу (с помощью макроса, функция Range("F3:F50200")
For Each cell In Target 'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("F3:F50200" ) Is Nothing Then 'если изменененная ячейка попадает в диапазон A2:A100
With cell.Offset(0, 1) 'вводим в соседнюю справа ячейку дату
.Value = Now
.EntireColumn.AutoFit 'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
Next cell
End Sub
Excel не распознает ввод даты на английском. Подскажите, что делаю не так ? )
Если ввожу 10янв14, преобразует в дату 10.01.2014 , формат ячейки становится Custom (все ОК)
Если ввожу 10Jan14, остается в формате General
Пробовал заранее ставить формат ячейки "Date", все равно остается текст 10Jan14
Добрый день, спасибо за сайт и за эту статью. Воспользовалась вашем макросом и вставила его в свой файл "Табель уч.раб. вр." все получилось. Этим файлом еще пользуются руководители подразделений и хотелось бы для них вставить "подсказку" (скриншот как у вас в начале статьи) как всплывающую подсказку, но не знаю как вставлять анимированные картинки, пожалуйста, помогите сделать.
Добрый день, у некоторых пользователей при вводе данных в разные ячейки макрос выдает ошибку Compile erorr: Synteax erorr
Выскакивать окно Visual Basic при любом редактировании значений.
Дико неудобно, при введении каждого значения приходится закрывать окно VB, дважды нажимая на кнопки.
Один пользователь даже пробовал менять дистрибутивы винды и офиса, переустанавливать – везде одно и то же.
Судя по ошибке у стоит библиотека или скрипт, который позволяет выполнять эту процедуру.
Приветствую! Вопрос в следующем, подскажите, какой должен быть код, чтобы можно было редактировать в дальнейшем дату в введенной ячейке? Т.е я ввел 150216, он мне автоматом перевел в 15.02.2016, супер, но, если ошибся на день, пытаешься отредактировать на 14.02.2016, дата меняется на 20.04.1949 и в дальнейшем выскакивает ошибка "Run-time Error 13. Type mismatch". После этого код больше не работает и необходимо закрывать и открывать заново документ. Это первый момент.
Спасибо. Код золотой и очень полезен в работе, но хочется чтобы не было таких ошибок т.к будет активно использоваться в работе. Буду очень благодарен.
Николай! Добрый вечер! Подскажите начинающему плиз. Мне нужно тоже самое, что и в Вашем примере, только формат не часы:минуты:секунды, а просто минуты (единицы минут, несколько):секунды. Всего 3 цифры. Например, 3:40. Никак не получается сделать. Моя конечная цель - автоматизация оценивания учеников по 5 бальной системе по результатам забегов на физкультуре. Может, как-то изменить макрос?
Здравствуйте! Очень сильно стараюсь вычислить из двух результатов времени лучший, это для забегов спортсменов, т.е. 12:55:10 и 12:55:45, как определить с помощью формул в столбе №3 лучшее время (12:55:10) что бы сразу из двух результатов писался лучший. Где то я ошибаюсь в формул, что то ставлю лишнее или на оборот не до ставляю За ранее Спасибо большое.
Николай, добрый день! А почему после защиты листа, на котором применяется макрос, он перестает работать? Защита с ячеек, куда нужно вводить время, снята. Появляется ошибка Run-time error '1004': Нельзя установить свойство NumberFormat класса Range. Как устранить поломку? Спасибо заранее.
Здравствуйте, Николай. Подскажите, как правильно прописать этот макрос, чтоб он выставлял время (без даты) в определенных строках. Например E11:AI11, E12:AI12, E15:AI15, E16:AI16 и т.д.
Ребята или Николай, кто-нибудь в общем)) Помогите
Замечательный код, но вот вопрос:
Если я хочу внедрить этот код для подобного способа ввода даты в колонку где у меня уже был изначально какой-то формат ячеек к сожалению, и на какой бы другой формат я их не менял, результата нет положительного, тогда как я могу вернуть "никакой формат ячеек" или может мне что то в коде вашем поменять можно?
ЗЫ более того, я ввожу эти 6 цифр не в саму ячейку, у меня для этого есть код, с помощью которого вылазит диалоговое окно с просьбой ввести данные, этот код ссылается на нужную ячейку где я собственно и хотел бы увидеть дату. Но опять же эти 6 цифр я ввожу в диалоговое окно, для которого написал код.
Если не понятно что я имею в виду говоря про код и диалоговое окно, то вот немного подробнее:
Я настроил свою таблицу для работы максимально удобно, чтобы не возить мышкой и кликать триста раз по ячейкам, я уже в исходном коде листа прописал несколько полезных штук, таким образом, что мне надо только в начале ввести в определенную ячейку данные, после чего запускается код и цепь разных действий в виде диалоговых окон типа:
Диалоговое окно "Введите дату" - это как раз первый запрос - и мне она нужна не текущая, а та что я сам впишу, ибо это дэдлайн проекта. Текущая дата вводится автоматом в другом месте, это я уже благодаря кстати Николаю реализовал пару-тройку лет назад, за что большое спасибо и не только за это)) В общем мне пока приходится вводить дату полностью или писать типа "9 мая" чтобы быстрее было, но мне гораздо удобнее было бы все вводить на нампаде, раз 6 цифр ввел нажал Enter, вылазит следующее диалоговое окно "введите бюджет" ну и так далее, нампад - энтер, нампад-энтер — быстро и удобно, и не надо бегать по строке по разным ячейкам глазами выискивать, тем более риск ввести не туда, а колонок много и перепутать легко.
Это я для полной картины просто написал, чтобы понимали)) а то мало ли, может если способ ввода реализуется с помощью вот таких диалоговых окон, то этот ваш код для ввода даты не годится.
Заранее благодарю всех и Николая в особенности.
КСТАТИ! Раз у меня есть текущая дата, то может вместо ввода 6 цифр, можно было бы написать код таким образом чтобы при вводе например цифры 3 на столько увеличивалась текущая дата и вводилась бы как раз в ячейку дедлайна в виде даты? если так можно реализовать код, то это была бы просто бомба))
Например у меня есть текущая дата 6 мая в ячейке A22, вылазит диалоговое окно с просьбой ввести число на которое будет увеличена дата ячейки A22 и введена в ячейку K22 с учетом прибавления того числа что я ввел в диалоговое окно, например я ввел цифру 3 и в ячейке K22 появилась дата 9 мая, то есть код поссчитал 6+3=9 и ввел нужные данные в ячейку K22 в формате даты "9 мая" или "09.05.2017" - без разницы
Как считаете можно такое провернуть??
А как доработать чтобы секунды можно было так же отобразить.
пробовал менять код, но не особо получается (
Для вычислений длительностей интервалов дат удобней всего использовать недокументированную функцию РАЗНДАТ() . Этой функции нет в справке EXCEL2007 и в Мастере функций ( SHIFT+F3 ), но она работает ( с некоторыми огрехами ).
Если в ячейке В2 содержится сегодняшняя дата, а в А2 –дата начала отсчета (например, день рождения), то формула: =РАЗНДАТ(A2;B2;"y")&" г. "& РАЗНДАТ(A2;B2;"ym")&" мес. "& РАЗНДАТ(A2;B2;"md")&" дн."
рассчитает сколько лет, месяцев, дней прошло с конкретной даты и вернет результат в виде 37 г. 5 мес. 1 дн .
Как показано в статье о функции РАЗНДАТ() , лучше избегать применения этой функции с аргументом "md", т.к. она может выдать неправильный результат. Поэтому часть формулы РАЗНДАТ(A2;B2;"md") лучше заменить ее эквивалентом: =ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);ДЕНЬ(B2)-ДЕНЬ(A2))
Итоговая формула приведена в файле примера:
Уточним склонения: год/ лет, месяца/ месяцев, дня/ дней
В случае, если получается целое количество лет, то формула вернет результат в виде 37 г. 0 мес. 0 дн. , что не очень красиво.
Воспользовавшись идеями, подсказанными нашим пользователем МСН, также просклоняем месяцев/ месяца и дней/ дня
=ЕСЛИ(РАЗНДАТ(A2;B2;"y");РАЗНДАТ(A2;B2;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(A2;B2;"y")-11;100);9);10);"[ РАЗНДАТ() с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней.
Алгоритм расчета этой функции следующий: сначала функция последовательно прибавляет месяцы к дате начала, до тех пор пока получившаяся дата меньше конечной. Т.е. к 30.01.2015 функция прибавит 1 месяц и получит 30.02.2015, но такой даты не существует, в феврале 2015г. 28 дней! Ничего страшного, функция запоминает, что нужно отнять 2 дня, чтобы получить правильную дату! Затем идет подсчет оставшихся дней в марте - их 16. Затем, функция к 16 дням прибавляет -2, и получаем 14 дней. Естественно, такой расчет не верен.
Получается, что функцию РАЗНДАТ() можно использовать только для расчета полных лет и месяцев , но без дней. Рассмотренные выше формулы будут в основном возвращать правильный результат (иногда, конечно, будет возникать погрешность в 1-2 дня в меньшую сторону), но часто удобство применения этой функции перевешивает требования к точности. Такой подход безусловно не годится для расчета страхового стажа для определения размера пособий по временной нетрудоспособности (об этом см. статью Расчет страхового (трудового) стажа в MS EXCEL ) .
Обратная задача
Теперь решим обратную задачу: задав количество количество лет, месяцев и дней, рассчитаем конечную дату.
Конечную дату будем рассчитывать двумя способами: через функцию ДАТА() и ДАТАМЕС()
Вычислив конечную дату, затем вычислим с помощью РАЗНДАТ() разницу между ними (должна совпасть с заданной). И наконец, из конечной даты получим начальную, вычтя заданное количество лет, месяцев и дней с использованием формул:
Если для начальной даты 15.01.2015 (строка 8 в файле примера, лист Обратная задача ) все вычисления понятны и одинаковы (добавили 1 год, 2 месяца и 5 дней), получили 20.03.2015, разница составила теже 1 год, 2 месяца и 5 дней, расчетная начальная дата совпала с заданной начальной датой, то для 29.01.2015 все не так гладко (см. строку 9). Для наглядности прибавим 1 месяц.
Как мы уже знаем из статьи Функция ДАТАМЕС() в MS EXCEL функции ДАТА() и ДАТАМЕС() не эквивалентны при прибавлении месяцев:
При прибавлении месяцев между ДАТАМЕС() и ДАТА() существует разница. Прибавим к 29.01.2015 один месяц:
- =ДАТАМЕС("29.01.2015";1) вернет 28.02.2015, т.к. 30 февраля не существует, то функция вернет последний день месяца, т.е. 28.02.2014 ("лишние" 2 дня будут отброшены);
- =ДАТА(ГОД("29.01.2015");МЕСЯЦ("29.01.2015")+1;ДЕНЬ("29.01.2015")) вернет 02.03.2015: "лишние" 2 дня (29 и 30 февраля) будут прибавлены к дате.
Расчет с помощью ДАТАМЕС() кажется логичней, хотя функция РАЗНДАТ() с этим не согласна: разницу, совпадающей с заданной она возвращает только для варианта с функцией ДАТА() .
Расчетная начальная дата в обоих случаях не совпадает с заданной начальной датой: получим 01.02.2015 и 28.01.2015 вместо 29.01.2015.
Как быть? Вероятно, решение будет зависеть от конкретной задачи, стоящей перед пользователем. Необходимо помнить, что нужно использовать ДАТАМЕС() при добавлении (вычитании) месяцев, а РАЗНДАТ() - это недокументированная и, соответственно, не гарантирующая правильность расчетов функция. Об этой функции написана статья Функция РАЗНДАТ() - Вычисление разности двух дат в днях, месяцах, годах в MS EXCEL , в которой дается формула, учитывающая вышеуказанную ситуацию (см. раздел "Еще раз о кривизне РАЗНДАТ() ").
Функция ДАТАМЕС в Excel используется для определения даты, отстоящей от даты, указанной в качестве начальной точки отсчета (первый аргумент функции) на определенное число месяцев (второй аргумент) и возвращает соответствующее значение формата Дата.
Примеры как определить дату после нескольких месяцев в Excel
Вид возвращаемого результата функцией ДАТАМЕС зависит от формата, установленного для ячейки. Например, если ячейка имеет Общий формат, функция вернет числовое значение, соответствующее вычисленной дате.
Рассматриваемая функция удобна при проведении расчетов в отношении некоторых событий, которые происходят через определенные промежутки времени, например, регулярные выплаты по задолженности, производимые определенного числа каждого месяца.
Пример 1. В таблице Excel хранятся данные начала выполнения некоторых производственных процессов и расчетные значения числа месяцев, необходимых для их окончания. Вычислить даты окончания производственных процессов. Если число месяцев является дробным числом, округлять их значения по правилам округления.
Вид таблицы данных:
Для нахождения первого значения используем формулу:
Для округления согласно условию вводим функцию ЕСЛИ для проверки условия «превышает ли дробная часть числа, характеризующего количество месяцев, значение 0,5» и выполняем округление, где требуется.
В результате функция автоматически рассчитала и определила даты после пройденного количества месяцев указанного во втором столбце.
Определение дат графика платежей по кредиту в Excel
Пример 2. В банке взят кредит на некоторую сумму сроком на 1 год. Дата заключения договора – 31.05.2018. Выплаты производятся ежемесячно. Если в месяце меньше 31 дня, дата выплаты переносится на первые числа следующего месяца. Составить график выплат.
Вид таблицы данных:
Для определения даты можно использовать функцию =ДАТАМЕС(B2;A3) или альтернативный вариант - =ДАТА(ГОД(B2);МЕСЯЦ(B2)+A3;ДЕНЬ(B2)). В последнем случае будет возвращена дата, отстоящая на 31 день от даты, указанной в ячейке B2. Например, в июле 2018 года было 30 дней. Значит, функция ДАТА вернет 01.07.2018, а функция ДАТАМЕС – 30.06.2018. Используем эту особенность для построения графика выплат.
В ячейке B3 запишем формулу:
С помощью функции ЕСЛИ проверяем, соответствуют ли результаты, полученные двумя указанными выше методами расчета, и выбираем подходящий метод для конкретной ситуации.
Результат вычисления для 1-го периода выплат:
В указанной формуле была зафиксирована ячейка ($B$2), чтобы она работала корректно при «растягивании» по столбцу.
В прошлых уроках мы научились создавать и отображать даты и время в Excel. Сегодня будет заключительный урок из этой серии. Вы познакомитесь с функциями, которые позволяют производить вычисления над датами в Excel, например, вычислять количество рабочих дней между двумя датами. Всего мы рассмотрим 4 таких функции.
ДАТАМЕС()
Возвращает порядковый номер даты, которая отстает от заданной на определенное количество месяцев вперед или назад. ДАТАМЕС имеет всего два аргумента: начальная дата и число месяцев. Если второй аргумент (число месяцев) является положительным числом, то функция вернет будущую дату относительно начальной, а если отрицательным, – то прошедшую.
Как видите, функция ДАТАМЕС возвратила порядковые номера дат, при этом никакой автоматической смены числового формата не произошло. Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:
КОНМЕСЯЦА()
Возвращает порядковый номер последнего дня месяца, который отстает от заданной даты на определенное количество месяцев вперед или назад. В плане синтаксиса, функция КОНМЕСЯЦА очень похожа на ДАТАМЕС и имеет точно такие же аргументы:
Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:
РАБДЕНЬ()
Возвращает порядковый номер даты, которая отстает от заданной на определенное количество рабочих дней вперед или назад. РАБДЕНЬ имеет два обязательных аргумента: начальная дата и количество рабочих дней. Если второй аргумент (количество рабочих дней) является положительным числом, то функция вернет будущую дату относительно заданной, а если отрицательным – то прошедшую.
Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:
Стоит отметить, что нерабочими днями считаются выходные дни и дни, определенные как праздничные. Для функции РАБДЕНЬ выходными считаются суббота и воскресенье. Программа Excel самостоятельно не может определить, какие дни считать праздниками, поэтому предлагает третий необязательный аргумент, где Вы сами можете все указать.
Если в Вашем случае выходные дни отличны от классических субботы и воскресенья, то вы всегда можете воспользоваться функцией РАБДЕНЬ.МЕЖД. По сути это расширенный вариант функции РАБДЕНЬ, который позволяет настроить количество выходных в неделе и на какие дни они приходятся. Функция РАБДЕНЬ.МЕЖД впервые появилась в Excel 2010.
ЧИСТРАБДНИ()
Возвращает количество рабочих дней между двумя датами. Праздничные и выходные дни в это количество не включаются. Функция ЧИСТРАБДНИ является как бы противоположностью функции РАБДЕНЬ, поэтому лишний раз описывать назначение аргументов функции не стоит.
Как видите, результатом вычисления функции оказались 63 рабочих дня. Если бы мы просто вычли из конечной даты начальную, то получили бы 89 дней.
Если в Вашем случае выходные дни отличны от классических субботы и воскресенья, то Вы всегда можете воспользоваться функцией ЧИСТРАБДНИ.МЕЖД. По сути это расширенный вариант функции ЧИСТРАБДНИ, который позволяет настроить количество выходных в неделе и на какие дни они приходятся. Функция ЧИСТРАБДНИ.МЕЖД впервые появилась в Excel 2010.
Как видите, все достаточно просто!
На этой прекрасной ноте мы заканчиваем последний урок данной серии. Этой информации Вам должно хватить, чтобы чувствовать себя вполне уверенно при работе с датами и временем в Excel. Теперь Ваша основная задача применить эти знания на практике. Всего Вам доброго и успехов в изучении Excel!
Читайте также: