Datumbereik in Excel-formule
Er zijn momenten waarop we verschillende bewerkingen moeten uitvoeren, zoals optellen of aftrekken op datumwaarden met Excel. Door datumbereiken in Excel in te stellen, kunnen we berekeningen op deze datums uitvoeren. Voor het instellen van datumbereiken in Excel kunnen we de cellen met een begin- en einddatum eerst opmaken als 'Datum' en vervolgens de operatoren '+' of '-'t gebruiken om de einddatum of de duur van het bereik te bepalen.
Voorbeelden van datumbereik in Excel
Voorbeeld # 1 - Basis datumbereiken
Laten we eens kijken hoe het toevoegen van een getal tot nu toe een datumbereik creëert.
Stel dat we een startdatum hebben in cel A2.
Als we er nu een getal aan toevoegen, bijvoorbeeld 5, kunnen we een datumbereik samenstellen.
Als we nu cel A3 selecteren en '= B2 + 1' typen.
Kopieer deze cel: B2 en plak deze in cel B3; de relatieve celverwijzing zou als volgt veranderen:
We kunnen dus zien dat op deze manier meerdere datumbereiken kunnen worden opgebouwd.
Voorbeeld # 2 - Datumvolgorde maken
Met Excel kunnen we eenvoudig meerdere reeksen maken. Nu weten we dat datums enkele getallen zijn in Excel. We kunnen dus dezelfde methode gebruiken om datumbereiken te maken. Dus om datumbereiken te maken die hetzelfde bereik of hetzelfde interval hebben, maar de datums veranderen naarmate we verder gaan, kunnen we de onderstaande stappen volgen:
- Typ een startdatum en einddatum in minimaal twee rijen.
- Selecteer beide bereiken en sleep het naar beneden tot de rij waar we de datumbereiken nodig hebben:
We kunnen dus zien dat Excel automatisch datumbereiken maakt voor de volgende rijen door de datumbereiken in de eerste twee rijen als sjabloon te gebruiken.
Voorbeeld # 3
Laten we nu zeggen dat we twee datums in twee cellen hebben, en we willen ze aaneengeschakeld weergeven als een datumbereik in een enkele cel. Hiervoor kan een formule worden gebruikt die is gebaseerd op de functie 'TEXT'. De algemene syntaxis voor deze formule is als volgt:
= TEXT (datum1, "format") & "-" & TEXT (datum2, "format")Zaak 1:
Deze functie ontvangt twee datumwaarden als numeriek en voegt deze twee datums samen in de vorm van een datumbereik volgens een aangepast datumformaat ("mmm d" in dit geval):
Datumbereik = TEXT (A2, "mmm d") & "-" & TEXT (B2, "mmm d")
We kunnen dus in de bovenstaande schermafbeelding zien dat we de formule hebben toegepast in cel C2. De TEXT-functie ontvangt de datums die zijn opgeslagen in de cellen A2 en B2, het en-teken '&' wordt gebruikt om de twee datums samen te voegen als een datumbereik in een aangepast formaat, in dit geval gespecificeerd als 'mmm d', in een enkele cel en de twee datums worden samengevoegd met een koppelteken '-' in het resulterende datumbereik dat wordt bepaald in cel C2.
Geval 2:
Laten we nu zeggen dat we in dit voorbeeld de twee datums willen combineren als een datumbereik in een enkele cel met een ander formaat, zeg 'd mmm jj'. Dus de formule voor een datumbereik zou in dit geval als volgt zijn:
Datumbereik = TEXT (A3, "d mmm jj") & "-" & TEXT (B3, "d mmm jj")
Dus we kunnen in de bovenstaande schermafbeelding zien dat de TEXT-functie de datums ontvangt die zijn opgeslagen in de cellen A3 en B3, de en-teken '&' wordt gebruikt om de twee datums samen te voegen als een datumbereik in een aangepast formaat, gespecificeerd als 'd mmm jj ”In dit geval in één cel. De twee datums worden samengevoegd met een koppelteken '-' in het resulterende datumbereik dat is bepaald in cel C3.
Voorbeeld # 4
Laten we nu eens kijken wat er gebeurt als de start- of einddatum ontbreekt. Laten we zeggen dat de einddatum als volgt ontbreekt:
De formule op basis van de TEXT-functie die we hierboven hebben gebruikt, zal niet correct werken als de einddatum ontbreekt, aangezien het koppelteken in de formule hoe dan ook aan de startdatum zou worden toegevoegd, dat wil zeggen, samen met de startdatum, zouden we ook zie een koppelteken in het datumbereik. We zouden daarentegen alleen de startdatum als het datumbereik willen zien voor het geval de einddatum ontbreekt.
Dus in dit geval kunnen we de formule hebben door de aaneenschakeling en de tweede TEXT-functie als volgt in een IF-clausule te wikkelen:
Datumbereik = TEXT (A2, "mmm d") & IF (B2 "", "-" & TEXT (B2, "mmm d"), "")
We kunnen dus zien dat de bovenstaande formule een volledig datumbereik creëert met behulp van beide datums wanneer beide aanwezig zijn. Het geeft echter alleen de startdatum weer in de opgegeven indeling als de einddatum ontbreekt. Dit wordt gedaan met behulp van een IF-clausule.
Als nu beide datums ontbreken, kunnen we een geneste IF-instructie in Excel gebruiken (dwz een IF in een andere IF-instructie) als volgt:
Datumbereik = IF (A4 "", TEXT (A4, "mmm d") & IF (B4 "", "-" & TEXT (B4, "mmm d"), ""), ")
We kunnen dus zien dat de bovenstaande formule een lege string retourneert als de startdatum ontbreekt.
Als beide datums ontbreken, wordt ook een lege string geretourneerd.
Dingen om te onthouden
- We kunnen zelfs een lijst met opeenvolgende datums maken met de opdracht 'Vulhendel'. Om dit te doen, kunnen we de cel met een startdatum selecteren en deze vervolgens naar het celbereik slepen dat we willen vullen. Klik op het tabblad 'Home' -> 'Bewerken' -> 'Serie vullen' en kies vervolgens een datumeenheid die we willen gebruiken.
- Als we de duur of het aantal dagen tussen twee datums willen berekenen, kunnen we de twee datums eenvoudigweg aftrekken met behulp van de '-' operator, en we krijgen het gewenste resultaat.
Opmerking: het formaat van de cellen: A2 en B2 is 'Datum', terwijl die van cel C2 'Algemeen' is, aangezien het het aantal dagen berekent.
