Excel-formule: dynamisch agendaraster -

Inhoudsopgave

Samenvatting

U kunt een dynamisch agendaraster instellen op een Excel-werkblad met een reeks formules, zoals uitgelegd in dit artikel. In het getoonde voorbeeld is de formule in B6:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

waarbij "start" het benoemde bereik K5 is, en de datum 1 september 2018 bevat.

Uitleg

Opmerking: in dit voorbeeld wordt ervan uitgegaan dat de startdatum wordt opgegeven als de eerste van de maand. Zie hieronder voor een formule die dynamisch de eerste dag van de huidige maand retourneert.

Met de lay-out van het raster zoals weergegeven, is het grootste probleem het berekenen van de datum in de eerste cel van de kalender (B6). Dit wordt gedaan met deze formule:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

Deze formule berekent de zondag voorafgaand aan de eerste dag van de maand door de functie KIEZEN te gebruiken om het juiste aantal dagen terug te draaien naar de vorige zondag. CHOOSE werkt perfect in deze situatie, omdat het willekeurige waarden voor elke dag van de week toestaat. We gebruiken deze functie om nul dagen terug te draaien wanneer de eerste dag van de maand een zondag is. Hier vindt u meer informatie over dit probleem.

Met de eerste dag vastgesteld in B6, verhogen de andere formules in het raster de vorige datum eenvoudig met één, te beginnen met de formule in C6:

=IF(B6"",B6,$H5)+1

Deze formule test de cel onmiddellijk naar links op een waarde. Als er geen waarde wordt gevonden, wordt een waarde opgehaald uit kolom H in de rij erboven. Opmerking $ H5 is een gemengde verwijzing om de kolom te vergrendelen terwijl de formule door het hele raster wordt gekopieerd. Dezelfde formule wordt in alle cellen gebruikt, behalve in B6.

Voorwaardelijke opmaakregels

De kalender gebruikt voorwaardelijke opmaakformules om de opmaak te wijzigen om vorige en toekomstige maanden te schaduwen en om de huidige dag te markeren. Beide regels worden op het hele raster toegepast. Voor vorige en volgende maanden is de formule:

=MONTH(B6)MONTH(start)

Voor de huidige dag is de formule:

=B6=TODAY()

Zie voor meer informatie: Voorwaardelijke opmaak met formules (10 voorbeelden)

Kop van de agenda

De kalendertitel - maand en jaar - worden berekend met deze formule in cel B4:

=start

Opgemaakt met de aangepaste getalnotatie "mmmm jjjj". Om de titel boven de kalender te centreren, heeft het bereik B4: H4 horizontale uitlijning ingesteld op "centreren over selectie". Dit is een betere optie dan het samenvoegen van cellen, aangezien het de rasterstructuur in het werkblad niet verandert.

Eeuwigdurende kalender met huidige datum

Om een kalender te maken die automatisch wordt bijgewerkt op basis van de huidige datum, kunt u de formule als volgt gebruiken in K5:

=EOMONTH(TODAY(),-1)+1

Deze formule haalt de huidige datum op met de TODAY-functie en haalt vervolgens de eerste dag van de huidige maand op met de EOMONTH-functie. Vervang TODAY () door een bepaalde datum om een kalender in een andere maand op te bouwen. Meer details over hoe EOMONTH hier werkt.