Excel - день и дата месяца

Question

Я попробовал несколько формул "Дата", но я не могу заставить это работать.

Вот моя проблема:

Я хочу настроить электронную таблицу, которая позволит пользователю вводить A1 - месяц и год.

Я хотел бы, чтобы моя таблица автоматически заполняла столбец А "Днями" месяца, например, если месяц начинается в среду, то среда будет первым днем. Затем я хотел бы, чтобы столбец B заполнял даты, например, 01.12.2017 (формат Великобритании).

Кроме того, я хотел бы, чтобы воскресенье было опущено. У меня есть ручная версия этого, где я должен вводить значения каждый месяц, но я хотел бы автоматизировать это, поскольку это необходимо сделать 80 раз, и я не хочу сначала заполнять вручную, затем копировать и вставлять 79 раз.

Какие-либо предложения?

Answer 1

Предполагая, что вы вводите в ячейку A1 для месяца и года числовую запись, такую как 01/18, 1/18, 01/2018 или 1/2018, вы можете использовать следующие формулы для составления списка.

Начните с размещения следующей формулы в B3.

=DATE(RIGHT(A1,LEN(A1)-FIND("/",A1)),LEFT(A1,FIND("/",A1)-1),1)+(WEEKDAY(DATE(RIGHT(A1,LEN(A1)-FIND("/",A1)),LEFT(A1,FIND("/",A1)-1),1))=1)

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

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

=DATE(year, month, day)

Поскольку известно, что дата является началом исследуемого месяца, установите день = 1. Следующая часть уравнения WEEKDAY определяет день недели. Если день недели воскресенье, то первый день месяца должен быть увеличен на 1. Поскольку логический результат TRUE является эквивалентом 1 в математических операциях Excel, а FALSE - ЭКВИВАЛЕНТОМ 0, выполняется простое добавление WEEKDAY()= 1 в формулу для определения первого месяца.

Если у вас есть начальное число для начала списка, вам нужно добавить 1 к дате следующей строки и добавить еще 1 к дате, если строка выше - суббота. Кроме того, вы хотите убедиться, что ваше новое значение даты не превышает конец месяца, и для моей процедуры я также хочу убедиться, что строка выше не пуста. Используйте следующую формулу в B4 и скопируйте достаточно далеко, чтобы охватить максимальное количество возможных дат.

=IF(B3<>"",IF(B3+1+(WEEKDAY(B3)=7)>EOMONTH($B$3,0),"",B3+1+(WEEKDAY(B3)=7)),"")

Это создаст список дат, пропускающих воскресенья.

У вас есть несколько вариантов для отображения дня недели. Вариант 1 - сделать это по формуле. В основном следующая формула возьмет дату из столбца B и отформатирует значение так, чтобы день недели отображался только в виде строки. В A3 используйте следующую формулу и скопируйте:

=TEXT(B3,"DDDD")

OR

=IF(B3<>"",TEXT(B3,"DDDD"),"")

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

Answer 2

Введите значение типа "3 2020" в ячейку A1 и выполните команду:

Sub INeedDates()
    Dim A1 As Range: Set A1 = Range("A1")
    Dim d As Date, i As Long

    i = 2
    arr = Split(A1, " ")
    d = DateSerial(arr(1), arr(0), 1)

    While CInt(Month(d)) = CInt(arr(0))
        If Format(d, "dddd") <> "Sunday" Then
            Cells(i, "A").Value = Format(d, "dddd")
            Cells(i, "B").Value = d
            Cells(i, "B").NumberFormat = "d/m/yyyy"
            i = i + 1
        End If
        d = d + 1
    Wend
End Sub

Answer 3

Когда я делаю _{^{(смутно)}} похожие вещи, я обычно предпочитаю разделять входные данные месяца и года, чтобы потом их было проще использовать другим. _{^{(Я видел удивительно большое количество людей, которые испытывают трудности с надежным вводом дат в Excel.)}}

Я рекомендую следующую настройку:(Извините за разрывы строк, некоторые из этих формул слишком длинны для одной строки в этой таблице.)

╔════╦═════════════════════════════════════════════════╦════════════════════════════════╗
║CELL║                   DATA/FORMULA                  ║               COMMENTS         ║
╠════╬═════════════════════════════════════════════════╬════════════════════════════════╣
║    ║                                                 ║                                ║
║ A1 ║  "Month"                                        ║ Locked cell with bold, centered║
║    ║                                                 ║ text, yellow fill, and red bor-║
║    ║                                                 ║ der on left, right & top sides ║
║    ║                                                 ║                                ║
║ B1 ║  "Year"                                         ║ Locked cell with bold, centered║
║    ║                                                 ║ text, yellow fill, and red bor-║
║    ║                                                 ║ der on left, right & top sides ║
║    ║                                                 ║                                ║
║ A2 ║  11                                             ║ Unlocked cell with Data Valid- ║
║    ║                                                 ║ ation requiring a whole number ║
║    ║                                                 ║ from 1 to 12 (inclusive), right║
║    ║                                                 ║ justified with red border on   ║
║    ║                                                 ║ left, right & bottom sides     ║
║    ║                                                 ║                                ║
║ B2 ║  2018                                           ║ Unlocked cell with Data Valid- ║
║    ║                                                 ║ ation requiring a whole number ║
║    ║                                                 ║ from 1 to 12 (inclusive), left ║
║    ║                                                 ║ justified with red border on   ║
║    ║                                                 ║ left, right & bottom sides     ║
║    ║                                                 ║                                ║
║ A3 ║  "Day"                                          ║ Locked cell, formatted as      ║
║    ║                                                 ║ heading to below table         ║
║    ║                                                 ║                                ║
║ B3 ║  "Date"                                         ║ Locked cell, formatted as      ║
║    ║                                                 ║ heading to below table         ║
║    ║                                                 ║                                ║
║ A4 ║  =IF(B4="","",TEXT(B4, "DDDD"))                 ║ This shows the name of the day ║
║    ║                                                 ║ of the date found in cell B4   ║
║    ║                                                 ║                                ║
║ B4 ║ =IF(WEEKDAY(DATE(B2, A2, 1))-1, DATE(B2, A2, 1),║ This picks the date of the 1st ║
║    ║  DATE(B2, A2, 2))                               ║ day of the month chosen above, ║
║    ║                                                 ║ unless it's a Sunday, then it  ║
║    ║                                                 ║ is the following day (Monday)  ║
║    ║                                                 ║                                ║
║ A5 ║  Copy A4 to these cells                         ║ As you copy A4 to these cells  ║
║ to ║                                                 ║ Excel will automatically alter ║
║ A29║                                                 ║ each to reference the cell in  ║
║    ║                                                 ║ column B of this row           ║
║    ║                                                 ║                                ║
║ B5 ║ =IF(B4="","",IF(IF(WEEKDAY(B4+1)-1,B4+1,B4+2)>= ║ This picks the date after B4   ║
║    ║ EOMONTH(B4,0),"",IF(WEEKDAY(B4+1)-1,B4+1,B4+2)))║ unless it's a Saturday, then it║
║    ║                                                 ║ picks the next Monday, unless  ║
║    ║                                                 ║ it would go into the next month║
║    ║                                                 ║                                ║
║ B6 ║  Copy B5 to these cells                         ║ As you copy B5 to these cells, ║
║ to ║                                                 ║ Excel will automatically alter ║
║ B29║                                                 ║ each one to reference the cell ║
║    ║                                                 ║ above it instead of B4         ║
╚════╩═════════════════════════════════════════════════╩════════════════════════════════╝

И вот скриншот этого в действии:

Answer 4

Не самое лучшее решение, но быстрое и простое. Если вы введете первый день месяца в ячейку A1, то в A2 добавьте эту формулу ....

=IF(TEXT(A1,"DDDD")="Sunday",A1+1,A1)
then A3
=IF(TEXT(A2+1,"DDDD")="Sunday",A2+2,A2+1)

Затем вы можете скопировать формулу оттуда. Это оставляет воскресенья из вашего списка. Появится единственное воскресенье, которое вы можете ввести в ячейку A1. Я проверял это на сентябрь 2019 года, так как первое - воскресенье. Возвращается

02/09/2019
03/09/2019
04/09/2019
05/09/2019
06/09/2019
07/09/2019
09/09/2019

Надеюсь, это поможет.

штифтик