PDA

Vollständige Version anzeigen : Wie ermittle ich den ersten/letzten Tag eines Zeitraums?


Stefan Kulpa
01.02.2003, 03:56
<div><link href="http://www.ms-office-forum.de/ubb/codeconv.css" rel="stylesheet"><pre><span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// In diesem Codeausschnitt werden folgende Funktionen dargestellt:</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Funktion |R&uuml;ckgabe</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// FirstDayCurrentWeek |Erster Tag der aktuellen Woche</span>
<span class="REM">'// LastDayCurrentWeek |Letzter Tag der aktuellen Woche</span>
<span class="REM">'// FirstDayOfWeek |Erster Tag einer beliebigen Woche</span>
<span class="REM">'// LastDayOfWeek |Letzter Tag einer beliebigen Woche</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// FirstDayCurrentMonth |Erster Tag des aktuellen Monats</span>
<span class="REM">'// LastDayCurrentMonth |Letzter Tag des aktuellen Monats</span>
<span class="REM">'// FirstDayOfMonth |Erster Tag eines beliebigen Monats</span>
<span class="REM">'// LastDayOfMonth |Letzter Tag eines beliebigen Monats</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// FirstDayCurrentQuarter |Erster Tag in diesem Quartal</span>
<span class="REM">'// LastDayCurrentQuarter |Letzter Tag in diesem Quartal</span>
<span class="REM">'// FirstDayInQuarter |Erster Tag im Quartal eines beliebigen Datums</span>
<span class="REM">'// LastDayInQuarter |Letzter Tag im Quartal eines beliebigen Datums</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// FirstDayCurrentYear |Ersten Tag des aktuellen Jahres</span>
<span class="REM">'// LastDayCurrentYear |Letzter Tag des aktuellen Jahres</span>
<span class="REM">'// FirstDayOfYear |Erster Tag eines beliebigen Jahres</span>
<span class="REM">'// LastDayOfYear |Letzter Tag eines beliebigen Jahres</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
&nbsp;
<span class="TOKEN">Sub</span> Beispiel()
&nbsp;
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag der aktuellen Woche: &quot;; FirstDayCurrentWeek()
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag der aktuellen Woche: &quot;; LastDayCurrentWeek()
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag einer beliebigen Woche: &quot;; FirstDayOfWeek(Date)
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag einer beliebigen Woche: &quot;; LastDayOfWeek(Date)
<span class="TOKEN">Debug.Print</span> <span class="TOKEN">String</span>(60, &quot;-&quot;)
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag des aktuellen Monats: &quot;; FirstDayCurrentMonth()
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag des aktuellen Monats: &quot;; LastDayCurrentMonth()
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag eines beliebigen Monats: &quot;; FirstDayOfMonth(5, 2003)
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag eines beliebigen Monats: &quot;; LastDayOfMonth(5, 2003)
<span class="TOKEN">Debug.Print</span> <span class="TOKEN">String</span>(60, &quot;-&quot;)
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag in diesem Quartal: &quot;; FirstDayCurrentQuarter()
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag in diesem Quartal: &quot;; LastDayCurrentQuarter()
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag im Quartal eines beliebigen Datums: &quot;; FirstDayInQuarter(Date)
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag im Quartal eines beliebigen Datums: &quot;; LastDayInQuarter(Date)
<span class="TOKEN">Debug.Print</span> <span class="TOKEN">String</span>(60, &quot;-&quot;)
<span class="TOKEN">Debug.Print</span> &quot;Ersten Tag des aktuellen Jahres: &quot;; FirstDayCurrentYear()
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag des aktuellen Jahres: &quot;; LastDayCurrentYear()
<span class="TOKEN">Debug.Print</span> &quot;Erster Tag eines beliebigen Jahres: &quot;; FirstDayOfYear(2003)
<span class="TOKEN">Debug.Print</span> &quot;Letzter Tag eines beliebigen Jahres: &quot;; LastDayOfYear(2003)
&nbsp;
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayCurrentWeek() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ersten Tag der aktuellen Woche ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayCurrentWeek = Date - (Weekday(Date, vbMonday) - 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayCurrentWeek() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzten Tag der aktuellen Woche ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayCurrentWeek = Date + (7 - Weekday(Date, vbMonday))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayOfWeek(dtDate <span class="TOKEN">As</span> Date) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ersten Tag einer beliebigen Woche ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | Pr&uuml;fdatum</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayOfWeek = dtDate - (Weekday(dtDate, vbMonday) - 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayOfWeek(dtDate <span class="TOKEN">As</span> Date) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzten Tag einer beliebigen Woche ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | Pr&uuml;fdatum</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayOfWeek = dtDate + (7 - Weekday(Date, vbMonday))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayCurrentMonth() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ermittelt den ersten Tag des aktuellen Monats</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | R&uuml;ckgabe eines Datums (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayCurrentMonth = <span class="TOKEN">CDate</span>(Date - Day(Date) + 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayCurrentMonth() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzten Tag des aktuellen Monats ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | R&uuml;ckgabe eines Datums (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayCurrentMonth = <span class="TOKEN">CDate</span>(DateAdd(&quot;m&quot;, 1, Date - Day(Date)))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayOfMonth(lMonth <span class="TOKEN">As</span> Long, lYear <span class="TOKEN">As</span> <span class="TOKEN">Long</span>) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ersten Tag eines beliebigen Monats ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | lMonth - Monatszahl (1-12)</span>
<span class="REM">'// | lYear - Jahreszahl</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | R&uuml;ckgabe eines Datums (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayOfMonth = DateSerial(lYear, lMonth, 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayOfMonth(lMonth <span class="TOKEN">As</span> Long, lYear <span class="TOKEN">As</span> <span class="TOKEN">Long</span>) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzten Tag eines beliebigen Monats ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | lMonth - Monatszahl (1-12)</span>
<span class="REM">'// | lYear - Jahreszahl</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayOfMonth = DateSerial(lYear, lMonth + 1, 0)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayCurrentQuarter() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Erster Tag des aktuellen Quartals ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="TOKEN">Dim</span> dblValue <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
dblValue = (Month(Date) - 1) / 3
FirstDayCurrentQuarter = _
DateSerial(Year(Date), Int(dblValue) * 3 + 1, 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayCurrentQuarter() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzter Tag des aktuellen Quartals ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="TOKEN">Dim</span> dblValue <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
dblValue = (Month(Date) - 1) / 3
LastDayCurrentQuarter = _
DateSerial(Year(Date), Int(dblValue) * 3 + 4, 0)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayInQuarter(dtDate <span class="TOKEN">As</span> Date) <span class="TOKEN">As</span> Date
<span class="REM">'// -----------------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ermittelt den ersten Tag im Quartal eines beliebigen Datums</span>
<span class="REM">'// -----------------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | dtDate - Pr&uuml;fdatum</span>
<span class="REM">'// -----------------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// -----------------------------------------------------------------------------</span>
<span class="TOKEN">Dim</span> dblValue <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
dblValue = (Month(dtDate) - 1) / 3
FirstDayInQuarter = DateSerial(Year(dtDate), Int(dblValue) * 3 + 1, 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayInQuarter(dtDate <span class="TOKEN">As</span> Date) <span class="TOKEN">As</span> Date
<span class="REM">'// ------------------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ermittelt den letzten Tag im Quartal eines beliebigen Datums</span>
<span class="REM">'// ------------------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | dtDate - Pr&uuml;fdatum</span>
<span class="REM">'// ------------------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ------------------------------------------------------------------------------</span>
<span class="TOKEN">Dim</span> dblValue <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
dblValue = (Month(dtDate) - 1) / 3
LastDayInQuarter = DateSerial(Year(dtDate), Int(dblValue) * 3 + 4, 0)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayCurrentYear() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ersten Tag des aktuellen Jahres ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayCurrentYear = Date - DatePart(&quot;y&quot;, Date) + 1
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayCurrentYear() <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzten Tag des aktuellen Jahres ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | -</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayCurrentYear = _
DateAdd(&quot;yyyy&quot;, 1, Date - DatePart(&quot;y&quot;, Date))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> FirstDayOfYear(lYear <span class="TOKEN">As</span> <span class="TOKEN">Long</span>) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Ersten Tag eines beliebigen Jahres ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | lYear - Jahreszahl</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
FirstDayOfYear = DateSerial(lYear, 1, 1)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Function</span> LastDayOfYear(lYear <span class="TOKEN">As</span> <span class="TOKEN">Long</span>) <span class="TOKEN">As</span> Date
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Methode: | Letzter Tag eines beliebigen Jahres ermitteln</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// Parameter: | lYear - Jahreszahl</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
<span class="REM">'// R&uuml;ckgabe: | Datumswert (s.o.)</span>
<span class="REM">'// ----------------------------------------------------------------------</span>
LastDayOfYear = DateSerial(lYear, 12, 31)
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Bei Bedenken gegen die Date-Funktion sollte diese durch die Now()-Funktion ersetzt werden. Dies hat allerdings zur Folge, dass sämtliche Rückgaben auch mit einer Uhrzeit behaftet sind, was nicht unbedingt gewünscht ist.