Eine weitere Möglichkeit zur Berechnung der DIN Kalenderwoche

'Im Beispiel steht das Datum in A1

=KÜRZEN((A1-WOCHENTAG(A1;2)+11-("1/"&JAHR(A1+4-WOCHENTAG(A1;2))))/7)

 Namen in Zellen aufteilen: Vorname - Nachname

'Aufruf durch Zelleingabe z.B. =Nachname(A1)

Function Nachname(GanzerName)
For I=0 to Len(GanzerName)-1
Umk=Umk & Mid(GanzerName,Len(GanzerName)-I,1)
Next I
Nachname=Right(GanzerName,InStr(Umk," "))
End Function

=Osterdatum(gewünschtes Jahr)

'Das Jahr als vierstellige Zahl eingeben

Function Osterdatum(Jahr As Variant) As Date
Dim A, B, C, D, E, J, M, N, O, Monat As Integer
If IsDate(Jahr) = True Then
J = Int(Year(Jahr))
ElseIf IsNumeric(Jahr) Then
J = Int(Jahr)
Else
Exit Function
End If
If J < 1900 Or J > 2078 Then
Exit Function
End If
M = 24
N = 5
A = J Mod 19
B = J Mod 4
C = J Mod 7
D = (19 * A + M) Mod 30
E = ((2 * B) + (4 * C) + (6 * D) + N) Mod 7
O = 22 + D + E
If O > 31 Then
O = D + E - 9
Monat = 4
If O = 26 Then O = 19
If O = 25 And D = 28 And (J Mod 19) > 10 Then O = 18
Else
Monat = 3
End If
Osterdatum = DateSerial(J, Monat, O)
End Function

=Kalenderwoche(Datum, Jahreserster)

'Den Jahresersten ebenfalls als Datum eingeben z.B. 01.01.98

Function KalenderWoche(datum As Date, Jahreserster As Date)
KalenderWoche = ((datum) - (Jahreserster)) * 52 / 365 + 1
KalenderWoche = Format((KalenderWoche), "##")
End Function

Kalenderwoche berechnen nach DIN-Norm

'Funktionsaufruf mit  =din_kw(Datum)'

Function din_kw(datum As Date) As Integer
Dim i, j, k As Integer
i = datum - DateSerial(Year(datum), 1, 1)
k = WeekDay(DateSerial(Year(datum), 1, 1), vbMonday)
j = Int((i - (8 - k)) / 7) + 1
If k <= 4 Then j = j + 1
If j = 0 Then
j = din_kw(DateSerial(Year(datum) - 1, 12, 31))
ElseIf j = 53 And WeekDay(DateSerial(Year(datum), 12, 31), vbMonday) <= 3 Then
j = 1
End If
din_kw = j
End Function

Monatsletzten ermitteln

'Aufruf von: Ultimo(A1), wobei in A1 das Datum des gew.Monats steht'

Function Ultimo(MeinDatum)
Dim NächsterMonat, Monatsende
'"m" gibt Monate als Intervall an
NächsterMonat = DateAdd("m", 1, MeinDatum)
Monatsende = NächsterMonat - DatePart("d", NächsterMonat)
Ultimo= Monatsende
End Function

Euro-Umrechnung

'Aufruf durch Eingabe von dmwert bzw. euwert'

Private Const umrechfak As Double = 1.95583
Function euro(dmwert)
euro = dmwert / umrechfak
End Function

Function dem(euwert)
dem = euwert * umrechfak
End Function

in Zelle eingeben:
=Wiederholen("Zeichen";Multiplikator)

als Zeichen kann jedes beliebige zeichen verwendet werden
Multiplikator ist die Zelle mit der Stückzahl

Rundungszahlen

Die Zahl X wird innterhalb der Grenzen eines beliebigen Intervalls Y auf das Vielfache auf- bzw. abgerundet
z.B.Intervall=25, wenn x = 112,49 dann wert 100, wenn x=112,50, dann wert 125

=Ganzzahl((x+y/2)/y)*y

Spitzenwerte addieren

Die Funktion KGrößte liefert den k-größten Wert einer Datengruppe, wobei k frei gewählt werden kann. Mit K=2 wird also der zweitgrößte Wert ermittelt:

Wenn nur die Spitzenwerte addiert werden sollen, folgendes eingeben:

=Summe(Kgrößte(B2:Q2;1);Kgrößte(B2:Q2;2);Kgrößte(B2:Q2;3))

Zellen aufsplitten

Name und Vorname stehen z.B. in A1

in B1 soll Vorname stehen
in C1 der Nachname

in B1 folgende Formel:=LINKS(A1;SUCHEN(" ";A1))
in Zelle C1 =RECHTS(A1;LÄNGE(A1)-SUCHEN(" ";A1))

Wichtig ist, daß zwischen Vor- und Nachname eine Leerzeichen ist.

Terminkalender erstellen

um einen Terminkalender zu erstellen nachfolgende Formel als Matrix* eingeben:

=WENN(MONAT(Start)<>MONAT(Start-WOCHENTAG(Start;3)+{0;1;2;3;4;5}*7+_
{1.2.3.4.5.6.7}-1);"";Start-WOCHENTAG(A2;3)+{0;1;2;3;4;5}*7+{1.2.3.4.5.6.7}-1)

start =Zelle mit dem gewünschten Datum
0,1,2,3,4,5, usw*7 bedeutet= Tabelle 5 Zeilen(wochen) und 7 spalten(Tage)
1.2.3.4.5.6.7 -1 bedeutet= 7 spalten in einer Zeile

wenn ich von 1-31 untereinander haben möchte:
0,1,2,3,4,5,6,7,8...bis37*1 = 37 Zeilen und 1 Spalte
1-1 bedeutet 1 spalte in einer Zeile

*Bei einer Matrix werden die zu füllenden Zellen markiert, die Formel eingegeben und mit STRG+Umschalt+Eingabe beendet.

(diesen Kalender zum Download finden Sie unter download)

Tabellenblattname in Zelle anzeigen

=RECHTS(ZELLE("Dateiname");LÄNGE(ZELLE("Dateiname"))-FINDEN("]";ZELLE("Dateiname")))

Datumsdifferenzen berechnen

folgende Funktion berechnet die Differenz zweier Datumsangaben:

=datedif(a1;b1;"d")

Das "d" steht für die Differenz in Tagen

folgende Variablen sind möglich:
"y" - Jahre
"m" - Monate
"d" - Tage
"ym" - Monate nach Abzug Jahre
"yd" - Tage nach Abzug Jahre
"md" - Tage nach Abzug Monate und Jahre

Zwischensumme nicht einbeziehen

Berechnung von Zellfarbe abhängig

'es werden nur die Zellinhalte mit der entsprechenden Hintergrundfarbe summiert. Aufruf durch =Farbsumme(Tabellenbereich;Farbnummer)
wenn die Summe der Textfarbe gesucht wird, die Zeile "If Zelle.Interior.ColorIndex abändern in If Zelle.Font.ColorIndex

Function Farbsumme(Bereich As Range, Farbe As Integer)
Dim Zelle As Object
Application.Volatile
For Each Zelle In Bereich
If Zelle.Interior.ColorIndex = Farbe Then
Farbsumme = Farbsumme + Zelle
End If
Next
End Function

Akustische Funktion für Ergebnis

'der Aufruf erfolgt mit =SpielWAV(A1<100;"c:\sound1.wav";"c:\sound2.wav")

Formeln einer Zelle auslesen

'Aufruf erfolgt mit =zeigeformel(Zelle)

Quersumme berechnen

'Aufruf erfolgt mit =Quersumme(Zahl), wobei Zahl für die Zahl oder den Bereich steht

Teilergebnis von gefilterten Daten

'die 9 steht in diesem Fall für Summe, D1:D20 ist der Bereich der ungefilterten Daten

doppelte Datensätze finden

'mit einer verschachtelten Wenn-Funktion. Die Formel in eine Spalte einfügen und nach unten ausfüllen. Anschließend die Spalte sortieren oder über Autofilter die doppelten Datensätze anzeigen lassen

Zufallszahl erzeugen

'Die Formel in eine Zelle eingeben und nach unten bis zur gewünschten Anzahl ausfüllen.

Alter berechnen

'Aufruf erfolgt mit =Alter(Geburtsdatum). Die Funktion berechnet das Alter verglichen mit dem heutigen Tag

Hetterich-Osterformel

' j steht für das Jahr

Zahl als Text anzeigen

'mit einer benutzerdefinierten Funktion. Der Aufruf erfolgt mit z.B. =zahltext(2100)

Differenzen zweier Zeitangaben

'mit der folgenden Formel.
Achtung! Das Ergebnis liegt als Text vor und kann in dieser Form für eine Weiterberechnung nicht mehr verwendet werden.

=WENN(A1-B1<0;"-";"+")&TEXT(ABS(A1-B1);"[h]:mm")

oder als Tipp von Peter zum weiterberechnen:

=WENN(B1>A1;B1-A1;(B1-A1)+1)

Zeiträume auf Stunden oder Minuten runden

'die Funktion ist enthalten, wenn bei der Installation die Analyse-Funktionen mit angegeben wurden

Euro runden auf 0,50

'Die Funktion VRUNDEN ist nur vorhanden, wenn die Analyse-Funktion im Add-In-Manager aktiviert ist

nach Geburtsdaten sortieren

'in einer Zusatzspalte nachfolgende Formel eingeben und diese Spalte sortieren lassen. Im Beispiel steht das Datum in Zelle A1

=DATUM(0;MONAT(A1);TAG(A1))

auf Excel-Kommentare zugreifen

'den Kommentar an anderer Stelle anzeigen lassen mit dem Aufruf der Funktion z.B. =Kommentar(Tabelle1!A3)