вставка формулы в графике работы сотрудников
setania
Дата: Четверг, 14.05.2015, 12:13 |
Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
Добрый день. Нужна формула! Задача: график работы сотрудников с 1 по 30 число, в ячейке пишем часовой диапазон, например с 10.00 до 23.30 и нужно в конце получить три колонки. Первая кол-во отработанных дней, вторая кол-во отработанный часов и третья колонка в том числе ночных часов после 22.00 Буду очень признательна!!!
Добрый день. Нужна формула! Задача: график работы сотрудников с 1 по 30 число, в ячейке пишем часовой диапазон, например с 10.00 до 23.30 и нужно в конце получить три колонки. Первая кол-во отработанных дней, вторая кол-во отработанный часов и третья колонка в том числе ночных часов после 22.00 Буду очень признательна!!! setania
Ответить
Сообщение Добрый день. Нужна формула! Задача: график работы сотрудников с 1 по 30 число, в ячейке пишем часовой диапазон, например с 10.00 до 23.30 и нужно в конце получить три колонки. Первая кол-во отработанных дней, вторая кол-во отработанный часов и третья колонка в том числе ночных часов после 22.00 Буду очень признательна!!! Автор - setania Дата добавления - 14.05.2015 в 12:13
МВТ
Дата: Четверг, 14.05.2015, 13:04 |
Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация:
137
±
Замечаний:
0% ±
Excel 2007
Для начала надо заменить "." на ":" в качестве разделителя часов и минут, иначе возни будет намного больше Количество отработанных дней Количество отработанных часов (формула массивная, вводится Ctrl-Shift-Enter) Код
=СУММ(ЕСЛИОШИБКА(ПРАВСИМВ(C6:AF6;5)-(ЛЕВСИМВ(C6:AF6;5));0))
Количество ночных часов (формула тоже массивная) Код
=СУММ(ЕСЛИ(ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)>22/24;ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)-22/24;0))
Для начала надо заменить "." на ":" в качестве разделителя часов и минут, иначе возни будет намного больше Количество отработанных дней Количество отработанных часов (формула массивная, вводится Ctrl-Shift-Enter) Код
=СУММ(ЕСЛИОШИБКА(ПРАВСИМВ(C6:AF6;5)-(ЛЕВСИМВ(C6:AF6;5));0))
Количество ночных часов (формула тоже массивная) Код
=СУММ(ЕСЛИ(ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)>22/24;ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)-22/24;0))
МВТ
Ответить
Сообщение Для начала надо заменить "." на ":" в качестве разделителя часов и минут, иначе возни будет намного больше Количество отработанных дней Количество отработанных часов (формула массивная, вводится Ctrl-Shift-Enter) Код
=СУММ(ЕСЛИОШИБКА(ПРАВСИМВ(C6:AF6;5)-(ЛЕВСИМВ(C6:AF6;5));0))
Количество ночных часов (формула тоже массивная) Код
=СУММ(ЕСЛИ(ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)>22/24;ЕСЛИОШИБКА(--ПРАВСИМВ(C6:AF6;5);0)-22/24;0))
Автор - МВТ Дата добавления - 14.05.2015 в 13:04
Nic70y
Дата: Четверг, 14.05.2015, 13:16 |
Сообщение № 3
Группа: Друзья
Ранг: Экселист
Сообщений: 8972
Репутация:
2356
±
Замечаний:
0% ±
Excel 2010
Количество отработанных часов
Код
=СУММ(ЕСЛИ(--ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":")<--ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":");1+ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":");ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":"))-ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":"))*24
Количество отработанных часов
Код
=СУММ(ЕСЛИ(--ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":")<--ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":");1+ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":");ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":"))-ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":"))*24
Nic70y
ЮMoney 41001841029809
Ответить
Сообщение Количество отработанных часов
Код
=СУММ(ЕСЛИ(--ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":")<--ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":");1+ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":");ПОДСТАВИТЬ(ПРАВБ(0&C6:AF6;5);".";":"))-ПОДСТАВИТЬ(ЛЕВБ(C6:AF6&0;5);".";":"))*24
Автор - Nic70y Дата добавления - 14.05.2015 в 13:16
setania
Дата: Четверг, 14.05.2015, 13:40 |
Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
в графе кол-во отработанных дней у меня получился результат. А вот в ячейке кол-во часов и в том числе ночных не получилось. Выдает ноль. Может я не правильно вношу цифры? 10:00-22:00 Может вместо тире нужно вставить какой то другой знак?
в графе кол-во отработанных дней у меня получился результат. А вот в ячейке кол-во часов и в том числе ночных не получилось. Выдает ноль. Может я не правильно вношу цифры? 10:00-22:00 Может вместо тире нужно вставить какой то другой знак? setania
Ответить
Сообщение в графе кол-во отработанных дней у меня получился результат. А вот в ячейке кол-во часов и в том числе ночных не получилось. Выдает ноль. Может я не правильно вношу цифры? 10:00-22:00 Может вместо тире нужно вставить какой то другой знак? Автор - setania Дата добавления - 14.05.2015 в 13:40
_Boroda_
Дата: Четверг, 14.05.2015, 14:00 |
Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16676
Репутация:
6481
±
Замечаний:
±
2003; 2007; 2010; 2013 RUS
Предлагаю так (2 последних - формулы массива, вводятся одновременным нажатием Контрл Шифт Ентер): Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+(ЛЕВБ(C6:AF6;2)-ПСТР(C6:AF6;7;2)>0)))
Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ABS(ТЕКСТ(ОСТАТ(ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;\0")-ТЕКСТ(ОСТАТ(ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;8:")+"8:")))
вторую формулу можно так (у Николая подглядел) Код
=СУММ(ПРАВБ(ПОДСТАВИТЬ(0&C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6&0;".";":");5)+(ЛЕВБ(C6:AF6&0;2)-ПСТР(C6:AF6&10^6;7;2)>0))
МВТ , если работа с 23 до 09, то сколько у Вас посчитает ночных? А нужно 1+6=7
Предлагаю так (2 последних - формулы массива, вводятся одновременным нажатием Контрл Шифт Ентер): Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+(ЛЕВБ(C6:AF6;2)-ПСТР(C6:AF6;7;2)>0)))
Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ABS(ТЕКСТ(ОСТАТ(ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;\0")-ТЕКСТ(ОСТАТ(ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;8:")+"8:")))
вторую формулу можно так (у Николая подглядел) Код
=СУММ(ПРАВБ(ПОДСТАВИТЬ(0&C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6&0;".";":");5)+(ЛЕВБ(C6:AF6&0;2)-ПСТР(C6:AF6&10^6;7;2)>0))
МВТ , если работа с 23 до 09, то сколько у Вас посчитает ночных? А нужно 1+6=7 _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Предлагаю так (2 последних - формулы массива, вводятся одновременным нажатием Контрл Шифт Ентер): Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+(ЛЕВБ(C6:AF6;2)-ПСТР(C6:AF6;7;2)>0)))
Код
=СУММ(ЕСЛИ(ЕПУСТО(C6:AF6);;ABS(ТЕКСТ(ОСТАТ(ПРАВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;\0")-ТЕКСТ(ОСТАТ(ЛЕВБ(ПОДСТАВИТЬ(C6:AF6;".";":");5)+"2:";1);"[<0,333]ч:мм;8:")+"8:")))
вторую формулу можно так (у Николая подглядел) Код
=СУММ(ПРАВБ(ПОДСТАВИТЬ(0&C6:AF6;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(C6:AF6&0;".";":");5)+(ЛЕВБ(C6:AF6&0;2)-ПСТР(C6:AF6&10^6;7;2)>0))
МВТ , если работа с 23 до 09, то сколько у Вас посчитает ночных? А нужно 1+6=7 Автор - _Boroda_ Дата добавления - 14.05.2015 в 14:00
МВТ
Дата: Четверг, 14.05.2015, 15:21 |
Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация:
137
±
Замечаний:
0% ±
Excel 2007
если работа с 23 до 09, то сколько у Вас посчитает ночных
Согласен, но тогда и общая сумма часов будет неправильно считаться. Хотя, формально, период с 0:00 до 9:00 это уже следующая дата :). UPD чтобы не мучиться, можно вот так [vba]Код
Function TimeInterval(S As String) As Date Dim arr Dim STime As Date, ETime As Date arr = Split(Application.WorksheetFunction.Trim(Replace(S, ".", ":")), "-") If UBound(arr) <> 1 Then Exit Function STime = CDate(arr(0)): ETime = CDate(arr(1)) If ETime > STime Then TimeInterval = ETime - STime Else TimeInterval = 1 - STime + ETime End Function
[/vba]
если работа с 23 до 09, то сколько у Вас посчитает ночных
Согласен, но тогда и общая сумма часов будет неправильно считаться. Хотя, формально, период с 0:00 до 9:00 это уже следующая дата :). UPD чтобы не мучиться, можно вот так [vba]Код
Function TimeInterval(S As String) As Date Dim arr Dim STime As Date, ETime As Date arr = Split(Application.WorksheetFunction.Trim(Replace(S, ".", ":")), "-") If UBound(arr) <> 1 Then Exit Function STime = CDate(arr(0)): ETime = CDate(arr(1)) If ETime > STime Then TimeInterval = ETime - STime Else TimeInterval = 1 - STime + ETime End Function
[/vba]МВТ
Сообщение отредактировал МВТ - Четверг, 14.05.2015, 15:43
Ответить
Сообщение если работа с 23 до 09, то сколько у Вас посчитает ночных
Согласен, но тогда и общая сумма часов будет неправильно считаться. Хотя, формально, период с 0:00 до 9:00 это уже следующая дата :). UPD чтобы не мучиться, можно вот так [vba]Код
Function TimeInterval(S As String) As Date Dim arr Dim STime As Date, ETime As Date arr = Split(Application.WorksheetFunction.Trim(Replace(S, ".", ":")), "-") If UBound(arr) <> 1 Then Exit Function STime = CDate(arr(0)): ETime = CDate(arr(1)) If ETime > STime Then TimeInterval = ETime - STime Else TimeInterval = 1 - STime + ETime End Function
[/vba]Автор - МВТ Дата добавления - 14.05.2015 в 15:21
setania
Дата: Четверг, 14.05.2015, 16:47 |
Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
Посчитала в ручную цифры. В колонке где общее кол-во отработанных часов неправильно считается общая сумма
Посчитала в ручную цифры. В колонке где общее кол-во отработанных часов неправильно считается общая сумма setania
Ответить
Сообщение Посчитала в ручную цифры. В колонке где общее кол-во отработанных часов неправильно считается общая сумма Автор - setania Дата добавления - 14.05.2015 в 16:47
setania
Дата: Четверг, 14.05.2015, 16:48 |
Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
ночные часы считаются с 22.00 до 06.00. Но в данном случае в том числе ночные часы считаются правильно!
ночные часы считаются с 22.00 до 06.00. Но в данном случае в том числе ночные часы считаются правильно! setania
Ответить
Сообщение ночные часы считаются с 22.00 до 06.00. Но в данном случае в том числе ночные часы считаются правильно! Автор - setania Дата добавления - 14.05.2015 в 16:48
setania
Дата: Четверг, 14.05.2015, 17:14 |
Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
я посчитала одну строчку, выделила красным
я посчитала одну строчку, выделила красным setania
Ответить
Сообщение я посчитала одну строчку, выделила красным Автор - setania Дата добавления - 14.05.2015 в 17:14
setania
Дата: Четверг, 14.05.2015, 17:15 |
Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
простите не присоединила файл
простите не присоединила файл setania
Ответить
Сообщение простите не присоединила файл Автор - setania Дата добавления - 14.05.2015 в 17:15
_Boroda_
Дата: Четверг, 14.05.2015, 17:24 |
Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16676
Репутация:
6481
±
Замечаний:
±
2003; 2007; 2010; 2013 RUS
Покусаю! Плохо посчитали. 10:00-16:00 как получилось 8? Аналогично 16:00-22:00 - не 8, а 6.
Покусаю! Плохо посчитали. 10:00-16:00 как получилось 8? Аналогично 16:00-22:00 - не 8, а 6. _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Покусаю! Плохо посчитали. 10:00-16:00 как получилось 8? Аналогично 16:00-22:00 - не 8, а 6. Автор - _Boroda_ Дата добавления - 14.05.2015 в 17:24
setania
Дата: Пятница, 15.05.2015, 09:40 |
Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация:
0
±
Замечаний:
20% ±
Excel 2010
Ответить
Сообщение Простите!!!! Автор - setania Дата добавления - 15.05.2015 в 09:40 Ответить
Сообщение _Boroda_, Помогите пожалуйста с этим же графиком только с суточными дежурствами! ПОЖАЛУЙСТА!!! Автор - NataljaHarlova Дата добавления - 04.04.2019 в 19:34
jakim
Дата: Четверг, 04.04.2019, 20:33 |
Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1210
Репутация:
314
±
Замечаний:
0% ±
Excel 2010
Умножьте на 24 и установите числовой формат
Код
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)))*24
Умножьте на 24 и установите числовой формат
Код
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)))*24
jakim
Ответить
Сообщение Умножьте на 24 и установите числовой формат
Код
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)))*24
Автор - jakim Дата добавления - 04.04.2019 в 20:33
Pelena
Дата: Четверг, 04.04.2019, 20:39 |
Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 19373
Репутация:
4531
±
Замечаний:
±
Excel 365 & Mac Excel
Или такКод
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)+(ЛЕВБ(G10:AK10;5)=ПРАВБ(G10:AK10;5))))
Или такКод
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)+(ЛЕВБ(G10:AK10;5)=ПРАВБ(G10:AK10;5))))
Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение Или такКод
=СУММ(ЕСЛИ(ЕПУСТО(G10:AK10);;ПРАВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)-ЛЕВБ(ПОДСТАВИТЬ(G10:AK10;".";":");5)+(ЛЕВБ(G10:AK10;2)-ПСТР(G10:AK10;7;2)>0)+(ЛЕВБ(G10:AK10;5)=ПРАВБ(G10:AK10;5))))
Автор - Pelena Дата добавления - 04.04.2019 в 20:39
NataljaHarlova
Дата: Четверг, 04.04.2019, 21:09 |
Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Спасибо ОГРОМНОЕ!!!!!!!!! Дай Бог ВАМ здоровья!
Наталья
Ответить
Сообщение Спасибо ОГРОМНОЕ!!!!!!!!! Дай Бог ВАМ здоровья! Автор - NataljaHarlova Дата добавления - 04.04.2019 в 21:09
NataljaHarlova
Дата: Четверг, 04.04.2019, 21:21 |
Сообщение № 17
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
простите за назойливость но можно сделать так чтобы он с 08:00 до 08:00 считал как 24 часа?
простите за назойливость но можно сделать так чтобы он с 08:00 до 08:00 считал как 24 часа? NataljaHarlova
Наталья
Ответить
Сообщение простите за назойливость но можно сделать так чтобы он с 08:00 до 08:00 считал как 24 часа? Автор - NataljaHarlova Дата добавления - 04.04.2019 в 21:21
Pelena
Дата: Четверг, 04.04.2019, 21:29 |
Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 19373
Репутация:
4531
±
Замечаний:
±
Excel 365 & Mac Excel
А сейчас он как считает?
А сейчас он как считает? Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение А сейчас он как считает? Автор - Pelena Дата добавления - 04.04.2019 в 21:29
_Boroda_
Дата: Четверг, 04.04.2019, 21:32 |
Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16676
Репутация:
6481
±
Замечаний:
±
2003; 2007; 2010; 2013 RUS
Просто в той моей формуле, что у Вас в AN, замените знак > на >=
Просто в той моей формуле, что у Вас в AN, замените знак > на >= _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Просто в той моей формуле, что у Вас в AN, замените знак > на >= Автор - _Boroda_ Дата добавления - 04.04.2019 в 21:32
NataljaHarlova
Дата: Четверг, 04.04.2019, 21:35 |
Сообщение № 20
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Pelena, никак не считает 0.0 я строчку выделила серым цветом :help:
Pelena, никак не считает 0.0 я строчку выделила серым цветом :help: NataljaHarlova
Наталья
Ответить
Сообщение Pelena, никак не считает 0.0 я строчку выделила серым цветом :help: Автор - NataljaHarlova Дата добавления - 04.04.2019 в 21:35