Как сделать автоматическое изменение ячейки каждый день в google sheets?

Question

[Teslaboy1]

Задача:
В компании 25 рабочих дней (Ячейка B1). План продаж, прогнозы... высчитываются в зависимости от отработанных дней ячейка (B2), но они не равны календарным, чтобы сделать формулу отталкиваясь от дат.
Каждый день в ячейку (B2) вставляется значение отработанных дней: 1, 2, 3... (максимум цифра 25) в ручную.

Вопрос: Можно ли автоматизировать процесс заполнения ячейки (B2), чтобы формула каждый день сама меняла значение на +1, но ограничивалась значением 25?

Пытался привязать значения СЕГОДНЯ, но дней бывает 31, воскр в компании считаются выходными. В-общем, не могу решить задачу.

Answer 1

[Евгений]

Количество рабочих дней в текущем месяце можно рассчитать формулой:

=NETWORKDAYS.INTL(EOMONTH(TODAY();-1)+1;EOMONTH(TODAY();0);"0000001";F1:F)

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

=NETWORKDAYS.INTL(EOMONTH(TODAY();-1)+1;TODAY();"0000001";F1:F)

Comments

[Евгений]

Teslaboy1, обсуждения ответов надо делать через кнопку "Комментировать", это Вам не форум.

Если добавить крайние дни, это уже будет не месяц. К тому же 25 отработанных дней за такой период не может быть. Если к календарному месяцу добавлять крайние дни, то это значение будет больше ~ до 30 дней.

Для реализации вашего графика работы нужно будет вручную указывать даты начала и конца периода, например в ячейки С1 и D1 соответственно, тогда формулой вычисляем количество рабочих дней за указаный период:

=NETWORKDAYS.INTL($C$1;$D$1;"0000001";F1:F)

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

=NETWORKDAYS.INTL($C$1;TODAY();"0000001";F1:F)

не забудьте только в столбце F перечислить все праздничные дни.

Answer 2

[Григорий Боев]

=ЕСЛИ((СЕГОДНЯ()-$A$2)>$A$1;$A$1;(СЕГОДНЯ()-$A$2))
,где $A$1 - ссылка на количество дней
$A$2 - условный "0", от которого считаем дни

Comments

[Teslaboy1]

Григорий, супер!!!

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

Например неделя с 30.11 по 06.12, но формула не плюсовала суб и вс (05.12 и 06.12) и неделя завершалась 5-ю днями, а когда начиналась новая неделя - формула снова плюсовала +1?



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

[Григорий Боев]

Teslaboy1, проще тогда будет сделать сопоставление день-число в отдельном месте, и просто подставлять
=еслиошибка(filter(B:B;A:A=CЕГОДНЯ()))
, где столбец A содержит все даты, а столбец B содержит соответствующие данные (числа, текст и т.п.)

Answer 3

[Михаил Васильев]

=ЕСЛИ(ДЕНЬ(СЕГОДНЯ())>25;25;ДЕНЬ(СЕГОДНЯ()))

Comments

[Teslaboy1]

Спасибо, но у меня ячейка тупо стала 25

Answer 4

[Sergey В.]

Можно создать ежедневный триггер, который будет запускать функцию, прибавляющую ячейке +1

Comments

[Teslaboy1]

Отлично! Как это сделать))

Answer 5

[Teslaboy1]

Евгений, классное решение!

Единственное, можно ли добавить к календарному месяцу крайние дни, чтобы недели были полными?
Например, в Декабре, в моей компании, рабочие недели считаются с понедельника по субботу и в Вашем решении у меня выпадают 30.11 и 1.12-2.12

.

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

Comments

[Евгений]

Teslaboy1, обсуждения ответов надо делать через кнопку "Комментировать", это Вам не форум.

Если добавить крайние дни, это уже будет не месяц. К тому же 25 отработанных дней за такой период не может быть. Если к календарному месяцу добавлять крайние дни, то это значение будет больше ~ до 30 дней.

Для реализации вашего графика работы нужно будет вручную указывать даты начала и конца периода, например в ячейки С1 и D1 соответственно, тогда формулой вычисляем количество рабочих дней за указаный период:

=NETWORKDAYS.INTL($С$1;$D$1;"0000001";F1:F)

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

=NETWORKDAYS.INTL($С$1;TODAY();"0000001";F1:F)

не забудьте только в столбце F перечислить все праздничные дни.

[Teslaboy1]

Евгений, супер гуд!

Если для компании праздничных дней нет я могу оставить F1:F - пустыми. Верно?

[Евгений]

Да, все верно. А можно вообще убрать из формулы последний параметр:
=NETWORKDAYS.INTL($С$1;TODAY();"0000001"), если не собираетесь праздновать вообще.

[Teslaboy1]

Евгений, Что сделано не так?

[Евгений]

Teslaboy1, скорее всего формула скопирована отсюда, и С1 написано кириллицей :)

=NETWORKDAYS.INTL(C1;TODAY();"0000001";F1:F)
=NETWORKDAYS.INTL(C1;D1;"0000001";F1:F)

копипастьте отсюда)

п.с. Если Вам подходит мое решение, отметьте мой ответ как "Лучший"

[Teslaboy1]

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

я смоделировал 4 декабря (сменил дату на компе) и почему-то раб дни (B1) не отработали(

[Евгений]

1. Поменяйте формулы из В1 и В2 местами.
2. Смена даты на компе не заставит сервера NTP гугла перевести всемирное время.
Не путайте ексель на компе с гугл-таблицами в облаке

[Teslaboy1]

Евгений, все заработало!
Спасибо Вам огромное!

PS
Видимо сервера Гугла проходят через мой комп)

[Евгений]

Иными словами "моделировать" дату вы не можете.
Можно отдельной формулой проверить каким рабочим днем по счету будет указанная дата, например в ячейке E1:
=NETWORKDAYS.INTL(C1;E1;"0000001";F1:F)

[Евгений]

Teslaboy1, не за что..
И всё же этот тред надо было вести в коментариях под моим ответом.
А мой ответ отметить кнопкой как "Лучший ответ"

[Teslaboy1]

Евгений, напишите итоговый ответ и я отмечу его решением.

[Teslaboy1]

Евгений, подскажите, почему формула считает не верно? Я ее установил, но менеджеры сломали на след день. Попытался повторить, но уже не работает?

[Евгений]

В качестве 1 аргумента Вы указываете начальный день, прописанный в ячейке C2, где у Вас число 30, а не дата начала.
Имхо вместо банального копипаста формул может лучше пару минут попробовать подумать и разобраться самостоятельно, хотя бы какие аргументы с каких ячеек скармливать функции?

[Teslaboy1]

Евгений, спасибо