Power Query, Datumsfunktionen

Lern Video




Definition der 1. Woche des Jahres

USA := Beginn 1. Kalenderwoche immer mit 1. Januar.
Deutschsprachigen Raum := ISO-Kalenderwoche üblich.
Woche 1 = Woche im Jahr, die den ersten Donnerstag des Jahres enthält.


Unterschiedliche Systematiken für die Zuordnung der Kalenderwochen zu einem Kalendermonat


  • 4-5-4 Methode, Gemeinjahr (Nicht-Schaltjahr) = 4 gleichlange Quartale. Jedes Quartal besteht aus drei Monaten, von denen der erste 28 Tage („4“ Wochen), der zweite 35 Tage („5“ Wochen) und der dritte wieder 28 Tage („4“ Wochen) umfasst.
  • 5-4-4 Methode, Gemeinjahr (Nicht-Schaltjahr) = 4 gleichlange Quartale. Jedes Quartal besteht aus drei Monaten, von denen der erste 35 Tage („5“ Wochen), der zweite 28 Tage („4“ Wochen) und der dritte wieder 28 Tage („4“ Wochen) umfasst.
  • 13-Monate-Kalender-Methode, Annahme = Monate beinhalten exakt 4 Wochen (28 Tage).

Datumsbezogene Funktionen, welche Texte zurückgeben

Date.DayOfWeekName([Datum], culture)


Kalenderjahresbezug siehe Custom M Funktion GetTableOfDateAndDateTime

Tagesbasierte Kalkulationen

  • Volle Datumsbezeichnung := Date.ToText([Datum], "dd. MMMM yyyy", culture)
  • Name des Tages := Date.DayOfWeekName([Datum], culture)

Ermittlung, ob Arbeitstag oder Wochenende

if (Date.DayOfWeek([Datum], Day.Monday) + 1) < 6 then "AT" else "WE"


Tag in der Kalenderwoche (nicht ISO-Kalenderwoche)

Date.DayOfWeek([Datum], Day.Monday) + 1


Tag im Kalendermonat := Date.Day([Datum])


Tag im Kalenderjahr := Date.DayOfYear([Datum])


Wochenbasierte Kalkulationen

Kalenderwoche im Jahr (nicht ISO-Kalenderwoche) := Date.WeekOfYear([Datum])

ISO-Kalenderwochennummer

--- SCHNIPP


(MyDate as date) =>
      //Source --> https://blogs.office.com/en-us/2009/06/30/week-numbers-in-excel/
    let
      //MyDate = #date(2016,1,3),
      Part1 = Number.From(MyDate) - Number.From(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) + 1) + 4)),1,3)),
      Part2 = Date.DayOfWeek(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) +1) + 4)),1,3), Day.Sunday)+1 + 5,
      ISOWeekNumber = Number.IntegerDivide(Part1 + Part2, 7)
    in
      ISOWeekNumber

--- SCHNAPP

ISO-Kalenderwochenbezeichnung

--- SCHNIPP

(MyDate as date) =>
      //Source --> https://blogs.office.com/en-us/2009/06/30/week-numbers-in-excel/
    let
      //MyDate = #date(2016,1,3),
      Part1 = Number.From(MyDate) - Number.From(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) + 1) + 4)),1,3)),
      Part2 = Date.DayOfWeek(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) +1) + 4)),1,3), Day.Sunday)+1 + 5,
      ISOWeekNumber = "ISO KW "& Text.From(Number.IntegerDivide(Part1 + Part2, 7))
    in
      ISOWeekNumber

--- SCHNAPP

Monatsbasierte Kalkulationen

Monatsnummer im Kalenderjahr := Date.Month([Datum])

Name des Monats := Date.MonthName([Datum], culture)


Quartalsbasierte Kalkulationen

Quartal im Kalenderjahr := Date.QuarterOfYear([Datum])

Name des Quartals im Kalenderjahr := "Q"&Number.ToText(Date.QuarterOfYear([Datum]))


Halbjahresbasierte Kalkulationen

Halbjahr im Kalenderjahr := Number.RoundUp(Date.Month([Datum])/6,0)

Name des Halbjahres im Kalenderjahr := "HJ"&Number.ToText(Number.RoundUp(Date.Month([Datum])/6,0))


Jahresbasierte Kalkulationen

Kalenderjahr := Date.Year([Datum])


Ermittlung von Schaltjahren := Date.IsLeapYear([Datum])


Monate, Wochen oder Tage zu einem Datum hinzufügen

Jahre zu einem Datum hinzufügen := Date.AddYears([Datum],AnzahlJahre)
Beispiel [Datum] := 12.08.2018, Anzahl Jahre := 1
Date.AddYears([Datum],1) := 12.08.2018

Monate zu einem Datum hinzufügen := Date.AddMonths([Datum],AnzahlMonate)
Beispiel [Datum] := 12.08.2018, Anzahl Monate :=  12
Date.AddMonths([Datum],12) := 12.08.2019

Wochen zu einem Datum hinzufügen := Date.AddWeeks([Datum],AnzahlWochen)
Beispiel [Datum] := 12.08.2018, Anzahl Wochen := 52
Date.AddWeeks([Datum],52) := 11.08.2019

Tage zu einem Datum hinzufügen := Date.AddDays([Datum],AnzahlTage)
Beispiel [Datum] := 12.08.2018, Anzahl Tage := 12
Date.AddDays([Datum],12) := 26.08.2019

Vorjahr zu einem Datum
Date.AddYears([Datum],-1)

Vormonat (previous month) zu einem Datum
Date.AddMonths([Datum],-1)

siehe auch Geschäftsjahr und -quartal 










Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()
Mehr anzeigen

Dropdown Liste mit Mehrfachauswahl

Bild
Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden (Daten -> Datentools -> Datenüberprüfung). Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden weiterführende Informationen siehe hier Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen). --- SCHNIPP -- Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String '** Errorhandling On Error GoTo Errorhandling '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&
Mehr anzeigen

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{"
Mehr anzeigen