☰

Excel-Formel zum Berechnen des Datums basierend auf 2 Zellen und wie kopiert man es in andere Zellen?

tag-icon • tag-icon microsoft-excel worksheet-function date
Excel-Formel zum Berechnen des Datums basierend auf 2 Zellen und wie kopiert man es in andere Zellen?

Anfänger im Excel-Bereich. Hier ist eine vereinfachte Version meiner Tabelle:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

Ich möchte Formeln erstellen, um das Start- und Enddatum basierend auf den Werten in den Spalten „eday“ und „duration“ und „begindate“ zu berechnen:

start date = begindate + eday
end date = start date + duration

Wenn ich also für das obige Beispiel eine Formel anwende, erwarte ich das folgende Ergebnis:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

Das Anfangsdatum 03/03/2020bleibt immer gleich.

Ich konnte eine Formel zum Laufen bringen, aber wenn ich den Ausfüllpunkt verwende, wird die Formel nicht richtig kopiert.

Bitte helfen Sie einem Neuling bei diesen Fragen:

  1. Wo kann das Anfangsdatum „03.03.2020“ am besten gespeichert werden – in einem separaten Arbeitsblatt? Fest codiert? Anderswo?

  2. Wie lautet die Formel für Startdatum und Enddatum?

  3. Wie kopiere ich die Formel in alle Zeilen der Spalte

Vielen Dank.

Antwort1

Das Erste, was ich mir über Datums- und Uhrzeitangaben in Excel merken möchte, ist, dass es sich eigentlich nur um Zahlen handelt, die mithilfe der Zahlenformatierung so formatiert werden, dass sie wie Datums-/Uhrzeitangaben aussehen. Das heutige Datum (07.09.2019) ist 43715, und die aktuelle Uhrzeit (00:20 Uhr) ist 0,14302083...

Es gibt Arbeitsblattfunktionen, die die magische Zahl (im Wesentlichen Tage seit dem 01.01.1900) für Sie abrufen können, z. B. DATE(), TODAY()oder NOW().

Zu diesem Zweck, vorausgesetzt, IhreTagSpalte ist Cund Ihre Daten beginnen in Zeile 2, können Sie IhreStartdatumFormel in A2als =DATE(2020,3,3)+$C2und IhreEndterminFormel in B2als =$A2+$D2. Das ist der einfachste Ansatz.

Beachten Sie, dass ich absolute Spaltenbezüge ( $C, $A, $D) verwendet habe, um Excel zu zwingen, immer in den Spalten C, Aund zu suchen D, unabhängig davon, wo ich kopiere/einfüge oder fülle. Außerdem habe ich relative Zeilenbezüge ( 2) verwendet, sodass sich beim Kopieren/Einfügen oder Ausfüllen nach unten die Zeilennummer als Teil des Füllvorgangs ändert (d. h. in Zeile 3würde die Formel lauten =$A3+$D3). Manchmal ist dies erwünscht, manchmal nicht. Sie sollten also mit $und nein experimentieren, $um zu sehen, was es bewirkt.

Ich vermute, dass Sie ursprünglich IhreAnfangsdatumWert in einer Zelle, sondern verwendet einen relativen Bezug in der Formel und beim Ausfüllen den relativen Bezug für dieAnfangsdatumgeändert. Wenn ich Recht habe, dann bräuchte man eine absolute Referenz für seineAnfangsdatumZelle, z. B. wenn sie in der Spaltenzeile wäre J, 1sollte Ihre Formel lauten $J$1, nicht J1.

Wenn Sie sicherer werden, können Sie komplexere Funktionen verwenden, um die Stabilität oder Konfigurierbarkeit zu verbessern. Zum Beispiel:

  • Du kannst den ... benutzenNamensmanagerum Ihr festes, konstantes Datum zu speichern, indem Sie einen neuen Namen erstellen BeginDateund seinen Wert auf setzen =DATE(2020,3,3). Jetzt können Sie Ihre Startdatumsformel in A2auf aktualisieren =BeginDate+$C2und dieser feste Wert wird überall verwendet.

  • Sie können Ihren vierspaltigen Bereich formatieren alsTabelle mit Überschriften, wodurch Sie diese Kopfzeilennamen anstelle von Spaltenverweisen verwenden können. Wenn Sie den Bereich als Tabelle formatieren, dann in der ersten Datenzeile IhresStartdatumSpalte (die war A2) können Sie eingeben =BeginDate+[eday]und in IhremEndterminIn der Spalte (die war B2) können Sie eingeben =[start date]+[duration]. Da es sich um eine Tabelle handelt, sollten beide Formeln automatisch ausgefüllt werden, wenn die Tabelle wächst. Wenden Sie nun ein einfaches Zahlenformat vonKurzes Datezu IhremStartdatumUndEndterminSäulen.

  • Sie können die Anzeige berechneter Daten in Ihrer Tabelle unterbinden, wenn Sie keine Werte eingegeben haben, edayoder durationindem Sie die Formel inStartdatumzu den komplizierteren:

    =IF([eday]="", "", BeginDate+[eday])

    ...und Änderung der Formel inEndterminZu:

    =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])

verwandte Informationen