Excel-formel: Dynamiskt kalendergaller -

Sammanfattning

Du kan ställa in dynamiskt kalendergaller på ett Excel-kalkylblad med en serie formler, som förklaras i den här artikeln. I exemplet som visas är formeln i B6:

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

där "start" är det namngivna intervallet K5 och innehåller datumet 1 september 2018.

Förklaring

Obs: Detta exempel förutsätter att startdatumet kommer att ges som den första i månaden. Se nedan för en formel som dynamiskt returnerar den första dagen i den aktuella månaden.

Med layouten för rutnätet som visas är huvudproblemet att beräkna datumet i den första cellen i kalendern (B6). Detta görs med denna formel:

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

Denna formel visar söndagen före den första dagen i månaden genom att använda VÄLJ-funktionen för att "rulla tillbaka" rätt antal dagar till föregående söndag. CHOOSE fungerar perfekt i denna situation, eftersom det tillåter godtyckliga värden för varje veckodag. Vi använder den här funktionen för att rulla tillbaka noll dagar när den första dagen i månaden är en söndag. Mer information om detta problem finns här.

Med den första dagen som fastställdes i B6, ökar de andra formlerna i rutnätet helt enkelt det föregående datumet med en, börjar med formeln i C6:

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

Denna formel testar cellen omedelbart till vänster för ett värde. Om inget värde hittas drar det ett värde från kolumn H i raden ovan. Obs! $ H5 är en blandad referens för att låsa kolumnen eftersom formeln kopieras genom rutnätet. Samma formel används i alla celler utom B6.

Villkorliga formateringsregler

Kalendern använder villkorliga formateringsformler för att ändra formatering för att skugga tidigare och framtida månader och för att markera den aktuella dagen. Båda reglerna tillämpas på hela rutnätet. För föregående och nästa månad är formeln:

=MONTH(B6)MONTH(start)

För aktuell dag är formeln:

=B6=TODAY()

Mer information finns i: Villkorlig formatering med formler (10 exempel)

Kalender rubrik

Kalendertiteln - månad och år - beräknas med denna formel i cell B4:

=start

Formaterad med det anpassade nummerformatet "mmmm åååå". För att centrera titeln ovanför kalendern har intervallet B4: H4 horisontell inriktning inställd på "centrera över valet". Detta är ett bättre alternativ än att slå ihop celler, eftersom det inte ändrar rutnätstrukturen i kalkylbladet.

Evig kalender med aktuellt datum

För att skapa en kalender som uppdateras automatiskt baserat på det aktuella datumet kan du använda formeln så här i K5:

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

Denna formel får det aktuella datumet med TODAY-funktionen och får sedan den första dagen i den aktuella månaden med EOMONTH-funktionen. Ersätt TODAY () med ett visst datum för att skapa en kalender i en annan månad. Mer information om hur EOMONTH fungerar här.