Excel - Tag und Datum des Monats

Angenommen, Sie geben für Monat und Jahr in Zelle A1 einen numerischen Eintrag ein (z. B. 01/18, 1/18, 01/2018 oder 1/2018). Sie können die folgenden Formeln verwenden, um Ihre Liste zu entwickeln.

Beginnen Sie mit der folgenden Formel in 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) 

Es sieht etwas kompliziert aus, aber das liegt daran, dass versucht wird, mit den 4 möglichen unterschiedlichen Datumseinträgen in A1 umzugehen. Wenn Sie ein anderes Datumseingabeformat haben, ist eine andere Formel erforderlich, um das Datum für den ersten Monat zu ermitteln.

In der obigen Formel findet er im Wesentlichen das "/" und verwendet seine Position, um zu bestimmen, wie viel der Zeichenfolge auseinandergezogen wird, um die Ziffern für den Monat bzw. das Jahr zu erfassen. Nachdem Sie die Ziffern für Monat und Jahr abgerufen haben, werden die Informationen in der DATE-Formel abgelegt, die Informationen im folgenden Format sucht:

=DATE(year, month, day) 

Da bekannt ist, dass das Datum der Beginn des untersuchten Monats ist, stellen Sie day = 1 ein. Der nächste Teil der Gleichung WEEKDAY bestimmt den Wochentag. WENN der Wochentag SONNTAG ist, muss der erste Tag des Monats um 1 erhöht werden. Da ein boolesches Ergebnis von TRUE in Excel-Operationen das Äquivalent von 1 ist und FALSE das GLEICHWERT von 0 ist, ist dies ein einfacher Zusatz des WEEKDAY () = 1 wird auf die Formel zur Bestimmung des ersten Monats gesetzt.

Sobald Sie den Startzeitpunkt für Ihre Liste haben, müssen Sie dem Datum für die nächste Zeile eine 1 hinzufügen und eine zusätzliche 1 zu dem Datum hinzufügen, an dem die Zeile oben ein Samstag ist. Außerdem möchten Sie sicherstellen, dass Ihr neuer Datumswert das Monatsende nicht überschreitet, und für meine Prozedur möchte ich auch sicherstellen, dass die Zeile darüber nicht leer ist. Verwenden Sie die folgende Formel in B4 und notieren Sie sich so weit, dass Sie die maximale Anzahl möglicher Daten abdecken.

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

Dadurch wird die Liste der Datumsangaben generiert, die sonntags übersprungen werden.

Sie haben mehrere Möglichkeiten, den Wochentag anzuzeigen. Option 1 besteht darin, es mit einer Formel zu tun. Grundsätzlich übernimmt die folgende Formel das Datum aus Spalte B und formatiert den Wert so, dass der Wochentag nur als Zeichenfolge angezeigt wird. Verwenden Sie in A3 die folgende Formel und kopieren Sie sie nach unten:

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

Die zweiten Gleichungen werden leer angezeigt, wenn Sie das Formular vor den Formeln in B nach unten kopieren und auf eine leere Zelle verweisen, statt auf eine Zelle, die "" enthält.

Geben Sie einen Wert wie "3 2020" in Zelle A1 ein und führen Sie Folgendes aus:

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 

Wenn ich _^(vage) ähnliche Dinge mache, ziehe ich es normalerweise vor, die Eingaben für Monat und Jahr getrennt zu halten, um es für andere später sehr einfach zu halten. _{^{(Ich habe eine überraschend große Anzahl von Leuten gesehen, die Schwierigkeiten haben, Datum zuverlässig in Excel einzugeben.)}}

Ich empfehle folgendes Setup: (Entschuldigen Sie die Zeilenumbrüche, einige dieser Formeln sind zu lang für eine Zeile in dieser Tabelle.)

╔════╦═════════════════════════════════════════════════╦════════════════════════════════╗ ║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 ║ ╚════╩═════════════════════════════════════════════════╩════════════════════════════════╝ 

Und hier ist ein Screenshot davon in Aktion:

Nicht die glatteste Lösung, aber schnell und einfach. Wenn Sie in Zelle A1 den ersten Tag des Monats eingeben, fügen Sie in A2 diese Formel hinzu ....

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

Sie können die Formel dann von dort aus kopieren. Es lässt Sonntage aus Ihrer Liste. Der einzige Sonntag, der angezeigt wird, ist der, den Sie möglicherweise in Zelle A1 eingeben. Ich habe dies für September 2019 getestet, da der erste Sonntag ist. Es kehrt zurück

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

Hoffentlich hilft das.

Brad