Что-то типа таймера для ячеек таблицы

[st.Archer]

Приветствую всех!
Суть вопроса:
Имеется несколько ячеек таблицы. В первую вносится произвольная дата в формате [день:месяц:год(не обязательно);часы:минуты] (порядок ДД\ММ\ГГ не принципиален). В соседнюю ячейку должна автоматом записаться некая дата (в том же формате), которая получается путем прибавления конкретного количества часов к первой дате. В третью ячейку необходимо вывести разницу между полученной датой из второй ячейки и текущей датой в формате [сутки:часы:минуты], либо, если сутки невозможно использовать, то просто [часы:минуты]. Также интересует такой функционал: возможно ли окрашивание последней ячейки в зависимости от оставшегося времени: более суток - красный, менее суток - желтый, время вышло - зеленый?
Подскажите, пожалуйста, как такое осуществить в таблицах OpenOffice'a? Заранее спасибо.

[JohnSUN]

Здравствуй, st.Archer, и добро пожаловать на форум!
Это всё возможно...
1. Время в ячейках таблиц обозначается дробной частью числа, обозначающего дату-время. Дата - это целая часть числа, количество дней прошедшее с "базовой даты". В Cаlc'е можно установить три варианта "базовых дат" - Сервис-Параметры-Calc-Вычисления, группа переключателей Дата.
Дробная часть обозначает долю суток. Таким образом 1/24=0,0416666667=01:00:00. То есть, чтобы добавить к значению в ячейке A2, например, 5 часов, нужно в B2 написать формулу =A2+5/24
2. Текущую дату дает функция TODAY(), текущие дату-время функция NOW(). Обычным вычитанием можно получить разницу дат =NOW()-B2.
3. Отобразить этот результат можно в любом виде. Например, среди стандартных форматов времени есть специальный формат [HH]:MM:SS,00, который отобразит часы:минуты:секунды,сотые
4. Сочетание цветов, которое ты предложил, меня немного удивило. Я бы раскрашивал наоборот: время еще есть - зеленый, приближаемся к финишу - желтый, ждать больше нельзя - красный. Впрочем, это уже детали. Такая раскраска делается двумя способами - или условным форматированием, или добавлением к результату вычислений функции STYLE(). Как-то так:
=NOW()-B2+STYLE(CHOOSE(SIGN(INT(CURRENT()))+2;"Red";"Yellow";"Green"))
Разумеется, стили с именами "Red", "Yellow" и "Green" должны быть созданы заранее

[st.Archer]

Как говорит одна моя знакомая: "Ох ты ж ёёёёжик! О_о"
Спасибо за ответ. Я понимаю, что сейчас последуют нубские вопросы, поэтому заранее прошу прощения:

1. Время в ячейках таблиц обозначается дробной частью числа, обозначающего дату-время. Дата - это целая часть числа, количество дней прошедшее с "базовой даты". В Cаlc'е можно установить три варианта "базовых дат" - Сервис-Параметры-Calc-Вычисления, группа переключателей Дата.

Если честно, то не уловил, в чем разница этих вариантов?

2. Текущую дату дает функция TODAY(), текущие дату-время функция NOW(). Обычным вычитанием можно получить разницу дат =NOW()-B2.

Вроде понятно, но: ввел я в одну ячейку (А1) значение: 25.04.2012 01:00:00. Во второй (В1): =А1+24/24 - в обоих случаях в ячейках отображаются только число\месяц\год, а времени нет. Как можно изменить? В третью ячейку (С1) ввожу: =В1-NOW() - нужно именно от полученной даты отнимать нынешнюю, но отображается почему-то 01.01.00. Что не так?

3. Отобразить этот результат можно в любом виде. Например, среди стандартных форматов времени есть специальный формат [HH]:MM:SS,00, который отобразит часы:минуты:секунды,сотые

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

4. Сочетание цветов, которое ты предложил, меня немного удивило. Я бы раскрашивал наоборот: время еще есть - зеленый, приближаемся к финишу - желтый, ждать больше нельзя - красный. Впрочем, это уже детали. Такая раскраска делается двумя способами - или условным форматированием, или добавлением к результату вычислений функции STYLE(). Как-то так:
=NOW()-B2+STYLE(CHOOSE(SIGN(INT(CURRENT()))+2;"Red";"Yellow";"Green"))
Разумеется, стили с именами "Red", "Yellow" и "Green" должны быть созданы заранее

Если не сильно вдаваться в подробности, то нужно отслеживать временную невозможность некоторого события, с последующей сигнализацией (цветом), что событие возможно в ближайшее время и возможно уже в принципе. Просто ячеек планируется очень много, для облегчения решил, что с цветом будет удобно. Со стилями ничего конечно же не понял, но то потом, а сейчас бы с самим таймером разобраться....

[JohnSUN]

Я понимаю, что сейчас последуют нубские вопросы, поэтому заранее прошу прощения:

Ой, да ладно! За что извиняться? "Мы все учились понемногу..." Еще совсем недавно я тоже обо всей этой кухне имел смутное представление

1. Время в ячейках таблиц обозначается дробной частью числа, обозначающего дату-время. Дата - это целая часть числа, количество дней прошедшее с "базовой даты". В Cаlc'е можно установить три варианта "базовых дат" - Сервис-Параметры-Calc-Вычисления, группа переключателей Дата.

Если честно, то не уловил, в чем разница этих вариантов?

Э? Каких вариантов? В смысле почему базовых дат целых три? Это исторически сложилось. Этой ошибке уже около 20 лет. В первых Экселях была выставлена базовая дата 1 января 1900 года. А чего? Нормальная точка отсчета, "круглая" такая. То есть, если в Экселе ввести в ячейку 1 (единичку) и отформатировать как дату, то увидим 01.01.1900. Но фокус в том, что 1900-ый год не был високосным. А Эксель считает, что 29 февраля 1900 существовал, этакий "день без рассвета". Потом Майкрософт пытался оправдываться, что это было сделано для совместимости с Lotus 1-2-3, а авторы Lotus утверждали, что у них такой ошибки не было... Во всяком случае, номера всех дней после 29/02/1900 на самом деле на единицу больше, чем должны были бы быть. В Calc'е для совместимости с Экселем есть базовая дата 01.01.1900, но лучше использовать основной вариант, который учитывает поправку на этот лишний день - 30.12.1899. В общем, история довольно забавная, в интернете много материала по ней...
Нас во всей этой истории интересует только один нюанс: дата в понимании электронных таблиц - это просто целое число дней, прошедшее с какой-то начальной, базовой даты. То есть, если из одной даты вычесть другую дату, то сразу получим количество дней между ними. Мы-то так далеко в прошлое обычно не заглядываем, а для текущих дат ошибка не проявляется.

2. Текущую дату дает функция TODAY(), текущие дату-время функция NOW(). Обычным вычитанием можно получить разницу дат =NOW()-B2.

Вроде понятно, но: ввел я в одну ячейку (А1) значение: 25.04.2012 01:00:00. Во второй (В1): =А1+24/24 - в обоих случаях в ячейках отображаются только число\месяц\год, а времени нет. Как можно изменить? В третью ячейку (С1) ввожу: =В1-NOW() - нужно именно от полученной даты отнимать нынешнюю, но отображается почему-то 01.01.00. Что не так?

Всё так. Calc посчитал всё правильно, но отобразил только дату. Попробуй выделить все ячейки, где должны быть видны и дата и время, нажми Ctrl+1 или выбери Формат-Ячейки (см. картинку). На вкладке Числа нужно выбрать одну из двух категорий Дата или Время, а в соседнем списке Формат выбрать подходящий образец. (Кстати, тебе не кажется, что 24/24 это просто 1? В смысле - "одни сутки")

3. Отобразить этот результат можно в любом виде. Например, среди стандартных форматов времени есть специальный формат [HH]:MM:SS,00, который отобразит часы:минуты:секунды,сотые

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

Можно, конечно, исхитриться и затолкать количество суток, часов и минут в одну ячейку. Но, поверь мне на слово, это довольно сложный трюк. Да и овчинка выделки не стоит. Если действительно нужно видеть одновременно и количество дней между датами и время, то лучше использовать две соседних ячейки - дни в одной, время в другой. Или плюнуть на условности и показывать только часы. Чем плохо 579:49:08? Трудно сосчитать сколько точно дней осталось? А по большому счету - кого это интересует? Больше 48 часов - значит времени еще навалом.

Со стилями ничего конечно же не понял, но то потом, а сейчас бы с самим таймером разобраться....

Без проблем! Доберешься до цветного раскрашивания - переспросишь.
Да! Для дальнейшей беседы было бы неплохо опубликовать какой-нибудь тестовый файлик, чтобы во время обсуждения можно было ссылаться на конкретные ячейки, формулы, стили... Сделаешь? Или мне сделать?

[вложение удалено Администратором]

[Hasim]

Могу предложить такой вариант.

[вложение удалено Администратором]

[JohnSUN]

Да, Hasim, что-то в этом роде я и имел в виду под "сложным трюком". Только там еще немного вычурнее получается: значение вычисляется в отдельной ячейке, а формула, которая представит полученное число в виде форматированной строки, пишется в следующей колонке. Если потребуются дополнительные вычисления, развернуть текстовую запись обратно в число будет затруднительно.
Кстати, наверное, первое двоеточие в твоей формуле есть смысл заменить на " дн. ", " суток " или просто пробел - непривычный формат получился.
Если точное количество часов-минут не имеет значения, то можно отформатировать строку с кодом формата "# ?/? суток" и получить "24 3/8 суток", "2/5 суток", "-1 5/8 суток". Код формата "# ??/24" тоже выглядит логичным

[st.Archer]

В смысле почему базовых дат целых три? Это исторически сложилось. Этой ошибке уже около 20 лет. В первых Экселях была выставлена базовая дата 1 января 1900 года. А чего? Нормальная точка отсчета, "круглая" такая. То есть, если в Экселе ввести в ячейку 1 (единичку) и отформатировать как дату, то увидим 01.01.1900. Но фокус в том, что 1900-ый год не был високосным. А Эксель считает, что 29 февраля 1900 существовал, этакий "день без рассвета". Потом Майкрософт пытался оправдываться, что это было сделано для совместимости с Lotus 1-2-3, а авторы Lotus утверждали, что у них такой ошибки не было... Во всяком случае, номера всех дней после 29/02/1900 на самом деле на единицу больше, чем должны были бы быть. В Calc'е для совместимости с Экселем есть базовая дата 01.01.1900, но лучше использовать основной вариант, который учитывает поправку на этот лишний день - 30.12.1899. В общем, история довольно забавная, в интернете много материала по ней...
Нас во всей этой истории интересует только один нюанс: дата в понимании электронных таблиц - это просто целое число дней, прошедшее с какой-то начальной, базовой даты. То есть, если из одной даты вычесть другую дату, то сразу получим количество дней между ними. Мы-то так далеко в прошлое обычно не заглядываем, а для текущих дат ошибка не проявляется.

Спасибо за ликбез, весьма познавательно.

Кстати, тебе не кажется, что 24/24 это просто 1? В смысле - "одни сутки"

Я подумал, что можно вводить только в часах в таком виде, а сутки привел просто для упрощения

Да! Для дальнейшей беседы было бы неплохо опубликовать какой-нибудь тестовый файлик, чтобы во время обсуждения можно было ссылаться на конкретные ячейки, формулы, стили... Сделаешь? Или мне сделать?

Файл прикрепил.

Могу предложить такой вариант.

В принципе, это именно то, что мне нужно. Подправил немного под себя - и все отлично работает. Спасибо огромное за данную формулу!
Теперь нужно разобраться со стилями и цветным выделением ячеек в зависимости от количества суток.
Попытался попробовать через условное форматирование, создал три стиля - "Красный", "Желтый", "Зеленый" - в которых менял только заливку фона (может там еще где нужно было поковыряться в настройках стиля?). В условиях пытался задать промежутки: больше чем, между и меньше чем - и соответственно подставить нужное количество суток (сначала просто цифрами 1,2,3, потом дробью 24/24), но ничего не вышло Подскажите, в чем ошибки?

[вложение удалено Администратором]

[JohnSUN]

В принципе, это именно то, что мне нужно. Подправил немного под себя - и все отлично работает.

К сожалению, как оказалось - работает не всё. Дело в том, что в результате выполнения "трюкаческой" формулы, мы (чаще всего! не всегда!) будем получать текстовую строку. Чтобы применить к ней условное форматирование, не достаточно сравнить значение этой ячейки на больше-меньше-между. Строки сравниваются между собой посимвольно, слева направо, так как обычно читаются. И поэтому строка "21сут. 12:36" как ни странно, но будет меньше, чем строка "9сут. 08:12". Потому что начинается с двойки, а вторая строка - с девятки.

Теперь нужно разобраться со стилями и цветным выделением ячеек в зависимости от количества суток.
Попытался попробовать через условное форматирование, создал три стиля - "Красный", "Желтый", "Зеленый" - в которых менял только заливку фона (может там еще где нужно было поковыряться в настройках стиля?). В условиях пытался задать промежутки: больше чем, между и меньше чем - и соответственно подставить нужное количество суток (сначала просто цифрами 1,2,3, потом дробью 24/24), но ничего не вышло Подскажите, в чем ошибки?

Есть способы раскрасить и такие ячейки, но условные форматы в таких случаях - при работе с текстовыми строками - обычно основываются не на значении ячейки, а на формулах (выбирается в выпадающем списке для каждого условия в окне условного форматирования). В приложенном файле так раскрашена последняя колонка.
Но что получилось? Сначала с помощью трюка мы преобразовали число, которое обозначало дни и часы с минутами в читабельную строку. А затем пришлось изобрести еще два трюка, чтобы раскрасить ячейки с этими строками. Не слишком ли сложно получается?
В первой цветной колонке, которая оформлена с помощью функции STYLE(), сутки явно не видны, но легко угадываются по количеству отображаемых часов. Ведь это не очень сложно - если часов больше 24, значит есть еще больше суток... А насколько больше - это обычно не важно, разве не так?
И если мы примем за основу не точность показаний, а удобство человека при восприятии информации, то можем выйти на компромиссный вариант (колонка F): в часах и минутах отображаются только строки которые "вот-вот наступят", а для значений "еще долго" и "уже пора" - единое представление.
Я в начале сообщения оговорился, что бывают трюки, которые не всегда преобразуют число в строку. Я имел в виду фокус, когда дата-время преобразуется в длинное целое число. Например так:

Код Выделить Развернуть

=INT(C2)*10000+HOUR(C2)*100+MINUTE(C2)
С помощью такой формулы интервал вида "5 сут. 17:36" превращается в целое число 51736. Остается преобразовать результат к нужному виду при помощи "хитрой" форматной строки

Код Выделить Развернуть

0" сут. "00:00
В чем преимущество такого трюка? Для ячеек с такими значениями условные форматы можно строить с помощью простых сравнений. И этот фокус можно рекомендовать для случаев, когда время всегда положительное. Однако в твоем случае у нас будут попадаться и отрицательные значения, а для них приведенная формула врёт. Чтобы она давала правильный результат, для отрицательных значений формула должна была бы быть такой:

Код Выделить Развернуть

=ROUND(C2;0)*10000+(HOUR(C2)*100+MINUTE(C2))*SIGN(C2)
Соответственно, чтобы она работала и для положительных и для отрицательных значений, формула станет еще более сложной.
Поэтому предлагаю остановиться на компромиссном варианте...

[вложение удалено Администратором]