надеюсь на помощь знатоков Excel:

имеется три графы в таблице учета рабочего времени: дата, дневное сальдо и сумма (в часах и минутах).

вторая графа имеет положительные числа (часы переработки), отрицательные (недоработанные часы), а так же пустые ячейки (например, в дни отпуска)

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

т..е. например, сейчас имеются 100 часов переработки.

после подсчета, описанного выше, будет видно, в какой день достигнется 100 часов в прошлом, т.к. отсчет от "сегодня" до какой-то даты в прошлом.

Приложите эксель-файл с примером.

пока что есть только пдф-файл

после конвертации в экселе останутся видимыми графы: дата, дневное сальдо, сумма

справа добавится графа сумма переработанных часов (бирюзовая маркировка) без учета недоработанных часов (желтая маркировка)

суммирование должно идти от последней даты в прошлое...число (красная маркировка) когда-то покажется, например, в 2022ом году и будет расти, конечно, дальше

Anhang

Вот так?

Перечитал ещё раз задачу.

спасибо Вам большое! вторая картинка соответствует поставленной задаче.

я после работы еще раз посмотрю, кажется, у меня будет вопрос по формуле на третьей картинкe.

Пожалуйста. Я думаю, когда введёте её в ячейку, там станет всё понятно.

да, формула понятна и работает с обычными числами.

в эксель-файле после конвертации на данный момент в ячейках таблицы числа, а если нажать на ячейку, то там "ссылки" на те таблицы, из которых была сдела сводная, они в таком формате =Sheet107!I29

формула работает и там, однако только частично: она хоть и распознает негативные числа, но почему-то все равно отнимает их при суммировании.

видимо, нужно сначала ячейки привести в формат чисел, а не "ссылок", чтобы формула заработала правильно

и еще один вопрос: формула будет работать в формате обычных чисел до 100.

однако ее нужно как-то изменить, чтобы она работала в формате часов и минут, т.е. после сложения чисел после запятой, достигнув 60, она переходила в следующий час и соответственно, чтобы после запятой не было чисел выше 59?

если изменить нельзя, тo понадобится еще одна графа, где сначала прийдется работать с долями часа, где 15 минут считаются как 0,25 часа и в следующей графе переводить назад в часы и минуты?

дневное сальдо (как и графа сумма) уже в часах и минутах, но таблица этого не знает, поэтому формула начнёт суммировать их как обычные числа до 100, например

2,35 + 4,45

будут посчитаны как 6,80 (обычное число)

а должны быть кak 7,20 (в часах и минутах)

т.е. или в формуле где-то должно быть заложено про: сумма 60 после запятой = 1 до запятой или про формат hh:mm

или позже понадобится дополнительный специальный формат ячеек в графе "сумма переработанных без учета недоработанных часов" - но будет ли он работать по всей длине таблицы, если там уже заложена формула суммирования с условием (Ваша третья картинка в сообщении нр. 4)?

про работы с долями часа это было скорее рассуждение вслух на тот случай, если нет технической возможности получить опсанный выше результат суммирования (7,20 вместо 6,80)

формула С6/24 не подходит, т.к. время тогда искажается: вместо 3 ч 49 мин получается 3 ч 29 мин и 24 сек

потому что таблица думает 3,49 это 3 и 49 часа, а не 3 часа и 49 мин

3 целых 49 сотых часа или 3 часа и 29 минут 24 секунды. Время отображается в Экселе в виде ЧЧ:ММ:СС и никак иначе. Если есть запятая, то это десятичное число. Вы либо приводите данные к стандартным значениям, либо колдуйте над преобразованиями.

а есть ли такая формула, которая переводит 0,44 в 0:44?

другими словами, которая меняет вид записи, а не формаt (запятая заменяется на двоеточие)

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

Вот я для Вас спросил.

а есть ли такая формула, которая переводит 0,44 в 0:44? другими словами, которая меняет вид записи, а не формаt (запятая заменяется на двоеточие)

ответ

Да, можно использовать формулу для замены запятой на двоеточие и преобразования чисел в формат времени. Однако Excel по умолчанию воспринимает числа, представленные в десятичной форме, как время в днях, и требуется преобразование для того, чтобы отобразить это именно в виде "часы:минуты".

Для этого можно воспользоваться следующей формулой:

=TEXT(INT(A1)*60 + (A1-INT(A1))*100, "00:00")

Вот что делает эта формула:

• INT(A1) — извлекает целую часть числа (часы).
• (A1-INT(A1))*100 — извлекает дробную часть и преобразует её в минуты (умножает на 100, чтобы получить 44 из 0,44).
• TEXT(..., "00:00") — форматирует результат в виде "часы:минуты".

Пример:

• Если в ячейке A1 стоит 0,44, результат будет 0:44.

Таким образом, эта формула позволяет изменить формат записи числа на вид "часы:минуты", где целая часть числа — это часы, а дробная — минуты.

а есть ли такая формула, которая переводит 0,44 в 0:44?

0,44 ≠ 0:44

Можете назвать цель именно такого преобразования (замена запятой на двоеточие)?

Спасибо! у чата ГПТ ещё ни разу не спрашивала, совсем про него забыла

Надо же, так просто выглядит, спасибо!Интересно, как с ней дальнейшие вычисления будут работать - обязательно попробую на выходных

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

=WECHSELN(A1;",";":")

Эта формула не поможет.

Это аналогично, если все запятые на двоеточие вручную перебить,

тем не менее складывать время там будет нельзя.

Чтобы считать после этого, Вам нужно будет проделать еще одну операцию.

Нужно поменять формат на Uhrzeit и избавиться от формулы в этих ячейках. Чтобы избавиться от формул нужно просто выделить ячейки с формулами, затем правой кнопкой мышки Kopieren и потом в том же месте правой кнокой мышки Inhalte Einfügen -> nur Werte.