Расчет разницы между двумя датами в Excel
Подскажите каким образом расчитать разницу между двумя датами в Excel. Что бы результат отражался в формате: ** лет, ** месяцев, **дней.
Заренее спасибо:D
Заренее спасибо:D
Преобразовать даты в милисекунды прошедшие с 1970 года, вычесть, и преобразовать назад.
А вообще есть встроенная функция
Или на VBA ее нет?
http://www.w3schools.com/vbscript/func_datediff.asp
А вообще есть встроенная функция
Код:
datediff
Или на VBA ее нет?
http://www.w3schools.com/vbscript/func_datediff.asp
Lida_k, формулировка Вашей задачи либо не вполне корректна, либо предполагает весьма сложное решение, т.к. и продолжительность месяцев в течение года разная, и продолжительность високосного и обычного годов тоже разная.
Если сложного решения не предполагается, то необходимо сделать некоторые допущения:
- считать продолжительность любого месяца равной 30 дням;
- считать продолжительность любого года равной 365 дням.
В упрощённом случае решение может быть таким:
Если сложного решения не предполагается, то необходимо сделать некоторые допущения:
- считать продолжительность любого месяца равной 30 дням;
- считать продолжительность любого года равной 365 дням.
В упрощённом случае решение может быть таким:
Код:
Sub Example()
Dim myDat1 As String, myDat2 As String
Dim xYY As Integer, xMM As Integer, xDD As Integer
myDat1 = "01.01.2004"
myDat2 = "01.03.2006"
xDD = DateDiff("d", myDat1, myDat2)
xYY = xDD \ 365
xDD = xDD Mod 365
xMM = xDD \ 30
xDD = xDD Mod 30
MsgBox "Лет: " & xYY & vbCr & "Месяцев: " & xMM & vbCr & "Дней: " & xDD
End Sub
Dim myDat1 As String, myDat2 As String
Dim xYY As Integer, xMM As Integer, xDD As Integer
myDat1 = "01.01.2004"
myDat2 = "01.03.2006"
xDD = DateDiff("d", myDat1, myDat2)
xYY = xDD \ 365
xDD = xDD Mod 365
xMM = xDD \ 30
xDD = xDD Mod 30
MsgBox "Лет: " & xYY & vbCr & "Месяцев: " & xMM & vbCr & "Дней: " & xDD
End Sub
Lida_K, Не надо никакого VBA ведь всё решается очень просто :
A1=15.12.2000
A2=СЕГОДНЯ()
A3=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"M")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"
A1=15.12.2000
A2=СЕГОДНЯ()
A3=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"M")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"
[QUOTE=pashulka]Lida_K, Не надо никакого VBA ведь всё решается очень просто...[/QUOTE]
pashulka, для пары дат [color=blue]01.01.2004[/color] и [color=blue]01.01.2006[/color] у меня получается такая разность: [color=blue]2 лет, 24 месяцев, 0 дней[/color]. Подобные же результаты получаются и в других случаях, когда интервал между датами больше или равен продолжительности года.
pashulka, для пары дат [color=blue]01.01.2004[/color] и [color=blue]01.01.2006[/color] у меня получается такая разность: [color=blue]2 лет, 24 месяцев, 0 дней[/color]. Подобные же результаты получаются и в других случаях, когда интервал между датами больше или равен продолжительности года.
Dmitrii, Это называется старость - не радость, а правильный вариант формулы, конечно же должен выглядеть так :
A1=01.01.2004
A2=01.01.2006
A3=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"YM")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"
A1=01.01.2004
A2=01.01.2006
A3=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"YM")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"
[QUOTE=pashulka]... правильный вариант формулы, конечно же должен выглядеть так...[/QUOTE]
Или так, например:
[color=blue]=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"M")-РАЗНДАТ(A1;A2;"Y")*12&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"[/color]
Кстати, ни в Excel 2000, ни в Excel 2003 функции РАЗНДАТ() нет в списках мастера функций, хотя в справке обеих версий описание этиой функции есть. Причём, кажется мне, что это не единственный такой случай.
Или так, например:
[color=blue]=РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"M")-РАЗНДАТ(A1;A2;"Y")*12&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"[/color]
Кстати, ни в Excel 2000, ни в Excel 2003 функции РАЗНДАТ() нет в списках мастера функций, хотя в справке обеих версий описание этиой функции есть. Причём, кажется мне, что это не единственный такой случай.
В принципе, первоначальный вариант формулы конечно же можно и усложнить, но … если есть более простой вариант, то весьма и весьма желательно использовать именно его. Что же касается функции =РАЗНДАТ() то это недобитые остатки Lotus и таких функций немного (ДОЛЛСША, РАЗНДАТ, СТРОКАДАННЫХ, СТРОКАЧИСЕЛ ...)
Почему возник такой вопрос: бухгалтеру нужно точно определить при увольнении сотрудника сколько лет, месяцев и дней он проработал на предприятии. Т.е. мне важно при вычислении учесть сколько за этот период было високосных годов, невисокосных, учитывать точное количество дней конкретного месяца в году. В данный момент самое точное что можно вычислить это количество дней между двумя датами, но теперь этот результат надо разбить хотя бы на месяцы. Спасибо
Lida_K, Для этих целей как раз и нужно использовать функцию =РАЗНДАТ(), а примеры рассчёта полного количества месяцев есть в этом топике.
А, а я думал пишите свой возрастник - есть такие жаба скрипты, которые вычисляют "разндат" между введеной и текущей и сообщают, типа: "Нам уже 1 год, 3 месяца, 11 дней". На родительских форумах такое повсеместно распространено.
Чтобы учесть всю календарные девиации надо использовать календарь.
Чтобы учесть всю календарные девиации надо использовать календарь.
Всем огромное спасибо за помощь. РАЗНДАТ() работает отлично. Хотя интересно почему эту функцию не документировали в Excel, ведь подобная задача нахождения разницы двух дат довольно часто встречается. И реализация через хитрые скрипты VBA довольно сложна.
Lida_K, Ответ на вопрос касательно документации могут дать только разработчики, что же касается сложных рассчётов в VBA, то их можно сократить, если непосредственно в макросе использовать функцию =РАЗНДАТ() Причём, результат можно получить даже без ввода функции в ячейку, но для этого нужно использовать Evaluate()
Добрый день!
У меня похожая проблема... у меня не получается посчитать разницу между часами в Excel. Например указано время:
А1: 15:00 (формат времени)
А2: 16:20 (формат времени)
Как посчитать, чтобы в ячейке А3 отображалась числовое значение разницы этого времени, т.е. не 1:20, а 1,3
А3: =1,3 (формат числа)
Я пробовала математической формулой как разницу, а потом просто умножала на 24, но там тянутся сотые и тысячные доли, т.е. не 1,3, а 1,33... и т.д.
Заранее спасибо :)
У меня похожая проблема... у меня не получается посчитать разницу между часами в Excel. Например указано время:
А1: 15:00 (формат времени)
А2: 16:20 (формат времени)
Как посчитать, чтобы в ячейке А3 отображалась числовое значение разницы этого времени, т.е. не 1:20, а 1,3
А3: =1,3 (формат числа)
Я пробовала математической формулой как разницу, а потом просто умножала на 24, но там тянутся сотые и тысячные доли, т.е. не 1,3, а 1,33... и т.д.
Заранее спасибо :)
Разобралась, спасибо :)
у меня также не получается посчитать разницу между датой и часами в Excel. Например:
у меня есть 07.12.2009 6:00
и 08.12.2009 15:30
или 07.12.2009 6:00 и 07.12.2009 19:23
мне нужно узнать количество часов и минун за єтот промежуток времени.
тоесть что-бы вышло вот так как я считаю вручную
07.12.2009 6:00 и 08.12.2009 15:30 = 33:30
07.12.2009 6:00 и 07.12.2009 19:23 = 13:23
Пдскажите плиз какую формулу нужно использовать...
Уже замаялся эта округляет =ЦЕЛОЕ(([Столбец2]-[Столбец1])*24)
а что делать ума не приложу мози кипят, все на что я способен уже делал но все без результата...
у меня есть 07.12.2009 6:00
и 08.12.2009 15:30
или 07.12.2009 6:00 и 07.12.2009 19:23
мне нужно узнать количество часов и минун за єтот промежуток времени.
тоесть что-бы вышло вот так как я считаю вручную
07.12.2009 6:00 и 08.12.2009 15:30 = 33:30
07.12.2009 6:00 и 07.12.2009 19:23 = 13:23
Пдскажите плиз какую формулу нужно использовать...
Уже замаялся эта округляет =ЦЕЛОЕ(([Столбец2]-[Столбец1])*24)
а что делать ума не приложу мози кипят, все на что я способен уже делал но все без результата...
=ТЕКСТ(A2-A1;"[ч]:мм")
Эта формула, естественно, будет возвращать текст, если сие нежелательно, то просто используйте =A2-A1 не забыв установить формат ячейки с формулой как [ч]:мм или [ч]:мм:сс (если секунды всё-таки могут быть важны)
Эта формула, естественно, будет возвращать текст, если сие нежелательно, то просто используйте =A2-A1 не забыв установить формат ячейки с формулой как [ч]:мм или [ч]:мм:сс (если секунды всё-таки могут быть важны)
Подскажите!
А как теперь в MS Office Excel сложить ячейки с получившимся результатом (по формуле: =РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"YM")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"), чтобы получить сумму результатов ячеек в том же формате "Х лет, Х месяцев, Х дней"?
[ATTACH=CONFIG]4992[/ATTACH]
А как теперь в MS Office Excel сложить ячейки с получившимся результатом (по формуле: =РАЗНДАТ(A1;A2;"Y")&" лет, "&РАЗНДАТ(A1;A2;"YM")&" месяцев, "&РАЗНДАТ(A1;A2;"MD") &" дней"), чтобы получить сумму результатов ячеек в том же формате "Х лет, Х месяцев, Х дней"?
[ATTACH=CONFIG]4992[/ATTACH]
Вы можете предварительно суммировать все начальные и конечные даты и уже к полученным результатам применить функцию =РАЗНДАТ() , но это будет, конечно же, не общий возраст, а всего лишь имитация
Цитата: pashulka
Вы можете предварительно суммировать все начальные и конечные даты и уже к полученным результатам применить функцию =РАЗНДАТ() , но это будет, конечно же, не общий возраст, а всего лишь имитация
Не понял, что значит предварительно суммировать все начальные и конечные даты, если нужно как раз вычитание дат, а не их сумма. Суммировать нужно уже полученные результаты после вычетания...
Я скачал две проги (одна бесплатная "Расчет стажа 1.41", другая shareware "Расчетчик стажа 6.1.1.0"), которые самостоятельно считают стаж по исходным парам дат.
Исходя из возможностей этих прог, ищу возможность автоматизировать процесс расчета стажа в MS Office Excel.
Сказано же в писании, предварительно, т.е. допустим в ячейках "A1:10" находятся даты рождения, а в ячейках "B1:B10" даты упокоения, в таком случае
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"Y")
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"YM")
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"MD")
Вы получите данные - довольно близкие к общему возврасту (стажу)
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"Y")
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"YM")
=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);"MD")
Вы получите данные - довольно близкие к общему возврасту (стажу)
Цитата: pashulka
...Вы получите данные - [COLOR="#ff00ff"]довольно близкие[/COLOR]:confused: к общему возврасту (стажу).
Меня смущает понятие "довольно близкие"! Не уж то батюшка Билл не предусмотрел возможность использования его продукта (MS Office Excel) сотрудниками "Human Resource Manager".
Уверен, что процесс просчёта стажа (по крайней мере общего) возможно автоматизировать и получить [COLOR="#a0522d"]ТОЧНЫЕ[/COLOR] результаты. Вот не знаю где бы эти формулы достать (формулы, которые используют сотрудники пенсионного фонда для просчёта стажа).
Как я уже указывал, я скачал две проги (одна бесплатная "Расчет стажа 1.41", другая shareware "Расчетчик стажа 6.1.1.0"), которые самостоятельно считают стаж по исходным парам дат.
Парадокс: я получил [COLOR="#006400"]ЧЕТЫРЕ![/COLOR] разных результата - два результата мне дали указанные программы, один результат Excel и ещё один я просчитал на старой доброй бумаге.
Как определить кто есть "Ху"?
Вот здесь приводится тот же алгоритм. А что касается точности, то при подсчёте возраста, лично я, выберу Excel, ибо там учитываются високосные года, да и полученные данные уже не раз проверялись дотошными суб'ектами.
Чтобы получить результативную ячейку, я осуществил слияние формул. Вот, что получилось:
=РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"Y")&" роки, "&РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"YM")&" місяці, "&РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"MD")&" днів"
А вот так выглядит результат:[ATTACH=CONFIG]4993[/ATTACH]
=РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"Y")&" роки, "&РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"YM")&" місяці, "&РАЗНДАТ(СУММ(A2:A17);СУММ(B2:B17);"MD")&" днів"
А вот так выглядит результат:[ATTACH=CONFIG]4993[/ATTACH]
Вас смущает полученный результат ? Или это просто отчёт о проделанных действиях :)
Не нужна никакая функция РазнДат!
Все намного проще!
Для разности дат (например, =A2-A1, где A1 = 13.11.1991, A2 = 08.05.2011)
задаем формат ячейки:
ГГ - видим 19 (лет)
ММ - видим 06 (месяцев)
ДД - видим 26 (дней)
Вот и все! В VBA это сделать сложнее!
---------------------------------------------------------------
См. форматы:
Дни, месяцы и годы Для отображения чисел в формате даты (с указанием числа, месяца и года) используются коды, указанные ниже.
М ОТОБРАЖЕНИЕ МЕСЯЦА В ВИДЕ ЧИСЛА БЕЗ НАЧАЛЬНОГО НУЛЯ.
ММ Отображение месяца в виде числа с начальным нулем, если он необходим.
МММ Отображение сокращенного названия месяца (янв — дек).
ММММ Отображение полного названия месяца (январь — декабрь).
МММММ Отображение месяца в виде одной буквы (я — д).
Д Отображение дня в виде числа без начального нуля.
ДД Отображение дня в виде числа с начальным нулем, если он необходим.
ДДД Отображение сокращенного названия дня недели (пн — вс).
ДДДД Отображение полного названия дня недели (понедельник — воскресенье).
ГГ Отображение года в виде двузначного числа.
ГГГГ Отображение года в виде четырехзначного числа.
Все намного проще!
Для разности дат (например, =A2-A1, где A1 = 13.11.1991, A2 = 08.05.2011)
задаем формат ячейки:
ГГ - видим 19 (лет)
ММ - видим 06 (месяцев)
ДД - видим 26 (дней)
Вот и все! В VBA это сделать сложнее!
---------------------------------------------------------------
См. форматы:
Дни, месяцы и годы Для отображения чисел в формате даты (с указанием числа, месяца и года) используются коды, указанные ниже.
М ОТОБРАЖЕНИЕ МЕСЯЦА В ВИДЕ ЧИСЛА БЕЗ НАЧАЛЬНОГО НУЛЯ.
ММ Отображение месяца в виде числа с начальным нулем, если он необходим.
МММ Отображение сокращенного названия месяца (янв — дек).
ММММ Отображение полного названия месяца (январь — декабрь).
МММММ Отображение месяца в виде одной буквы (я — д).
Д Отображение дня в виде числа без начального нуля.
ДД Отображение дня в виде числа с начальным нулем, если он необходим.
ДДД Отображение сокращенного названия дня недели (пн — вс).
ДДДД Отображение полного названия дня недели (понедельник — воскресенье).
ГГ Отображение года в виде двузначного числа.
ГГГГ Отображение года в виде четырехзначного числа.
Цитата: AlexXimik
Не нужна никакая функция РазнДат!
Все намного проще!
Все намного проще!
А ведь и правда проще. Достаточно просто посмотреть на дату последнего сообщения. :)
Цитата: Alexander92
А ведь и правда проще. Достаточно просто посмотреть на дату последнего сообщения. :)
..и немного подумать (про AlexXimik):
Цитата:
10.03.1996 10.03.1997 30.12.00
10.02.1996 10.02.1997 31.12.00
10.02.1995 10.02.1996 30.12.00
10.02.1995 11.02.1996 31.12.00
10.02.1995 12.02.1996 01.01.01
что, разница между 12м и 10 февраля соседних лет 1 месяц и один год?
Народ, а как наоборот, к определённой дате нужно прибавить нужное количество месяцев?
Для разности дат (например, =A2-A1, где A1 = 13.11.1991, A2 = 08.05.2011)
задаем формат ячейки:
ГГ - видим 19 (лет)
ММ - видим 06 (месяцев)
ДД - видим 26 (дней)
================================================
И что получается за 19 лет - 4 высокосных года (если год идет из расчета - 365 дней + 4 дня, месяцев 6 - а это 183 дня) - а если мне нужно эту разницу перевести только в часы и минуты?! Тогда ак быть?!
задаем формат ячейки:
ГГ - видим 19 (лет)
ММ - видим 06 (месяцев)
ДД - видим 26 (дней)
================================================
И что получается за 19 лет - 4 высокосных года (если год идет из расчета - 365 дней + 4 дня, месяцев 6 - а это 183 дня) - а если мне нужно эту разницу перевести только в часы и минуты?! Тогда ак быть?!
Разница в часах :
=(A2-A1)*24
в минутах :
=(A2-A1)*1440 '=(A2-A1)*24*60
Если же нужен только визуальный эффект, то ответ уже наличествует в посте от 08 декабря 2009 года, т.е. форматы [ч] и [м] соответственно.
=(A2-A1)*24
в минутах :
=(A2-A1)*1440 '=(A2-A1)*24*60
Если же нужен только визуальный эффект, то ответ уже наличествует в посте от 08 декабря 2009 года, т.е. форматы [ч] и [м] соответственно.
Помогите проблема! есть время в которое совершались звонки. есть их стоимость. необходимо: Для звонков, совершенных в ночное время (после 23:00 и до 6:00) установите скидку в 30% от стоимости.
какую формулу использовать ? и как задать критерий с 23:00 по 6:00?
какую формулу использовать ? и как задать критерий с 23:00 по 6:00?
Если не знаете , как вычесть можете тут http://CalcSoft.ru/calculator-dney высчитать столько, сколько вам нужно , любую дату, даже вбейте в Exel!))