Родились сегодня:
wadim21


Лидеры рейтинга

ID: 401284

Михаил Александров

Советник

378

Россия, Санкт-Петербург


ID: 259041

Алексеев Владимир Николаевич

Мастер-Эксперт

339

Россия, пос. Теплоозёрск, ЕАО


ID: 401888

puporev

Профессор

215

Россия, Пермский край


ID: 405338

vovaromanov.jr

1-й класс

117


ID: 400669

epimkin

Профессионал

111


ID: 242862

Hunter7007

Мастер-Эксперт

29

Россия, Омск


ID: 137394

Megaloman

Мастер-Эксперт

25

Беларусь, Гомель


8.10.2

13.10.2021

JS: 2.10.2
CSS: 4.6.0
jQuery: 3.6.0
DataForLocalStorage: 2021-10-23 05:16:01-standard


Установка, настройка и работа в пакете Microsoft Office.

Администратор раздела: Megaloman (Мастер-Эксперт)

Консультация онлайн # 200406

Раздел: Пакет MSOffice
Автор вопроса: youtuxmail (Посетитель)
Дата: 11.03.2021, 11:31 Консультация закрыта
Поступило ответов: 1

Здравствуйте, уважаемые эксперты! Прошу вас ответить на следующий вопрос по Условному форматированию (УФ) в Excel.
Какие формулы в УФ применить к ячейкам с датами, чтобы даты, относящиеся, например, к текущей неделе, все имели зелёную заливку; к прошлой - красную, следующая неделя - жёлтая и т.п...
Но когда наступит следующая неделя (переход с воскресенья на понедельник), заливка ячеек должна измениться (т.к., к примеру зелёные даты "текущей" недели уже перейдут в "прошлую" неделю и должны стать красными).
Интересуют недели:
- позапрошлая и далее
- прошлая
- текущая
- следующая
- через неделю.
(xlsx приложил)

Спасибо!

-----
Прикрепленные файлы:


7f1b0b6bb2ba245b73105a4d814796c7ae5f7ff4.xlsx
скачать (11.3 кб)
Ответ # 280814 от Megaloman
Здравствуйте, youtuxmail!
Внимание! Ответ изменён из-за обнаруженной ошибки!
Допустим, для ячейки B2 формула для условного форматирования
Код
позапрошлая и далее
=И(ЕЧИСЛО(B2);B2<СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3))

прошлая
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()-1-ДЕНЬНЕД(СЕГОДНЯ()-7;3))

текущая
=И(СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)<=B2;B2<=СЕГОДНЯ()+6-ДЕНЬНЕД(СЕГОДНЯ();3))

следующая
=И(СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3)<=B2;B2<=СЕГОДНЯ()+13-ДЕНЬНЕД(СЕГОДНЯ()+7;3))

через неделю
=И(СЕГОДНЯ()+14-ДЕНЬНЕД(СЕГОДНЯ()+14;3)<=B2;B2<=СЕГОДНЯ()+20-ДЕНЬНЕД(СЕГОДНЯ()+14;3))

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

Для Вашего примера


Для более легкого контроля работы форматирования даты подряд:


Пример применения форматирования для каждого условия по отдельности:


Прикрепил Ваш файл с моим форматированием. ya210312.xlsx (16.1 кб)

И еще совет: при экспериментах с форматированием в сторонке от массовых данных сделайте ячейку с тестовыми данными и её форматируйте, а затем её формат специальной вставкой распространяйте на боевой диапазон, например:

В чём радость? Вы меняете форматирование только одной ячейки, а не диапазона.

Megaloman

Мастер-Эксперт
11.03.2021, 18:53
5


Большой мастер и энтузиаст!

Мини-форум консультации # 200406

q_id

Megaloman

Мастер-Эксперт

ID: 137394

1

= общий =    11.03.2021, 14:34
youtuxmail:

Даты рассматриваются в текущем году, или они могут быть произвольными? Какая неделя первая в году, с какого дня она начинается?

Последнее редактирование 11.03.2021, 15:09 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

2

= общий =    11.03.2021, 18:47

Привязки к году/месяцу нет. Важно, чтобы после смены недели менялся цвет заливки. Т.е. пока даты находятся в "рамках" текущей недели - заливка зелёная. И так все остальные заливки - при смене недели меняется заливка.
Т.е. "рамка" - 5 недель (позапрошлая, прошлая, текущая, следующая, через неделю).

Спасибо за быстрый ответ.

ЗЫ: и почему-то УФ помечает конец недели в субботу! А начало в воскресение. smile

q_id

Megaloman

Мастер-Эксперт

ID: 137394

3

= общий =    11.03.2021, 18:56

ЗЫ: и почему-то УФ помечает конец недели в субботу! А начало в воскресение.

В какой функции? При использовании функции =ДЕНЬНЕД подсветилась подсказка:

Последнее редактирование 11.03.2021, 19:32 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

Megaloman

Мастер-Эксперт

ID: 137394

4

= общий =    11.03.2021, 19:15
youtuxmail:

Я Вам отослал ответ, не хочу его менять. Я подразумевал в нём, что Вы форматируете ячейки или с датами, или пустые.
Если там возможны и текстовки, то лучше написать:

Код
через неделю
=И(ЕЧИСЛО(J2);J2>СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3))

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

5

= общий =    11.03.2021, 21:20

(К сожалению вставить скрины в тело сообщения не получилось через "Вставить тег изображения" smile Приложил скрины).....

По отдельности формулы отрабатывают так:
1. "ПОЗАПРОШЛАЯ И ДАЛЕЕ"
=И(ЕЧИСЛО(B2);B2<СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3))
- формула работает отлично! (рисунок 1). smile
https://ibb.co/1skQV8k
--------------

2. "ПРОШЛАЯ"
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3))
- формула залила правильно с 01.03 по 07.03. НО захватила с 08.03 по 14.03 (т.е. "текущую") и ещё 15.03 (Пн "следующей") (рисунок 2). smile
https://ibb.co/4Ps72fk
--------------

3. "ТЕКУЩАЯ"
=И(СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)<=B2;B2<=СЕГОДНЯ()+ДЕНЬНЕД(СЕГОДНЯ();3))
- формула залила правильно с 08.03 по 14.03. НО захватила ещё 15.03 и 16.03 (Пн и Вт "следующей") (рисунок 3). smile
https://ibb.co/P9kWPXk
--------------

4. "СЛЕДУЮЩАЯ"
=И(СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3)<=B2;B2<=СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3))
- формула залила правильно с 15.03 по 21.03. НО захватила ещё 22.03 и 23.03 (Пн и Вт "через неделю") (рисунок 4). smile
https://ibb.co/3mqrNs0
--------------

5. "ЧЕРЕЗ НЕДЕЛЮ"
=B2>СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3)
- формула пропустила 22.03 и 23.03 (Пн и Вт "через неделю") и унеслась вдаль ) (рисунок 5). smile Можно ограничить только "через неделю"?
https://ibb.co/q5TqVH8

-----
Прикрепленные файлы:

q_id

youtuxmail

Посетитель

ID: 404864

6

= общий =    11.03.2021, 21:24

И xlsx приложил.

-----
Прикрепленные файлы:


704f01418ae6e8b82ea8dfb5f8535ffe.xlsx
скачать (12.2 кб)

q_id

Megaloman

Мастер-Эксперт

ID: 137394

7

= общий =    11.03.2021, 22:44
youtuxmail:

И xlsx приложил.

И открыл, и ничего не менял и не увидел повода к рёву smile :

Всё правильно! Чтобы не напрягаться, вот скриншот с датами по порядку:

Правый столбец отформатирован в соответствии с пожеланием:

Можно ограничить только "через неделю"?

В формуле для форматирования через неделю заменить:
Код
=И(СЕГОДНЯ()+14-ДЕНЬНЕД(СЕГОДНЯ()+14;3)<=B2;B2<=СЕГОДНЯ()+14+ДЕНЬНЕД(СЕГОДНЯ()+14;3))

Ранее сделал, как понимал логику расцветки.
Форматирование работает правильно. Не знаю, как Вам удалось извратить smile результат. Подозреваю, меняли дату на компьютере, а данные в таблице не меняли. В этом случае надо пересчитать формулы принудительно

Чтобы принудительно пересчитать всю книгу или рабочий лист, используйте горячие клавиши Excel: F9 – пересчёт всех листов во всех открытых книгах Shift+F9 – пересчёт активного листа Ctrl+Alt+F9 – пересчёт всех листов во всех открытых книгах независимо от того, вносились ли в них изменения с момента последнего пересчёта

Либо сохранить книгу, закрыть, открыть заново.
ya21031122-out2.xlsx (15.9 кб)
Здесь на Вашем листе ничего не менял. На другом листе - пример с датами по порядку для лёгкого контроля работы форматирования.

Последнее редактирование 11.03.2021, 23:15 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

8

= общий =    12.03.2021, 07:00

Спасибо, что помогаете, Megaloman! Сам я никогда до всего такого не додумался бы... smile
----------------

И открыл, и ничего не менял


- вот вот smile Поэтому я и написал в самом начале:

По отдельности формулы отрабатывают так:


Если тестировать КАЖДУЮ ОТДЕЛЬНО формулу, то видно всё, что я в предыдущем посте наскриншотил.
Сделал скрины и с Вашим листом. (см. в прицепе)
(подписи над каждым рисунком).
----------------
0 - Открыл. Не менял. Все правила на месте. Визуально всё норм.

----

1 - только ПОЗАПРОШЛАЯ И ДАЛЕЕ. Всё хорошо! smile

----

2 - только ПРОШЛАЯ неделя + залила ещё и ТЕКУЩАЯ +Пн СЛЕДУЮЩАЯ smile

----

3 - только ТЕКУЩАЯ + залила Пн и Вт СЛЕДУЮЩАЯ smile

----

4 - только СЛЕДУЮЩАЯ +залила Пн и Вт ЧЕРЕЗ НЕДЕЛЮ smile

----

5 - только ЧЕРЕЗ НЕДЕЛЮ (новая). всё хорошо! smile

----------------
Задал я Вам задачку smile ...

-----
Прикрепленные файлы:

Последнее редактирование 12.03.2021, 07:20 youtuxmail (Посетитель)

q_id

Megaloman

Мастер-Эксперт

ID: 137394

9

= общий =    12.03.2021, 08:10
youtuxmail:

Давайте сначала. Вы задали вопрос, я предложил решение и предоставил его Вам. Вы:

0 - Открыл. Не менял. Все правила на месте. Визуально всё норм

То есть моё решение, моё форматирование работает. Не меняя моё форматирование, меняйте данные в ячейках - всё работает правильно?
Далее, Вы экспериментируете smile . Это здорово! smile . Но это уже не совсем моё, тут надо искать Ваши ошибки. Например, чисто визуально на Ваших скриншотах, Вы пишите форматирование для разных условий, но при этом на эти разные условия Вы прописываете одинаковые цвета: в чём тогда претензия?.
Я не очень понимаю, каким алгоритмом Вы руководствуетесь при Ваших экспериментах, давайте формулируйте словами, что-то одно не получается - давайте разберём. Не будем всё сразу валить в кучу.

Последнее редактирование 12.03.2021, 08:13 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

10

= общий =    12.03.2021, 08:21

Извиняюсь, что так непонятно объясняю.... smile

Итак... Тот файл, что Вы скинули последний, я открыл и никакие данные в ячейках не трогал. Я хотел увидеть, как работает каждая формула отдельно (эта формула для этой недели, эта - для другой и т.д.). Например, если мне в будущем понадобиться заливать только ПРОШЛУЮ неделю, я просто удалю формулы для других недель и всё. НО вот тут-то и выяснилось, что по-отдельности некоторые формулы заливают почему-то лишние дни, а не в границах свое недели. smile
К примеру, если бы понадобилась одна формула только для ПРОШЛОЙ недели, она бы отличалась от той, что сейчас?

q_id

Megaloman

Мастер-Эксперт

ID: 137394

11

= общий =    12.03.2021, 08:33

только для ПРОШЛОЙ недели, она бы отличалась от той, что сейчас?

Конечно нет. Только пусть в условном форматировании при этом будет эта одна единственная формула. Если хотите комбинировать несколько условий, прописывайте им разные форматы, например, цвета.
На Ваших скриншотах я вижу по два условия с одинаковыми цветами.
Я делаю так: беру одну ячейку, форматирую ее, убеждаюсь, что результат адекватный моим устремлениям,
ctrl/c
выделяю необходимый диапазон ячеек
Спец вставка - форматы.

Последнее редактирование 12.03.2021, 08:39 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

12

= общий =    12.03.2021, 08:48

Хорошо.) Пойдём так....
Предположим, что требуется залить синим только даты прошлой недели. Возьмём только формулу для ПРОШЛОЙ недели:
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3))

Как я понимаю, она должна залить только ячейки прошедшей недели. На данный момент "прошедшая" неделя - это даты с 01.03 по 07.03. Их и должна залить формула. Она их и заливает. smile
Но она заливает и с 08.03 по 14.03, и 15.03 (т.е. и даты текущей недели, и даже Пн следующей), что не нужно.
Вопрос: Как использовать эту формулу, чтобы залить ТОЛЬКО даты прошлой недели и не трогать никакие другие даты? smile

Вот так я и рассуждал по каждой формуле - правильно ли работает она в том диапазоне дат, для которого писалась?

Последнее редактирование 12.03.2021, 09:43 youtuxmail (Посетитель)

q_id

Megaloman

Мастер-Эксперт

ID: 137394

13

= общий =    12.03.2021, 11:12
youtuxmail:

Вот так я и рассуждал по каждой формуле - правильно ли работает она в том диапазоне дат, для которого писалась?

Виноват! Мои извинения! В формуле для определения даты конца недели вкралась ошибка, и, по трагической случайности, я её писал в четверг, когда она отрабатывала правильно. smile . Я изменил ответ, перезалил файл, посмотрите, что получилось. smile

Последнее редактирование 12.03.2021, 11:26 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

Megaloman

Мастер-Эксперт

ID: 137394

14

= общий =    12.03.2021, 12:28
youtuxmail:

Offtopic: У кого то из нас бифуркация: в почте в извещении я вижу: "youtuxmail (Посетитель) положительно оценила Ваше сообщение", а в постах: "Вот так я и рассуждал". Надо быть последовательным: или исправить регистрационные данные, либо изменить окончания глаголов smile

Последнее редактирование 12.03.2021, 12:29 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

15

= общий =    12.03.2021, 16:28

исправить регистрационные данные


- эээээ... я вообще не видел при регистрации каких-то эм/жо граф.
А что там движок сайта придумывает, мне неведомо smile ....

Приложил скрины)


И xlsx приложил.


Сам я никогда до всего такого не додумался бы

я и написал в самом начале:
я в предыдущем посте наскриншотил. ... Сделал скрины


- уж не знаю из-за чего путаница )))

UPD: щас в Личные данные заглянул: Пол: женский smile !! При регистрации про пол вопросов не помню! И почему именно женский выставился smile ....

Последнее редактирование 12.03.2021, 16:33 youtuxmail (Посетитель)

q_id

Алексеев Владимир Николаевич

Мастер-Эксперт

ID: 259041

16

= общий =    12.03.2021, 16:30
Megaloman:

Увидев Вашу сложную формулу вычисления номера недели в году, я вспомнил о наличии изящной команды в PowerShell, в которую вводишь любую дату и получаешь N недели в году и всё прочее, что нужно от операций с время/датами.
В Консоли всё легко: команда
"Для даты " + (Get-Date '1.1.2021' -UFormat "%d %b %Y %A N недели в году = %V")
возвратила: Для даты 01 янв 2021 пятница N недели в году = 1

Я обрадовался, хотел предложить Вам в помощь кмд вызова PowerShell-строки, но решил проверить в динамике, написал скрипт с циклом 40 дней. Оказалось, PowerShell инкрементит N недели не по понедельникам, а по прошествии каждых 7 суток от начала года. Я поискал в инете как исправить? Но увидел варианты не проще Вашего, тк в разных странах неделя начинается не всегда по понедельникам.

-----
Прикрепленные файлы:

q_id

Megaloman

Мастер-Эксперт

ID: 137394

17

= общий =    12.03.2021, 16:59
Алексеев Владимир Николаевич:

видев Вашу сложную формулу вычисления номера недели в году, я вспомнил о наличии изящной команды в PowerShell, в которую вводишь любую дату и получаешь N недели в году

Цитата: Библия. Притчи 16 стих 25

Есть пути, которые кажутся человеку прямыми, но конец их – путь к смерти.

В Excel есть аналогичная функция - НОМНЕДЕЛИ: берёшь дату, получаешь номер недели в году. И я сначала на неё клюнул, но построить логику работы формулы, корректно работающей на границе декабрь-январь на этой функции мне показалось сложным. Вот если бы можно было взять номер недели от сотворения мира ... Для Excel это 01.01.1900
Если бы youtuxmail позволил использовать макрос, тогда можно было бы написать свою функцию, которая до минимума сократила бы длину формулы в форматировании.
Возможно из любопытства это проделаю.
Возможно из мазохизма попытаюсь всё же применить НОМНЕДЕЛИ
И вообще, номер недели в решении я никак не вычисляю.

Последнее редактирование 12.03.2021, 18:32 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

Megaloman

Мастер-Эксперт

ID: 137394

18

= общий =    12.03.2021, 17:02
youtuxmail:

Гендер в личных данных не столь важен - мне интереснее результат моего ответа: Вы достигли поставленных целей?

=====
Нет времени на медленные танцы

q_id

youtuxmail

Посетитель

ID: 404864

19

= общий =    12.03.2021, 17:25

Да, да.. Только что протестил формулы. Всё работает как надо!
Отличная работа!!! smile
ОГРОМНОЕ спасибо за Ваш труд и знания. smile





Библия


- любимая книга.

q_id

Megaloman

Мастер-Эксперт

ID: 137394

20

= общий =    12.03.2021, 18:34
youtuxmail:

Обращайтесь! Диалог с человеком, который хочет разобраться, полезен обоим.

=====
Нет времени на медленные танцы

q_id

Megaloman

Мастер-Эксперт

ID: 137394

21

= общий =    12.03.2021, 21:06

Уважаемый Алексеев Владимир Николаевич! Позвольте в очередной раз выразить Вам моё восхищение и совершеннейшее почтение! smile Общение с Вами побуждает к плодотворным размышлениям.
Итак, как я писал,
- И вообще, номер недели в решении я никак не вычисляю.
- В Excel есть аналогичная функция - НОМНЕДЕЛИ: берёшь дату, получаешь номер недели в году. И я сначала на неё клюнул, но построить логику работы формулы, корректно работающей на границе декабрь-январь на этой функции мне показалось сложным. Вот если бы можно было взять номер недели от сотворения мира ...
«И тут Остапа понесло…»: а кто мне мешает посчитать абсолютный номер недели относительно 01.01.1900 (числовое значение = 1), ведь даты в Excel - это числа по порядку. И, оказалось, формулы существенно подсократились и капитально унифицировались: всего-то надо посчитать разницу между абсолютными номерами недель сегодня и заданной датой и сопоставить эту разницу нужному интервалу.
Работает! Картиночка с результатом и формулами ниже:

ya21031221.xlsx (16.7 кб)

=====
Нет времени на медленные танцы

q_id

Алексеев Владимир Николаевич

Мастер-Эксперт

ID: 259041

22

= общий =    13.03.2021, 16:29
Megaloman:

Вы писали : "номер недели в решении я никак не вычисляю" - а я как увидел текущую задачу (ещё без Вашего Ответа), то сразу подумал, будто НомерНедели - ключевой параметр в Алгоритме решения. Потому что именно разница номеров недели м-ду текущей неделей и исследуемой как раз и нужна Автору Вопроса для назначения цвета исследуемым ячеям.
Да Вы и сами заметили "всего-то надо посчитать разницу между абсолютными номерами недель сегодня и заданной датой и сопоставить эту разницу нужному интервалу."

"взять номер недели от сотворения мира" + "абсолютный номер недели относительно 01.01.1900" - я подозреваю, Вы немного ошиблись. За начало компьютерной эры принята дата 1янв1970 . В системном реестре Windows есть много параметров, где ДатаВремя хранится в формате числа секунд, прошедших от 1янв1970 0:00 . Например, ДатаУстановки ОперСистемы вписана в HKLM\Software\Microsoft\Windows NT\CurrentVersion\InstallDate в виде числа типа 1477716376 . Для пересчёта в осмысливаемую дату надо к этому числу секунд прибавить тчк отсчёта 1янв1970 и смещение от Гринвича (для меня это 10 час * 3600 сек/час).

Для UNIX-систем за начало отсчёта времени тоже принято 1янв1970, 0 часов 0 минут (см "Форматы времени и даты в PowerShell" Ссылка )

Однако, функция НомерНедели работает в PowerShell неправильно, и меня удивляет, что умные дяди из корпорации Microsoft сумели придумать сложнейшие ОперСистемы (популярность Windows - вне конкуренции), а не сумели или не захотели сделать простую правильную функцию счётчика недели от начала года.

"В Excel есть аналогичная функция - НОМНЕДЕЛИ" - я проверил : эта функция работает правильно, но только для Америки и англо-язычных. В ней номер недели инкрементится в Воскресенье - первый день недели в США. В ПанелиУправления \ "Язык и РегиональныеСтандарты" на вкладке Дата задано ПервыйДеньНедели = Понедельник. Я менял эту настройку на др дни; в HKCU\Control Panel\International\ сис-реестре я менял iFirstDayOfWeek = 0 ==> 1 и iFirstWeekOfYear = 0 ==> 1 … Но упрямый Excel игнорит системные настройки, а своих настроек недели у него нет либо они так глубоко, что я не докопался.

Так что Ваше Решение - гениально и макси-оптимально! С чем я Вас и поздравляю! Вы не подсчитывали, сколько трудов Вы вложили в своё Решение? Я прикидываю, не менее 20 часов?

q_id

Megaloman

Мастер-Эксперт

ID: 137394

23

= общий =    13.03.2021, 19:50

Цитата: Я

"взять номер недели от сотворения мира"

- я утрирую. Смысл в том, чтобы посчитать номер дня или недели от какой-то заведомо далёкой даты, которая гарантированно не встретится в задаче. Насчёт первого дня недели, настройка в самой функции Excel НОМНЕДЕЛИ
Цитата: Справочная система в функции НОМНЕДЕЛИ Excel

Описание
Возвращает номер недели для определенной даты. Например, неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.

Для этой функции могут использоваться две разные системы.
Система 1 Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
Система 2 Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1.

Синтаксис
НОМНЕДЕЛИ(дата_в_числовом_формате;[тип])Аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.) функции НОМНЕДЕЛИ описаны ниже.

Дата_в_числовом_формате Обязательный. Дата, по которой определяется неделя. Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.
Тип Необязательный. Число, которое определяет, с какого дня начинается неделя. Значение по умолчанию — 1. Тип Начало недели Система
1 или опущен Воскресенье 1
2 Понедельник 1
11 Понедельник 1

12 Вторник 1
13 Среда 1
14 Четверг 1
15 Пятница 1
16 Суббота 1
17 Воскресенье 1
21 Понедельник 2

Замечание
В приложении Microsoft Excel даты хранятся в виде порядковых номеров, над которыми могут выполняться вычисления. По умолчанию дата 1 января 1900 г. имеет номер 1, а 1 января 2008 г. — 39 448, поскольку это 39 448-ой день, так как интервал между этими датами составляет 39 448 дней.
Если значение "дата_в_числовом_формате" находится вне диапазона для текущего базового значения даты, возвращается ошибка #ЧИСЛО!.
Если значение "тип" не входит в диапазон, указанный в таблице выше, возвращается ошибка #ЧИСЛО!.

Я привык, что понедельник - первый день недели, но не понял разницы между 2 и 11 типом (оба в Системе 1).

Кстати, что Excel, что VBS определяет абсолютный номер дня относительно 1 января 1900 г. одинаково, например, в ячейке Excel при форматировании ячейки с датой 31.12.2020
в число получим 44196, то же число выдаст VBS-скрипт:
Код
a = DateValue("31.12.2020")
MsgBox CStr(a) + vbCrLf + CStr(CLng(a))
Offtopic:

Вы не подсчитывали, сколько трудов Вы вложили в своё Решение? Я прикидываю, не менее 20 часов?

Я не раз писал, что я лентяй и верхогляд. Если время считать как в хоккее, то 2-3 часа, не более, причем ощутимая доля - переписка и оформление ответа. Другое дело, я получаю задачу, возникает идея, как решать, не прокатывает, бросаю её и подсознание придумывает новую идею и я её щупаю уже руками.

Последнее редактирование 13.03.2021, 19:55 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

Алексеев Владимир Николаевич

Мастер-Эксперт

ID: 259041

24

= общий =    14.03.2021, 14:09
Megaloman:

Вы писали: "что Excel, что VBS определяет абсолютный номер дня относительно 1 января 1900 г. одинаково" - меня удивило Ваше упорство про 1900 г, и я решил проверить, потому что я много лет полагал и убеждался, будто отсчёт комп-эры ведётся от 1970г. Но Вы правы: это 2й стандарт комп-эры! Я написал скрипты (нужны ли они Вам?) и убедился: VBS , VBA и Excel отсчитывают дни от 29дек1899 ! И значит, VBS - это детище MsOffice-разработчиков, а не ОперСистемщиков. Буду знать! Спасибо Вам за просвещение!

А для исправления бага в PowerShell в рунете ничего найти не удалось. Пришлось запрашивать на английском по фразе:
PowerShell How to change number of week in a year on Mondays? (Как изменять нумерацию дней недели в году по понедельникам?).
Я нашёл 2 серьёзных источника : Ссылка1 и Ссылка2. Читаю…

q_id

Megaloman

Мастер-Эксперт

ID: 137394

25

= общий =    14.03.2021, 16:42
Алексеев Владимир Николаевич:

VBS , VBA и Excel отсчитывают дни от 29дек1899

Позвольте не согласиться!
Вот распечатка, как преобразуется число в даты в Excel и VBA (=vbs)

Код
S=""
S=S + CStr(1) + vbTab + DataVBA(1) + vbCrLf
S=S + CStr(2) + vbTab + DataVBA(2) + vbCrLf
S=S + CStr(3) + vbTab + DataVBA(3) + vbCrLf
S=S + CStr(4) + vbTab + DataVBA(4) + vbCrLf

S=S + CStr(58) + vbTab + DataVBA(58) + vbCrLf
S=S + CStr(59) + vbTab + DataVBA(59) + vbCrLf
S=S + CStr(60) + vbTab + DataVBA(60) + vbCrLf
S=S + CStr(61) + vbTab + DataVBA(61) + vbCrLf
S=S + CStr(62) + vbTab + DataVBA(62) + vbCrLf
S=S + CStr(63) + vbTab + DataVBA(63)
MsgBox S
' ==================================
Function DataVBA(i)
    DataVBA = CStr(CDate(i))
End Function
В Excel идёт отсчет от 01.01.1900, а в VBA/VBS от 31.12.1899
Разница в 1 день даты сохраняется до 60 дня (29.02.1900 в Excel и 28.02.1900 в VBA/VBS), затем оба способа совпадают от даты 01.03.1900 и до сегодняшних дней. Не знаю, можно ли когда-нибудь об это споткнуться smile

=====
Нет времени на медленные танцы

q_id

Алексеев Владимир Николаевич

Мастер-Эксперт

ID: 259041

26

= общий =    15.03.2021, 11:07
Megaloman:

Вы писали : "В Excel идёт отсчет от 01.01.1900, а в VBA/VBS от 31.12.1899" - про Excel спорить не буду, там свои настройки коррекции-подтасовки, отличающиеся от системных зачем-то…
А в VBS я проверил ещё раз и убедился: Нуль отсчёта - 30 дек 1899. Это для НЕдокументированной команды cLng(Дата), которой я ранее никогда не пользовался, потому что для вычисления разницы м-ду 2мя величинами в формате ДатаВремя существует официальная надёжная команда
DateDiff("ЕдИзмерения",Дата1,Дата2)

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

Я примерно пол-жизни работал в цехах КИПиАвтоматики. Мне, как самому добросовестному зануде, не верящему авторитетам, поручали поверку новых и отремонтированных и измерительных приборов (КИП - КонтрольноИзмерительные Приборы). Поверка - сличение показаний поверяемого прибора с образцовым, обычно состоит из 3х этапов : Проверка/коррекция Нуля, Коррекция Диапазона, и проверка линейности выборочно по характерным оцифрованным точкам. Нельзя отдавать НЕповеренный прибор в горячие цеха, чтоб не множить проблемы работников с вредными условиями труда.
Также нельзя использовать сомнительную команду в скриптах для вычисления даты и проч операций, пока не сделана поверка этой кмд.

Так, команда PowerShell (см ~5 постов выше) Get-Date 'дата' -UFormat %V прошла успешно мой первый этап поверки на 0-точке : для 1янв она правильно возвратила N недели = 1. Но на 2м этапе оказалось, кмд врёт, и ею нельзя пользоваться. Я заметил это на прошлой неделе, тк живу в деградирующей стране. А программисты развитых и развивающихся стран заметили этот глюк в 2010г (см "Powershell - week of the year" Ссылка)

Для поверки Вашей кмд cLng(Дата) я тоже написал поверочный скрипт, прилагаю его ниже. Я не буду засорять эту страницу кодом, кому интересно, смогут открыть скрипт в ТекстРедакторе (я пишу в простом Блокноте) и почитать мои подробные комменты.

Прикрепляю скрин. На нём Вы можете сопоставить результаты Здравому смыслу и официальной, проверенной команде DateDiff().

Как видите, нулевая точка отсчёта для поверяемого вычислителя дней cLng(Дата) - это 30 декабря 1899г. Если поддаться Вашему авторитету "отсчет … в VBA/VBS от 31.12.1899" - это всё равно, что выпустить из поверки в эксплуатацию глубиномер со смещённым нулём на 1 м. На производстве такое легкомыслие не прощают, чёрная метка разгильдяя приклеится на много лет.

"Не знаю, можно ли когда-нибудь об это споткнуться" - Вы правы, за 10 лет программирования кмд cLng(Дата) не понадобилась мне ни разу изза наличия удобной альтернативы DateDiff(). И вряд ли понадобится отсчёт от 1899г. Поэтому я тоже не вижу смысла продолжать этот спор и развивать тему. smile

-----
Прикрепленные файлы:


970d741e9b99458aee24c146772793a7.zip
скачать (927 байт)

q_id

Megaloman

Мастер-Эксперт

ID: 137394

27

= общий =    15.03.2021, 15:03
Алексеев Владимир Николаевич:

Это для НЕдокументированной команды cLng(Дата)

Есть Excel, в нём инструмент создания макросов - VBA, он там задокументирован и в справочной системе для функций преобразования типов данных видим:
Код
The function name determines the return type as shown in the following:

Function Return Type 	Range for expression argument 
CBool	 Boolean	 Any valid string or numeric expression. 
CByte	 Byte		 0 to 255. 
CCur	 Currency	 -922,337,203,685,477.5808 to 922,337,203,685,477.5807. 
CDate	 Date		 Any valid date expression. 
CDbl	 Double 	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. 
CDec	 Decimal 	+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001. 
CInt	 Integer 	-32,768 to 32,767; fractions are rounded. 
CLng	 Long 		-2,147,483,648 to 2,147,483,647; fractions are rounded. 
CLngLng	 LongLong 	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; fractions are rounded. (Valid on 64-bit platforms only.) 
CLngPtr	 LongPtr 	-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems; fractions are rounded for 32-bit and 64-bit systems.  
CSng	 Single 	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values. 
CStr	 String 	Returns for CStr depend on the expression argument. 
CVar	 Variant 	Same range as Double for numerics. Same range as String for non-numerics. 
Это не чьи-либо выдумки, это официальный источник.
У нас с Вами разговор о разном.
Я уже цитировал из документации Excel: "В приложении Microsoft Excel даты хранятся в виде порядковых номеров, над которыми могут выполняться вычисления. Я показал, что в Excel день с номером 1 это 01.01.1900, а в VBA/VBS 31.12.1899 и что начиная с 60 дня эта разница устранена, что позволяет как в Excel, так и в VBA/VBS работать одинаково с номером дня относительно начала отсчета. Всё!

И не вижу повода к раздражительности и агрессии. smile

=====
Нет времени на медленные танцы

q_id

Megaloman

Мастер-Эксперт

ID: 137394

28

= общий =    15.03.2021, 15:28
Алексеев Владимир Николаевич:

Offtopic: Мы родом из страны советов smile Позволю поэтому, совет: я использую в Excel средство создания/редактирования макросов в качестве редактора при создании vbs, это позволяет контролировать синтаксис, смотреть справочник по функциям, редактор немного улучшает форматирование кода, его читаемость, так как приводит написание функций к единому стандарту, позволяет легче отлаживать код. Редко, очень редко, приходится что-то допилить потом, в файле VBS.

Последнее редактирование 15.03.2021, 15:38 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

q_id

Алексеев Владимир Николаевич

Мастер-Эксперт

ID: 259041

29

= общий =    16.03.2021, 14:05
Megaloman:

Спасибо Вам за совет! Я тоже любил Excel в 2003г, когда корпорация Microsoft писала свои лучшие продукты WinXP и OfficeXP с макси-уважением к пользователям. Примерно 10 лет я был даже лучшим Excel-спецом в фирмах, где я работал. Но сейчас продукты Microsoft стали навязывать нам избыточные навороты и ограничивать полезные инструменты. Работать в Маткаде стало гораздо приятнее, чем в Excel , работать в html-формате - гораздо приятнее, чем в Word .

Вы - гений Excel , Ваша фраза "начиная с 60 дня эта разница устранена, что позволяет как в Excel, так и в VBA/VBS" означает, что Excel для Вас - образцовый прибор, с которым Вы сравниваете показания Ваших скриптов. Предложенный Вами РедакторКода хорош для отладки небольших программ.

Но у меня - другая ситуация: я вообще не люблю и редко загружаю тяжёлые продукты (Excel , Хром-браузер, антивирус…). Но мои скрипты очень большие и делают громадную работу в Консоли с прокруткой экрана. Например Optimizer.vbs 374 кБ содержит 5600 строк кода для очистки сохранённых html-документов от JavaScript , фреймов, РекламБаннеров, избыточных мусор-тэгов с глубокими вложениями… Объём очищеных докум-файлов уменьшается в 10…100 раз, избавляя меня от покупки дорого-стоящих жёстких дисков в Б колич-ве.

Спец-РедакторКода имеет моно-ширинный мелкий шрифт. При увеличении размера шрифта увеличивается меж-строчный итервал и уменьшается макси-ширина строки в символах. Резко увеличивется число страниц визуально-раздутого кода.
А в Блокноте я задаю самый легко-читаемый шрифт Arial с переменной и компактной шириной кегля, без дурацких засечек, но с макси-плотностью текста. Главное: не пользовать одно-буквенные имена переменных (i , n , x…). И когда var-имена уникальны, то Блокнот-поиск мигом находит заданные мною фразы и блоки горячими клав-сочетаниями, чтоб сделать прогу проще и надёжнее. smile

q_id

Megaloman

Мастер-Эксперт

ID: 137394

30

= общий =    16.03.2021, 16:24
Алексеев Владимир Николаевич:

Offtopic: Вы очень преувеличиваете мои возможности - я всего лишь немного продвинутый пользователь, способный решать задачи в Excel, в том числе с привлечением автозаписи макросов, поиска подсказок в инете. Не более того. smile

Последнее редактирование 16.03.2021, 16:27 Megaloman (Мастер-Эксперт)

=====
Нет времени на медленные танцы

Возможность оставлять сообщения в мини-форумах консультаций доступна только после входа в систему.
Воспользуйтесь кнопкой входа вверху страницы, если Вы зарегистрированы или пройдите простую процедуру регистрации на Портале.

Лучшие эксперты раздела

Зенченко Константин Николаевич

Старший модератор

Рейтинг: 165

Megaloman

Мастер-Эксперт

Рейтинг: 25

solowey

Академик

Рейтинг: 3

CradleA

Мастер-Эксперт

Рейтинг: 1

Лысков Игорь Витальевич

Мастер-Эксперт

Рейтинг: 0

SFResid

Мастер-Эксперт

Рейтинг: 0