Расчет даты исключая периоды - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Расчет даты исключая периоды - Мир MS Excel

Старая форма входа

Страница 1 из 4 1 2 3 4 »
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет даты исключая периоды

Расчет даты исключая периоды

Дата: Пятница, 03.08.2012, 10:44 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Помогите воплотить такое.
Есть 2 колонки (формат дата): 1-я начало работ, 2-я окончание работ
В 3-й считается между ними разница, дающая длительность работ.
Необходимо исключить из длительности нерабочее время. Для определенности рабочее время будни с 8-00 до 17-00
\Время начала работ может попадать сразу в нерабочее время\

Сообщение отредактировал ALARMus - Пятница, 03.08.2012, 10:45

Ответить

Дата: Пятница, 03.08.2012, 11:23 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19188

Репутация: 4421 ±

Замечаний: ±

Excel 365 & Mac Excel

А если просто вычесть?
Например, в A1 8:00, в B1 17:00, в C1, например, обеденный перерыв 1:00

Code

=B1-A1-C1

дает 8:00

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 03.08.2012, 11:26 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

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

Я пока домыслил, если дата в колонках отличается, то надо от разницы этих колонок отнимать еще 16 часов для разницы дат в 1 день, 32 часа для разницы дат в 2 дня,...
Но пока не знаю как учесть:
- выходные
- разные месяца (год пока что один)
- если начало работ зафиксировано после 17-00, но до 8-00 следующего дня

Вот еще что додумал:
1)
если разница дней 0, то просто вычесть конец и начало
если разница дней 1, то 17-00 вычесть начало + конец вычесть 8-00 +0
если разница дней 2, то 17-00 вычесть начало + конец вычесть 8-00 +9
если разница дней 3, то 17-00 вычесть начало + конец вычесть 8-00 +18

2)
подсчитать количество суббот + воскресений между началом и концом, за каждое найденное количество отнять из результата 1) 9

Только как это реализовать (п.п. 1 и 2) и как узнать количество дней если начало и конец в разных месяцах ?
Или я слишком нагромоздил и можно проще ?

Сообщение отредактировал ALARMus - Пятница, 03.08.2012, 11:44

Ответить

Дата: Пятница, 03.08.2012, 11:32 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 19188

Репутация: 4421 ±

Замечаний: ±

Excel 365 & Mac Excel

Нерабочее время тоже с датами? Лучше пример покажите

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 03.08.2012, 11:49 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

вот файлик.
Первая колонка это как раз то что Вы предложили сначала. smile

smile

К сообщению приложен файл: 0878978.xlsx (16.9 Kb)

Ответить

Дата: Пятница, 03.08.2012, 12:31 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

воспользовался функцией ЧИСТРАБДНИ и делил ее на 3 - получил общее число рабочих часов (*).
Осталось воплотить:
(*) - [ДАТА НАЧАЛА - начало этого рабочего дня (8-00)] - [конец этого рабочего дня (17-00) - КОНЕЦ РАБОТ]

Как вычислить то что в [] ?

Ответить

Дата: Пятница, 03.08.2012, 13:10 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Вот что удалось пока что.
Синие шапки - колонки как видите там где не удается получить нужные цифры
А также есть ячейки помеченные зеленым - там время начала и время окончания зафиксированы в нерабочее время !

К сообщению приложен файл: 7244337.xlsx (26.4 Kb)

Ответить

Дата: Пятница, 03.08.2012, 13:40 | Сообщение № 8

Группа: Пользователи

Ранг: Участник

Сообщений: 68

Репутация: 17 ±

Замечаний: 0% ±

2007,2013

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

К сообщению приложен файл: new.xlsx (17.4 Kb)

Ответить

Дата: Пятница, 03.08.2012, 14:04 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

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

cry

Сообщение отредактировал ALARMus - Пятница, 03.08.2012, 14:24

Ответить

Дата: Пятница, 03.08.2012, 14:52 | Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2748

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Чисто вне конкурса, но по теме. Нетрадиционное использование свойств объектов Excel для нужд - в данном случае - подсчета времени. Недавно я подобным образом выступал на другом форуме по вопросу, связанному с датами: http://www.sapboard.ru/forum....p456070

Там мой юмор не очень оценили, а мне понравилось. Надеюсь на бОльшее понимание в эксельном сообществе wink

wink

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

[vba]

Code

Sub nonTraditionalTimeCalculate()

     Dim tBegPlan    As Date
     Dim tEndPlan    As Date
     Dim tBegFact    As Date
     Dim tEndFact    As Date

     Dim tOutPlan    As Date

     Dim addrPlan    As String
     Dim addrFact    As String

     Dim rngPlan     As Range
     Dim rngFact     As Range
     Dim rngIntesect As Range


     tBegPlan = TimeSerial(8, 0, 0)  'официальное начало работы
     tEndPlan = TimeSerial(17, 0, 0) 'официальное окончание работы

     tBegFact = TimeSerial(6, 0, 0)  'фактическое начало работы
     tEndFact = TimeSerial(19, 0, 0) 'фактическое окончание работы

     addrPlan = "A" & CStr(tBegPlan * 86400 + 1) & ":A" & CStr(tEndPlan * 86400)
     addrFact = "A" & CStr(tBegFact * 86400 + 1) & ":A" & CStr(tEndFact * 86400)
     '86400 = 24*60*60 - секунд в сутках

     Set rngPlan = Range(addrPlan)
     Set rngFact = Range(addrFact)

     Set rngIntesect = Application.Intersect(rngPlan, rngFact)

     If rngIntesect Is Nothing Then
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count)
     Else
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count - rngIntesect.Cells.Count)
     End If

     Debug.Print tOutPlan 'общее время вне официального интервала

End Sub

[/vba]

Чисто вне конкурса, но по теме. Нетрадиционное использование свойств объектов Excel для нужд - в данном случае - подсчета времени. Недавно я подобным образом выступал на другом форуме по вопросу, связанному с датами: http://www.sapboard.ru/forum....p456070

Там мой юмор не очень оценили, а мне понравилось. Надеюсь на бОльшее понимание в эксельном сообществе wink

wink

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

[vba]

Code

Sub nonTraditionalTimeCalculate()

     Dim tBegPlan    As Date
     Dim tEndPlan    As Date
     Dim tBegFact    As Date
     Dim tEndFact    As Date

     Dim tOutPlan    As Date

     Dim addrPlan    As String
     Dim addrFact    As String

     Dim rngPlan     As Range
     Dim rngFact     As Range
     Dim rngIntesect As Range


     tBegPlan = TimeSerial(8, 0, 0)  'официальное начало работы
     tEndPlan = TimeSerial(17, 0, 0) 'официальное окончание работы

     tBegFact = TimeSerial(6, 0, 0)  'фактическое начало работы
     tEndFact = TimeSerial(19, 0, 0) 'фактическое окончание работы

     addrPlan = "A" & CStr(tBegPlan * 86400 + 1) & ":A" & CStr(tEndPlan * 86400)
     addrFact = "A" & CStr(tBegFact * 86400 + 1) & ":A" & CStr(tEndFact * 86400)
     '86400 = 24*60*60 - секунд в сутках

     Set rngPlan = Range(addrPlan)
     Set rngFact = Range(addrFact)

     Set rngIntesect = Application.Intersect(rngPlan, rngFact)

     If rngIntesect Is Nothing Then
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count)
     Else
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count - rngIntesect.Cells.Count)
     End If

     Debug.Print tOutPlan 'общее время вне официального интервала

End Sub

[/vba]Gustav

МОИ: Ник, Tip box: 41001663842605

Ответить

СообщениеЧисто вне конкурса, но по теме. Нетрадиционное использование свойств объектов Excel для нужд - в данном случае - подсчета времени. Недавно я подобным образом выступал на другом форуме по вопросу, связанному с датами: http://www.sapboard.ru/forum....p456070

Там мой юмор не очень оценили, а мне понравилось. Надеюсь на бОльшее понимание в эксельном сообществе wink

wink

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

[vba]

Code

Sub nonTraditionalTimeCalculate()

     Dim tBegPlan    As Date
     Dim tEndPlan    As Date
     Dim tBegFact    As Date
     Dim tEndFact    As Date

     Dim tOutPlan    As Date

     Dim addrPlan    As String
     Dim addrFact    As String

     Dim rngPlan     As Range
     Dim rngFact     As Range
     Dim rngIntesect As Range


     tBegPlan = TimeSerial(8, 0, 0)  'официальное начало работы
     tEndPlan = TimeSerial(17, 0, 0) 'официальное окончание работы

     tBegFact = TimeSerial(6, 0, 0)  'фактическое начало работы
     tEndFact = TimeSerial(19, 0, 0) 'фактическое окончание работы

     addrPlan = "A" & CStr(tBegPlan * 86400 + 1) & ":A" & CStr(tEndPlan * 86400)
     addrFact = "A" & CStr(tBegFact * 86400 + 1) & ":A" & CStr(tEndFact * 86400)
     '86400 = 24*60*60 - секунд в сутках

     Set rngPlan = Range(addrPlan)
     Set rngFact = Range(addrFact)

     Set rngIntesect = Application.Intersect(rngPlan, rngFact)

     If rngIntesect Is Nothing Then
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count)
     Else
         tOutPlan = TimeSerial(0, 0, rngFact.Cells.Count - rngIntesect.Cells.Count)
     End If

     Debug.Print tOutPlan 'общее время вне официального интервала

End Sub

[/vba]

Автор - Gustav
Дата добавления - 03.08.2012 в 14:52

Дата: Пятница, 03.08.2012, 15:44 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (Gustav)

Там мой юмор не очень оценили, а мне понравилось. Надеюсь на бОльшее понимание в эксельном сообществе

Супер! Я оценил

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Пятница, 03.08.2012, 16:15 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Вот так изображу задачу online2long

online2long

Хотелось бы без макроса - ну если уж без него никак, то... wacko

wacko

К сообщению приложен файл: 5514341.jpg (70.6 Kb)

Сообщение отредактировал ALARMus - Пятница, 03.08.2012, 16:20

Ответить

Дата: Пятница, 03.08.2012, 17:00 | Сообщение № 13

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2748

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Quote (Serge_007)

Супер! Я оценил

Спасибо, я знал, что тебе понравится smile

smile

Давно вынашиваю идейку создать отдельную тему, наверное, в "Мозговом штурме"? Хочется полета коллективной фантазии на тему где подобный нетрадиционный подход может быть эффективен для каких-нибудь практических задач. Список этих задач и хочется нафантазировать. Предметные области могут быть самые разнообразные.

До двух задач с периодами времени - этой и на САПфоруме - мой мозг воображал только какой-нибудь план рассадки людей в кинотеатре или в самолете: судите сами, ячейки - прямо готовые "кресла" типа A10 (у окна), B10, C10 (у прохода smile

smile

). В зависимости от занятых мест можно сформировать при помощи Union сложный многообластный Range и далее что-нибудь с ним сделать в плане объединения (Union) или пересечения (Intersect) с другим Range. Например, выяснить совпадающие (конфликтующие) места двух планов рассадки от разных компаний по продаже билетов...

ALARMus, я дико извиняюсь, что немного "засорил" Вашу тему. Умолкаю с офф-топом. Рисунок, кстати, замечательный! Наглядный.

МОИ: Ник, Tip box: 41001663842605

Ответить

Дата: Пятница, 03.08.2012, 18:10 | Сообщение № 14

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Quote (Gustav)

я дико извиняюсь, что немного "засорил" Вашу тему. Умолкаю с офф-топом. Рисунок, кстати, замечательный! Наглядный.

Нет уж все biggrin

biggrin

засорили, тогда надо принять участие cool

cool

не все же о великом думать holiday

holiday

Сообщение отредактировал ALARMus - Пятница, 03.08.2012, 18:38

Ответить

Дата: Пятница, 03.08.2012, 18:37 | Сообщение № 15

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2748

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Quote (ALARMus)

надо принять участие

да я, собственно, и не отказываюсь smile

smile

додумаю до чего-нибудь конструктивного - непременно поделюсь

по ходу вопрос: а точность (грубость) по времени какая предполагается? до минуты, т.е. без секунд? или даже, наверное, до 5-10 минут?

МОИ: Ник, Tip box: 41001663842605

Ответить

Дата: Пятница, 03.08.2012, 18:41 | Сообщение № 16

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

http://www.excelworld.ru/forum/2-2110-23113-16-1343985013 - ПОСТ №7
содержит вложение, в котором конкретные исходные данные.
Точность до минут.

Ответить

Дата: Пятница, 03.08.2012, 18:43 | Сообщение № 17

Группа: Админы

Ранг: Местный житель

Сообщений: 19188

Репутация: 4421 ±

Замечаний: ±

Excel 365 & Mac Excel

ALARMus, почему рабочий день 8 часов, если начало в 8:00, а окончание в 17:00?

Что должно получиться при дате начала 16.07.2012 18:44:09 и окончания 17.07.2012 9:16:32?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Сообщение отредактировал Pelena - Пятница, 03.08.2012, 19:07

Ответить

Дата: Пятница, 03.08.2012, 19:40 | Сообщение № 18

Группа: Друзья

Ранг: Обитатель

Сообщений: 382

Репутация: 148 ±

Замечаний: 0% ±

Excel2010

ALARMus,
Получилась огромная формула.
Считал 9 часов в рабочем дне. 17-8=9

К сообщению приложен файл: _9.xlsx (16.8 Kb)

Ответить

Дата: Пятница, 03.08.2012, 20:45 | Сообщение № 19

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Вариант, на примере файла ZORRO2005:

Code

=(ЧИСТРАБДНИ(A3;B3)-1)*($B$1-$A$1)+МИН(МАКС(ОСТАТ(B3;1);$A$1);$B$1)-МИН(МАКС(ОСТАТ(A3;1);$A$1);$B$1)

К сообщению приложен файл: ALARMus.xlsx (13.5 Kb)

Ответить

Дата: Пятница, 03.08.2012, 21:51 | Сообщение № 20

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Лучше даже так:

Code

=(ЧИСТРАБДНИ(A3;B3)-1)*($B$1-$A$1)+МИН(МАКС(ОСТАТ(B3;1);(ДЕНЬНЕД(B3;2)>5)*$B$1;$A$1);$B$1)-МИН(МАКС(ОСТАТ(A3;1)*(ДЕНЬНЕД(A3;2)<6);$A$1);$B$1)

К сообщению приложен файл: ALARMus2.xlsx (14.3 Kb)

Сообщение отредактировал MCH - Пятница, 03.08.2012, 21:51

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет даты исключая периоды

Страница 1 из 4
1
2
3
4
»

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!