надеюсь на помощь знатоков 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

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